patch or program not installed report

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

patch or program not installed report

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> patch or program not installed report

Page: [1]

Message

<< Older Topic Newer Topic >>

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

alokin123

Posts: 28
Score: 0
Joined: 8/7/2008
Status: offline

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

Post #: 1

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

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to alokin123)

Post #: 2

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

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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. :)

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)

Post #: 3

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

alokin123

Posts: 28
Score: 0
Joined: 8/7/2008
Status: offline

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.

(in reply to jnelson993)

Post #: 4