K1000 Reporting help
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!
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
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
Comments:
-
Thanks! You are a star :) - BowdenJ 7 years ago
