hello everyone

 

just checking any of you guys already have  a custom report to list any computer without a certain software like mcafee for example the report should be able to provide System Name, IP Address, Bios Serial Number, OS Name any help would be greatly appreciated.

 

thanks

 

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

If you are on 5.4 you should be able to do that with the wizard and create a report using the Computer and the topic and Software as the subtopic. You can also do it via machine labels. Create smart label that looks for machines who don't have Software Title Mcafee and give it a label like Don't have Mcafee. 

 

Then create a computer report to show you:

system name, ip address, BSN, OS name, label

in step 5 filter so it only shows you machiens that have the Dont' have mcafee machine label.

Answered 01/17/2013 by: nshah
Red Belt

Please log in to comment
1

SELECT DISTINCT M.NAME, IP, BIOS_SERIAL_NUMBER,OS_NAME

FROM MACHINE M

LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON M.ID = MSJT.MACHINE_ID

LEFT JOIN SOFTWARE S ON MSJT.SOFTWARE_ID = S.ID

WHERE S.DISPLAY_NAME NOT LIKE '%MCAFEE%'

Answered 01/18/2013 by: dugullett
Red Belt

  • thanks
  • Won't that list all of the software titles for all computers that aren't McAfee?
    • Yes. That's what they asked for "any computer without a certain software like mcafee".
  • I tried that and it returned ALL of the computers, not just the ones without the particular title.
    • Try this.

      SELECT DISTINCT M.NAME, IP, BIOS_SERIAL_NUMBER,OS_NAME
      FROM MACHINE M
      LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON M.ID = MSJT.MACHINE_ID
      LEFT JOIN SOFTWARE S ON MSJT.SOFTWARE_ID = S.ID
      LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID
      LEFT JOIN LABEL L ON MLJT.LABEL_ID=L.ID
      WHERE ((1 not in (select 1 from SOFTWARE S, MACHINE_SOFTWARE_JT MSJT where M.ID = MSJT.MACHINE_ID
      and MSJT.SOFTWARE_ID = S.ID and (S.DISPLAY_NAME like '%McAfee%'))) )
      AND L.NAME LIKE '%<LABEL_NAME>%'
Please log in to comment
1

Make sure you use the EXACT name from the software inventory.

 

Select

  MACHINE.NAME As 'System Name',

  MACHINE.SYSTEM_DESCRIPTION As 'Description',

  MACHINE.IP As 'IP Address',

  Group_Concat(Distinct SOFTWARE.DISPLAY_NAME Separator '\n') As

  'Software Title Name',

  SOFTWARE.DISPLAY_VERSION As 'Software Version'

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 != 'INSERT EXACT SOFTWARE NAME' And

  SOFTWARE.DISPLAY_VERSION !=  'INSERT EXACT SOFTWARE VERSION'

Group By

  MACHINE.ID

Order By

  MACHINE.NAME, SOFTWARE.DISPLAY_NAME

Answered 01/18/2013 by: WhitzEnd
Seventh Degree Black Belt

Please log in to comment
1

Here is one that correctly uses a subquery to limit to only machines that don't have McAfee Installed. I use McAfee Agent as the name for the software.

 

Select

  MACHINE.NAME As 'System Name',

  MACHINE.SYSTEM_DESCRIPTION As 'Description',

  MACHINE.IP As 'IP Address'

From

  MACHINE,

  MACHINE_SOFTWARE_JT,

  SOFTWARE  

Where

MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID  

andMACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID

and MACHINE.NAME not in (Select 

  MACHINE.NAME 

From

  MACHINE,

  MACHINE_SOFTWARE_JT, 

  SOFTWARE  

Where

MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID  

andMACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID

andSOFTWARE.DISPLAY_NAME in ('McAfee Agent')

)

Group By

  MACHINE.ID

Order By

  MACHINE.NAME

Answered 04/15/2013 by: crc4430
White Belt

Please log in to comment
0

Here is a query using a subquery that only shows machines that do not have McAfee installed. The software name that I use is "McAfee Agent"

 

Select

  MACHINE.NAME As 'System Name',

  MACHINE.SYSTEM_DESCRIPTION As 'Description',

  MACHINE.IP As 'IP Address'

From

  MACHINE,

  MACHINE_SOFTWARE_JT,

  SOFTWARE  

Where

MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID  

andMACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID

and MACHINE.NAME not in (Select 

  MACHINE.NAME 

From

  MACHINE,

  MACHINE_SOFTWARE_JT, 

  SOFTWARE  

Where

MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID  

andMACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID

andSOFTWARE.DISPLAY_NAME in ('McAfee Agent')

)

Group By

  MACHINE.ID

Order By

  MACHINE.NAME

Answered 04/15/2013 by: crc4430
White Belt

  • That works.
    I only want to run this report on computers in a particular label. What would I need to add for that?

    Thanks so much
Please log in to comment
Answer this question or Comment on this question for clarity