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.
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
Technical Solutions Consultant
Xcend Group
http://xcendgroup.com
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
Technical Solutions Consultant
Xcend Group
http://xcendgroup.com
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
Technical Solutions Consultant
Xcend Group
http://xcendgroup.com
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.