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
 
 
Answer Summary:
Query below.
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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

 
Answered 07/12/2012 by: dugullett
Red Belt

  • I understand we only have about 3k machines and our label library is huge. I think soneone even created labels for groups of labels.
Please log in to comment

Answers

1

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.

Answered 07/12/2012 by: SMal.tmcc
Red Belt

Please log in to comment
0

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
Answered 07/12/2012 by: dugullett
Red Belt

  • what does it do if you query by software metadata categorys "security" and/or "malware"
    • I would have to set that up. I'm currently not using that feature.
  • 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
Please log in to comment
This content is currently hidden from public view.
Reason: Removed by user request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity