Collection query not reporting back accurately

[sccmnoobie]

Hi guys,

 

I have this query:

 

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 LIKE "%Avamar%") and SMS_R_System.Client = 1 and SMS_R_SYSTEM.OperatingSystemNameAndVersion like 'Microsoft Windows NT%Server%'

 

In short, it should report back with servers that don't have Avamar installed (not in ARP).

 

The issue is, even though it has filtered many servers that do, I hasn't for all of them. I have RDPed to one of the servers that comes up and in ARP there is an entry 'EMC Avamar for Windows x64 Editions' in the ARP Name field.

 

I entered the server name into the report 'Software registered in Add Remove Programs on a specific computer', and the Display Name shows 'EMC Avamar for Windows x64 Editions'.

 

I also have another collection that checks for servers without Sophos by copying and pasting the query, just changing LIKE "%Avamar%" the LIKE "%Sophos%". The same server doesn't show in the collection as the query has detected Sophos is installed.

 

I can confirm Avamar and Sophos are both installed on this particular server.

 

Thanks,

SCCMNoobie.

[Peter van der Woude]

Check if the Inventories are running correctly.

[sccmnoobie]

Hi Peter,

 

Thanks for you response. I'm not sure how to check the inventory, however when running a report against the resource that says, say, Sophos isn't installed; it shows it's there.

 

Is there any instructions you can provide to check the inventory, or have other suggestions?

 

Many thanks indeed!

[Peter van der Woude]

The quickest test is to right-click a client, of which you know that it should have the software, click Start, click Resource Explorer and search for the software.

[sccmnoobie]

Hi Peter,

 

Thanks for that. The software is showing in ResourceExplorer...

 

Resource Explorer / Software / Product Details / EMC Corporation / Avamar Windows Client

 

Does my query look correct?

 

Thanks!

[Peter van der Woude]

At first look I think your query should looks good... About where you where looking in the Resource Explorer, Add/ Remove Programs is part of the Hardware Inventory (to keep things easy). So you should look under there...

[sccmnoobie]

Thanks Peter. I have navigated to Resource Explorer / Hardware / Add/Remove Programs (64), and there is an entry in the Display Name saying EMC Avamar for Windows x64 Editions.

[sccmnoobie]

Any other ideas, guys?

[Peter van der Woude]

So it seems the inventories are running correctly. Then I would look at the query, see here for a good example: http://www.sccm-tools.com/tools/wqlqueries/WQLQueries-xnotinstalled.html

[sccmnoobie]

Just following up on this. I have a solution. The query was for the most part correct, however it was missing the ARP64 (where Avamar is).

 

Here is the query:

 

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 LIKE "%Avamar%") and SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName LIKE "%Avamar%")and SMS_R_System.Client = 1 and SMS_R_SYSTEM.OperatingSystemNameAndVersion like 'Microsoft Windows NT%Server%'