Video Screencast Help
Search Video Help Close Back
to help
New in the Rewards Catalog: Vouchers for "Symantec Technical Specialist" and "Symantec Certified Specialist" exams.

Create a collection requiring software

Updated: 21 May 2010 | 6 comments
mickwearn's picture
0 0 Votes
Login to vote
This issue has been solved. See solution.

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

jharings's picture
18
Dec
2008
0 Votes 0
Login to vote

Hey Mick,

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 ) which is why the reports don't translate well. Anyway, start with this, see how it works. I can try and customize by guessing.



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.

mickwearn's picture
18
Dec
2008
0 Votes 0
Login to vote

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.

jharings's picture
18
Dec
2008
0 Votes 0
Login to vote

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.

mickwearn's picture
18
Dec
2008
0 Votes 0
Login to vote

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.





jharings's picture
18
Dec
2008
0 Votes 0
Login to vote

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.

mickwearn's picture
18
Dec
2008
0 Votes 0
Login to vote

Nice Work



It works awesome,



I've never used the 'Guid not in ' statement before..



Thanks Heaps for your help



Cheers,



Mick.