patch or program not installed report (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

alokin123 -> patch or program not installed report (10/2/2008 12:53:52 AM)

my query language skills are a bit rusty so if someone could point me in the right direction that would be great. I am trying to produce a report for a specific program, patch NOT being installed on machines. I've tried following this link:
http://blogcastrepository.com/forums/p/1591/2683.aspx
But i cant seem to get it to work.
I can see that you basically have to set up a query first to find the product and then put that in as a sub query specifying that you want machines returned that don't meet that criteria. I have this so far but as a sub query but when i try and do a "not in" or "not exists" it don't work. Can anyone help:

SELECT v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_COMPUTER_SYSTEM.ResourceID, v_GS_COMPUTER_SYSTEM.Name0, v_GS_COMPUTER_SYSTEM.UserName0
FROM v_GS_ADD_REMOVE_PROGRAMS v_GS_ADD_REMOVE_PROGRAMS, v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM
WHERE v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_GS_COMPUTER_SYSTEM.RevisionID AND ((v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 Like '%KB901017%'))




jnelson993 -> RE: patch or program not installed report (10/2/2008 1:09:45 AM)


If you're using ITMU to patch, I'd use the V_GS_PatchStateEx view. 

If not then the query you're using first of all has an error where you've got a join criteria of ResourceID = RevisionID. That's going to be a problem.  Then, if you're using it as a subselect, you should only be selecting ResourceID, not all that other stuff, in fact you don't need any of the v_GS_Computer_System stuff, since you just need the resourceID which is in V_GS_Add_Remove_Programs.

So, use something like this as the subquery

SELECT
  cs.ResourceID
FROM  
  v_GS_ADD_REMOVE_PROGRAMS v_GS_ADD_REMOVE_PROGRAMS
WHERE  v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%KB901017%'





jnelson993 -> RE: patch or program not installed report (10/2/2008 1:51:16 AM)

BTW, the code that blogcast post is less-than-ideal because of unecessary joins and way too much extra stuff, especially in that second . Don't get me wrong, if it works for you, then don't worry about it, it's not horrible, but I prefer to make sure the code I implement is as tight as possible.  There are several articles out there explaining NOT IN subselect queries, but most or all of them are missing some important (in my mind anyway) optimizations that are needed to make it as clean and efficient as possible.  I'm planning to write an article on this, but it's just based on my time, and we're in the middle of CM planning and rollout so...I'll get to it when I get to it. :)



alokin123 -> RE: patch or program not installed report (10/2/2008 8:17:53 PM)

cool, thanks for your help. I did a bit more googling and and found this link: http://www.myitforum.com/articles/1/view.asp?id=179 and was able to create a collection with machines that didn't have the particular app. 



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.171875