Query To Find Computers That Do NOT Have Certain Software

[bostonrake]

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

[GarthMJ]

That query looks fine. But keep in mind that it will only work on the x86 version of "GlobalProtect".

[bostonrake]

That query looks fine. But keep in mind that it will only work on the x86 version of "GlobalProtect".

Ah ha... I wonder if that is my problem. How can I run a query that will show which computers do not have it for both 32 and 64 bit?

[GarthMJ]

Ah ha... I wonder if that is my problem. How can I run a query that will show which computers do not have it for both 32 and 64 bit?

 

Simple, use multiple sub-select queries.

[bostonrake]

 

Simple, use multiple sub-select queries.

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.

 

Thanks,

Doug

[GarthMJ]

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?

[bostonrake]

 

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

[bostonrake]

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

[bostonrake]

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