/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


K1000 Reporting help

10/04/2017 1004 views
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:
0 Comments   [ + ] Show comments

Comments


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 :)

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ