none
A collection that shows machines without software

All replies

  • You can do this via HW inventory:

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Program X" )

    You can do it via Software inventory or you can do it via DCM, or you can do it via Asset Intelligence...


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Wednesday, February 29, 2012 7:04 PM
    Moderator
  • Hi,

    This topica has been discussed here before, check out this thread with sample query:

    http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/ab48733e-ce5e-46df-b061-eee9f915afb9/

    Regards,
    Jörgen


    -- My System Center blog ccmexec.com -- Twitter @ccmexec

    Wednesday, February 29, 2012 7:05 PM
  • ok, I went to this link and I did a copy/paste of the code.   I've tried to look for Silverlight, Silverlight%, %Endpoint% (for Symantec) and each time it returns every client machine we have, over 15,000.     Here is what I have.

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "%Endpoint%")

    We know that Symantec Endpoint Protection is installed on about 99% of our machines so this logic is not working.  What syntax error do I have? 


    mqh7

    Wednesday, February 29, 2012 8:52 PM
  • Matthew, I did a copy/paste of your code and changed Program X to Symantec% and the SCCM Collection tool says there is a syntax error and it won't finish.  It does not say what the error is.

    mqh7

    Wednesday, February 29, 2012 8:54 PM
  • Jorgen, I went to your link  (http://www.geekshangout.com/?q=node/66) 

    I did a copy/paste of the code and it too returns every machine we have. 

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId   WHERE SMS_G_System_SYSTEM.Name   NOT IN (SELECT SMS_G_System_SYSTEM.Name  FROM  SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId INNER JOIN SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId   WHERE SMS_G_System_SoftwareFile.FileName = "silverlight.exe"   AND SMS_R_System.Client = 1)

    So I'm not sure what's going on but none of these SQL statements display machines "missing" the .EXE in question. 


    mqh7

    Wednesday, February 29, 2012 8:59 PM
  • I just pulled from another post I had.  It could have a special character.  Seperate the wql then put it back together.

    select SMS_G_System_COMPUTER_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Program X"

    Start with this query.  this will pull all the computers that have the application.  Then use the subselect to remove them.  test it...


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Wednesday, February 29, 2012 9:29 PM
    Moderator
  • Hi,

    I just tested it in my environment and it works just fine.. Try with something else that Silverlight.exe ? are you sure the syntax was pasted corecctly?

    Regars,
    Jörgen


    -- My System Center blog ccmexec.com -- Twitter @ccmexec

    Wednesday, February 29, 2012 9:32 PM
  • Keep in mind that query only look at x86 workstations.. You will need to modify it if you want both x86 and x64.



    http://www.enhansoft.com/

    Wednesday, February 29, 2012 9:48 PM
  • I changed it to look for excel.exe.  It still returns over 15,000 records.  And Office is part of our OS image so it should have returned 0 records.

    SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId   WHERE SMS_G_System_SYSTEM.Name   NOT IN (SELECT SMS_G_System_SYSTEM.Name  FROM  SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId INNER JOIN SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId   WHERE SMS_G_System_SoftwareFile.FileName = "excel.exe"   AND SMS_R_System.Client = 1)


    mqh7

    Wednesday, February 29, 2012 9:56 PM
  • I changed it to look for excel.exe.  It still returns over 15,000 records.  And Office is part of our OS image so it should have returned 0 records.

    SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId   WHERE SMS_G_System_SYSTEM.Name   NOT IN (SELECT SMS_G_System_SYSTEM.Name  FROM  SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId INNER JOIN SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId   WHERE SMS_G_System_SoftwareFile.FileName = "excel.exe"   AND SMS_R_System.Client = 1)

    That collection query is based on Software metering, not hardware inventory. Is SINV active and are you collecting excel.exe (or *.exe etc)? If SINV is not active: it's expected behavior, because the subquery won't return anything so nothin will be excluded then.


    Torsten Meringer | http://www.mssccmfaq.de

    Wednesday, February 29, 2012 10:05 PM
  • Yes, we have both hardware and software inventory enabled on all primary sites.   And we search for *.exe  in our Software Inventory Client Agent.   I can easily write an SCCM SQL Report that finds all machines that are running excel.exe or any *.exe for that matter so the info is in the DB.

    Where else do I need to look?


    mqh7

    Wednesday, February 29, 2012 10:13 PM
  • Try to create a collection and use the below SQL query options:

    Thursday, March 01, 2012 6:19 AM
  • Yes, I can create a collection that shows me all machines "With" any .EXE.  But I can't create one that shows me machines "Without" an .EXE.


    mqh7

    Thursday, March 01, 2012 2:48 PM
  • Jorgen, another thing is some of the .EXE's I will look for may not have anything displayed in Add/Remove.    How can you find them as well?   And I still can't get any of this code working.  it always returns 15,000+ records.

    And I have to have this all done by tomorrow :-)   


    mqh7

    Thursday, March 01, 2012 3:04 PM
  • Garth, thank you.  I think I have used the method you mention.   I have tried both of the following sets of code.   This is done within a SCCM 2007 R3 Collection.

    select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe%") and SMS_R_System.Client = 1

    -------------------------------------------------------------------------------------------------------------------------------------

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId WHERE SMS_G_System_SYSTEM.Name NOT IN (SELECT SMS_G_System_SYSTEM.Name FROM SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId INNER JOIN SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId WHERE SMS_G_System_SoftwareFile.FileName = "AcroRd32.exe" AND SMS_R_System.Client = 1)

    Both collections return everything.   Every workstation in our DB get returned.   Over 15,000 machines are in each collection results.  So either this code is wrong or there is something wrong with our SCCM environment??


    mqh7


    • Edited by mqh7 Thursday, March 01, 2012 3:53 PM
    Thursday, March 01, 2012 3:52 PM
  • Try this.. Note that I wrote this free hand and therefore it might have typos in it.

    select 
    	SMS_R_System.ResourceId, 
    	SMS_R_System.ResourceType, 
    	SMS_R_System.Name, 
    	SMS_R_System.SMSUniqueIdentifier, 
    	SMS_R_System.ResourceDomainORWorkgroup, 
    	SMS_R_System.Client 
    from 
    	SMS_R_System 
    where 
    	SMS_R_System.ResourceId not in (select 
    						SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId 
    					from 
    						SMS_G_System_ADD_REMOVE_PROGRAMS 
    					where 
    						SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe%") 
    
    	and SMS_R_System.ResourceId not in (select 
    						SMS_G_System_ADD_REMOVE_PROGRAMS64.ResourceId 
    					from 
    						SMS_G_System_ADD_REMOVE_PROGRAMS64 
    					where 
    						SMS_G_System_ADD_REMOVE_PROGRAMS64.DisplayName = "Adobe%") 
    	and SMS_R_System.Client = 1


    http://www.enhansoft.com/

    Thursday, March 01, 2012 4:28 PM
  • Garth, I copy/pasted your code into the SCCM collection and it says "Syntax Error" but does not indicate where.   I dropped it into a SCCM Report and it says Invalid object name 'SMS_R_System'.   I then dropped the code straight into SQL and it   says:  

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'SMS_R_System'.

    Your code looks good to me and I don't see any errors.    What part do you think failed the syntax check?


    mqh7

    Thursday, March 01, 2012 7:02 PM
  • Confirms that the WQL name for the ARP64 table is called “SMS_G_System_ADD_REMOVE_PROGRAMS64” it might be called “SMS_G_System_ADD_REMOVE_PROGRAMS_64”



    http://www.enhansoft.com/

    Thursday, March 01, 2012 7:17 PM
  • no, that didn't work either :-)  

    mqh7

    Thursday, March 01, 2012 9:32 PM
  • no, that didn't work either :-)  

    mqh7


    What didn't work? Did you lookup what the 64 bit ARP view is called? What is the view called?

    http://www.enhansoft.com/

    Thursday, March 01, 2012 9:37 PM
  • I am afraid we have exhausted all work that can be performed via the fourm.  I recommend that you continue to test on your own or find another ConfigMgr close to you that can possibly sitdown and watch what you are doing to determine what problems you are having.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Thursday, March 01, 2012 9:52 PM
    Moderator
  • Matthew Hudson, I have it working.

    select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System

    as sys inner join SMS_R_System as SystemResource on SystemResource.Client = sys.Client where sys.Name not in (select distinct

    sys.name from sms_r_system as sys   inner join sms_g_system_softwarefile ON   sys.ResourceID =

    sms_g_system_softwarefile.resourceid   where sms_g_system_softwarefile.filename = "acrord32.exe") and sys.Name like "%-PC-%"

    THIS WORKS!!  I got this from a Microsoft SQL Tech.

    Thanks to everyone for your help.

    mqh7

    Friday, March 02, 2012 9:30 PM