Jump to content


sccmnoobie

Collection query not reporting back accurately

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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%'

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.