Jump to content


bostonrake

Query To Find Computers That Do NOT Have Certain Software

Recommended Posts

Hello,

I'm new to SCCM and 5 weeks into a new job. We have SCCM 2007 and several queries to help me out, but nothing seems accurate.

 

There is this one query that is supposed to show all computers that do NOT have GlobalProtect. The query statement is...

 

select SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName, SMS_R_System.IPAddresses, SMS_G_System_COMPUTER_SYSTEM.SystemType 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "GlobalProtect") order by SMS_G_System_SYSTEM.Name

 

When I click on the query, it finds hundreds of computers which we know is not accurate. Matter of fact, my laptop shows in the results and I have GlobalProtect installed.

 

Could it be this statement isn't correct? Will this statement work for Windows 7 computers? I know absolutely nothing about CCM other than what it is capable of doing. I have been able to update some distributions, but so far that is about it.

 

Thanks,

Doug

Share this post


Link to post
Share on other sites

Sorry, I'm new to ccm and queries. I honestly don't know a thing about them, so when you say "sub-select queries," I have no idea what you are talking about.

 

 

You create it the same way you created the one above. How exactly did you great that one?

Share this post


Link to post
Share on other sites

 

You create it the same way you created the one above. How exactly did you great that one?

It was already in there. At first, I thought great, there is already a query to show me the computers that don't have GlobalProtect. However, after running it, we immediately knew it wasn't correct. Now that I know it is 32 bit only, I'm sure that is the problem. I have never created a query from scratch.

 

Thanks,

dd

Share this post


Link to post
Share on other sites

I'm close... I think. I ran the following to show me systems that do have GlobalProtect for both the 32 and 64 bit versions....

 

select SMS_R_System.NetbiosName from SMS_R_System where SMS_R_System.ResourceId 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 = "GlobalProtect") or SMS_R_System.ResourceId 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 = "GlobalProtect") order by SMS_R_System.NetbiosName

 

I then created another query and copied the above statement but added the "NOT" shown below...

 

select SMS_R_System.NetbiosName from SMS_R_System where SMS_R_System.ResourceId 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 = "GlobalProtect") or 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 = "GlobalProtect") order by SMS_R_System.NetbiosName

 

The query that shows the computers that DO have it installed seems accurate... matter of fact it jives perfectly with the report from CCM when I use "Computers with specific software registered in Add Remove Programs"... the numbers are the exact same. So I'm confident the first query showing computers that have it is correct.

 

However, the 2nd query where I tried to change it to show computers that do NOT have it, I don't think that is correct as it is showing more computers than we anticipate.

 

Is there anyone that can take a look at my 2nd query? Again, I'm trying to find all computers that do NOT have GlobalProtect and it needs to include both 32 and 64 bit.

 

Thanks,

Doug

Share this post


Link to post
Share on other sites

Finally got it! Here is the query to show computers that do NOT have GlobalProtect for both 32 and 64 bit...

 

select SMS_R_System.NetbiosName 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 = "GlobalProtect") 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 = "GlobalProtect") order by SMS_R_System.NetbiosName

 

 

Now, how do I add the last logged on user to this? Right now I just have the computer name.

 

Thanks,

Doug

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.