Create a collection requiring software
Hi There,
I want to build a collection of computers which are in need of a software patch. I know I can create a collection of computers with the patch, and then exclude this from another collection called computer requiring the patch but its kinda messy.
It would be really good if there was a way to build a query which says
where Inv_AeX_OS_Add_Remove_Programs.[Name] NOT LIKE '%KB884020' which returns all computers which is correct because Altiris interprets this as return all rows NOT LIKE '%KB884020' rather then just all the computers where this Where clause is true.
I was looking at this report http://forums.altiris.com/mess...id=52900&enterthread=y
Which works in report builder or SQL Query Analyser, but it doesn't work in a collection.
So just to re-cap, is there a way to build a single collection showing just computers which are requiring an up date.
Any ideas?
Thanks Heaps,
Mick.
Comments
Hey Mick,
) which is why the reports don't translate well. Anyway, start with this, see how it works. I can try and customize by guessing.
I don't have a full database to test against, but the important thing to remember is that collections always use guids (typically from vResource, vComputer, and maybe the basic inventory table - dang, I'm lazy
select guid
from vComputer
where guid not in
(
select [_ResourceGuid]
from Inv_AeX_OS_Quick_Fix_Engineering
where [hotfix id] like 'KB884020%'
)
Jim
Jim Harings
HP Enterprise Services
1st Rule of Connect Club: Mark the post that helped you the most as a 'solution'. 2nd Rule of Connect Club:You must talk about Connect club.
Hey Jim,
Thanks for that, it works pretty good.
it just needs one more tweak in it to only include computers where the OS is 'Microsoft Windows XP'
I tried adding this line to the end of your query
AND (Inv_AeX_OS_Operating_System.[OS Name] = 'Microsoft Windows XP') but it doesn't work..
This query will save us a lot of time and effort. Thanks again for your help and quick reply.
Cheers,
Mick.
select guid
from vComputer
where guid in
(
select [_ResourceGuid]
from Inv_AeX_OS_Operating_System
where [os name] = 'Microsoft Windows XP'
)
AND
where guid not in
(
select [_ResourceGuid]
from Inv_AeX_OS_Quick_Fix_Engineering
where [hotfix id] like 'KB884020%'
)
- formatted kind of bad, but give it a go.
Jim Harings
HP Enterprise Services
1st Rule of Connect Club: Mark the post that helped you the most as a 'solution'. 2nd Rule of Connect Club:You must talk about Connect club.
Kinda works..
This bit works
select guid
from vComputer
where guid in
(
select [_ResourceGuid]
from Inv_AeX_OS_Operating_System
where [os name] = 'Microsoft Windows XP'
)
but if I run the query with this bit it doesn't work.
AND
where guid not in
(
select [_ResourceGuid]
from Inv_AeX_OS_Quick_Fix_Engineering
where [hotfix id] like 'KB884020%'
)
I get the error 'Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'where'.
If I run this with out the OS name it also works.
select guid
from vComputer
where guid not in
(
select [_ResourceGuid]
from Inv_AeX_OS_Quick_Fix_Engineering
where [hotfix id] like 'KB884020%'
)
Something funny between the two Where statements... my logic says it should work?
Mick.
Last shot until tomorrow, when I can look at a real db.
select guid
from vComputer vc, Inv_AeX_OS_Operating_System os
where vc.guid = os.[_resourceguid] and
os.[os name] = 'Microsoft Windows XP'
and vc.guid not in
(
select [_ResourceGuid]
from Inv_AeX_OS_Quick_Fix_Engineering
where [hotfix id] like 'KB884020%'
)
Jim Harings
HP Enterprise Services
1st Rule of Connect Club: Mark the post that helped you the most as a 'solution'. 2nd Rule of Connect Club:You must talk about Connect club.
Nice Work
It works awesome,
I've never used the 'Guid not in ' statement before..
Thanks Heaps for your help
Cheers,
Mick.
Would you like to reply?
Login or Register to post your comment.