Report for AV - List Multiple
I have this query. Which is working, but need it to display all the DISPLAY_NAME that match Sophos, Symantec, McAfee, and Norton. I need to list all the AV that is on the current machine. So Sophos + another AV. It currently looks like it just takes the first software title it finds.
Trying to stop users from having too many.
SELECT MACHINE.NAME, MACHINE.IP, SOFTWARE.DISPLAY_NAME, SOFTWARE.DISPLAY_VERSION as Version FROM MACHINE JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID WHERE (OS_NAME like '%Microsoft Windows 7 Enterprise x64%')AND ((DISPLAY_NAME like '%SOPHOS%') AND (DISPLAY_NAME like '%symantec%') OR (DISPLAY_NAME like '%norton%') OR (DISPLAY_NAME like '%mcafee%')) order by name
Answers (3)
Query below.
Ended up using this. It's lengthy. I was trying not to do anymore labels if not needed. We have around 23,000 machines so they add up.
SELECT MACHINE.NAME, MACHINE.IP,
concat("Sophos Anti-Virus\n",GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n')) as Software
FROM MACHINE JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE
OS_NAME like '%Microsoft Windows 7 Enterprise x64%'
AND (
(DISPLAY_NAME REGEXP "Symantec") and name IN (
SELECT MACHINE.NAME FROM MACHINE JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE DISPLAY_NAME like '%Sophos Anti-Virus%'
) or
(DISPLAY_NAME REGEXP "Norton") and name IN (
SELECT MACHINE.NAME FROM MACHINE JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE DISPLAY_NAME like '%Sophos Anti-Virus%'
) or
(DISPLAY_NAME REGEXP "Mcafee") and name IN (
SELECT MACHINE.NAME FROM MACHINE JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE DISPLAY_NAME like '%Sophos Anti-Virus%'
)
)
GROUP BY NAME
Comments:
-
I understand we only have about 3k machines and our label library is huge. I think soneone even created labels for groups of labels. - SMal.tmcc 12 years ago
This looks like it will work.
I went into inventory, software and created a smart label with the AV vendors you mentioned. This showed me all my AV products. I then checked them all and did a choose action, add label and put AV products in the blank.
I then went to reports also put "label names" in as a column and created a rule for the os and if label is AV products.
I now have it working, but it's still only displaying McAfee when Sophos is installed as well.
SELECT MACHINE.NAME, MACHINE.IP, SOFTWARE.DISPLAY_NAME, GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') as Software FROM MACHINE JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID left JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID WHERE (OS_NAME like '%Microsoft Windows 7 Enterprise x64%')AND ((DISPLAY_NAME like '%SOPHOS%') AND (DISPLAY_NAME like '%symantec%') OR (DISPLAY_NAME like '%norton%') OR (DISPLAY_NAME like '%mcafee%')) order by name
Comments:
-
what does it do if you query by software metadata categorys "security" and/or "malware" - SMal.tmcc 12 years ago
-
I would have to set that up. I'm currently not using that feature. - dugullett 12 years ago
-
How about creating a smart label with the AV names in it and do a report based on the label name instead of display_name - SMal.tmcc 12 years ago