Sign in with
Sign up | Sign in
Your question

SCCM 2007 sql report problem!

Last response: in Business Computing
January 7, 2011 10:55:05 AM

Hello. Dunno if anyone here knows sql, but I'm trying to make a query work that just doesn't want to!
  1. Select distinct SYS.Netbios_Name0
  2. FROM v_GS_SoftwareProduct SP
  3. JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
  4. WHERE SYS.Netbios_Name0 LIKE @var2 AND SYS.Netbios_Name0 NOT IN (SELECT distinct SYS.Netbios_Name0 FROM v_GS_SoftwareProduct SP WHERE SP.ProductName like @var1 AND SYS.Netbios_Name0 like @var2)
  5. Order by SYS.Netbios_Name0

This above is supposed to list all computers with a given netbios name and not with a given software installed. However I am unsuccessful.

My idea is to have the IN statement generate a list of computers with the software installed, and then compared to the name of computers looked for - and only list the non matches.

I don't know if that works, but the script above does not. I produces zero results.
If I use the Where that looks for software as standalone I get 109 results for Microsoft Office 2010 in CK% (and a total of 231 computers in CK%)
I've also tried to remove and invert statements. Basicly it looks as if the Where command inside the IN returns all CK% computers. Because removing the name like @var2 outside it lists roughly the 1500 systems not found with CK%

Any help available?

First time ever I looked at sql was yesterday, so I only know what sites like firstsql tells me - and only read what I perceived as relevant.
January 11, 2011 7:05:39 PM

Why not write the query in the SCCM Console query module?

select distinct SMS_R_System.Name from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FilePath = "yourfilepathhere" and SMS_G_System_SoftwareFile.FileName like "softwarename"

SCCM comes with all the tables and views. As far as variable names, you would need to look to having it prompt for the info, or move further ahead into SSRS to manage the data. will pull the name and the SMS_G_System_SoftwareFile tables will hold all the info you're looking to get.
January 11, 2011 7:08:01 PM

Review this statement as it is the part that appears to be causing your problem:

NOT IN (SELECT distinct SYS.Netbios_Name0 FROM v_GS_SoftwareProduct SP WHERE SP.ProductName like @var1 AND SYS.Netbios_Name0 like @var2)

I would recommend having a collection of computers by either Name or the software product, then write a simple SQL statement to pull the info you are looking to grab.
Related resources
January 11, 2011 7:26:32 PM

a collection could actually do the trick! didn't think of that!
January 12, 2011 12:29:11 PM

I'm not a SQL/DBA person either. Breaking it down with a collection generally helps. If you have the recommended hardware requirements (4 proc, 16GB ram) at least, having a good portion of collections won't really affect your console performance. If it does, make sure you have all the .Net updates. If it still continues, I would move to a remote console on a terminal/citrix server.
January 12, 2011 1:16:48 PM

I collection did the trick - now I just had to include systems NOT in that collection.
I'm still not sure why the above doesn't work, but at this point I don't care.
Now I need to figure out how I can limit a select to not display certain display adapters in a hardware report (the fictional adapters) ... more fighting with that tomorrow :) 
and I need to figure out if there's an entry in the database somewhere that details if a system runs on a crt or lcd monitor - the display tables don't know, probably because windows doesn't. Likely not possible.

Anyway, the system performs fine. Sometimes a query can take time, but that's because the sql server doesn't only host sccm.
The sccm servers themselves are running inside esx with 8 processors and 12gb memory. Most of the time half the memory is unused.
January 13, 2011 7:26:53 PM

The SQL server shouldn't be virtualized if that's how it is currently setup. If it is you may expect to run into additional issues later on.

You can query based on the Hertz refresh rate. LCDs are generally 60Hz I think so that might give you an idea of what is on the system.

Check your PMs, I'm going to send you some info and links. All legit info, just don't want to redirect a lot of traffic. :) 
January 13, 2011 7:58:21 PM

Almost everything we have is virtual. Safer that way. Not sure if the sql is though.
As for 60hz - not a bad idea :)