Hi Can anyone help me with creating a SQL Report please?

What I need is all the devices with an IP address beginning 172.20.2 with the following columns:
  • System Name
  • IP Address
  • OS Name
  • Agent Version
  • What version of Symantec Endpoint Protection they have installed
I can created a report using the wizard, but I cannot get the software version as a separate column

Thanks for any help!
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

2
Here's a report that I wrote for Acrobat Pro:
SELECT MACHINE.NAME AS SYSTEM_NAME, 
USER_LOGGED, 
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED ,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_VERSION SEPARATOR '\n') AS SOFTWARE_DISPLAY_VERSION_GROUPED
FROM MACHINE  
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) 
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) 
WHERE (SOFTWARE.DISPLAY_NAME like '%Acrobat%Pro%')  
GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
You would need to change the display name and add to the where clause for IP address:
SELECT MACHINE.NAME AS SYSTEM_NAME, 
USER_LOGGED, 
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED ,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_VERSION SEPARATOR '\n') AS SOFTWARE_DISPLAY_VERSION_GROUPED
FROM MACHINE  
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) 
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) 
WHERE (SOFTWARE.DISPLAY_NAME like '%Symantec%') and MACHINE.IP like "172.20.2%" 
GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
As an FYI, you may be better off using the INET_ATON function for IP addresses, especially if you are looking for machines in a certain range. For instance:
INET_ATON(MACHINE.IP) between INET_ATON("172.16.96.1") and INET_ATON("172.16.103.255")
It can be cleaner than using a like statement as above. I used it in the example because you requested IP addresses that begin with 172.20.2

Answered 10/04/2017 by: chucksteel
Red Belt

  • Thanks! You are a star :)
Please log in to comment

Answers

Answer this question or Comment on this question for clarity