SQL Query Help - Custom Collection

This issue has been solved. See solution.
SegaAges's picture
SegaAges
October 1st, 2009

Hi All,

I'm trying to create a custom collection that only includes the computer name, the computer type (which I only want laptops), and is in a specific OU.  It seems simple enough, and my query works PERFECT in SQL Query Analyzer, but when I plug it into the Custom SQL in Altiris, it returns every record of everything, i.e. software, user names, computer names, everything!

Here is the query:

SELECT     
Inv_OU_Membership.[Distinguished Name],
Inv_AeX_HW_Serial_Number.[Computer Type],
Inv_AeX_AC_Identification.[Name], 
Inv_AeX_AC_Identification.[_ResourceGuid]

From Inv_AeX_AC_Identification  
     
left join Inv_AeX_HW_Serial_Number     
on Inv_AeX_AC_Identification.[_ResourceGuid]=Inv_AeX_HW_Serial_Number.[_ResourceGuid]   
left join Inv_OU_Membership   
on Inv_AeX_AC_Identification.[_ResourceGuid]=Inv_OU_Membership.[_ResourceGuid]     
where    
(Inv_AeX_HW_Serial_Number.[Computer Type] LIKE 'Portable' or    
Inv_AeX_HW_Serial_Number.[Computer Type] LIKE 'Notebook' or    
Inv_AeX_HW_Serial_Number.[Computer Type] LIKE 'Laptop')    
and (( Inv_OU_Membership.[Distinguished Name] = 'OU=TEMECULA,OU=WEST COAST,OU=NORTH AMERICA,OU=ENTERPRISE,DC=CORP,DC=MILLIPORE,DC=COM'))

Can someone help me figure out what I can be doing wrong ?  I really appreciate it!  

Thanks,

-Nick

Filed under: , , , , , ,
0 votes
Robert Lundsten's picture
Robert Lundsten
7 weeks 6 days ago

You need to return the Guid...

Solution

Hey Nick!

Collection queries in Altiris need an Guid returned. Otherwise it does not know what to display. Crazy? Yes, I know :)

Here is a working query;

SELECT    
Inv_AeX_AC_Identification.[_ResourceGuid] AS Guid
From Inv_AeX_AC_Identification 
left join Inv_AeX_HW_Serial_Number    
on Inv_AeX_AC_Identification.[_ResourceGuid]=Inv_AeX_HW_Serial_Number.[_ResourceGuid]  
left join Inv_OU_Membership  
on Inv_AeX_AC_Identification.[_ResourceGuid]=Inv_OU_Membership.[_ResourceGuid]    
where   
(Inv_AeX_HW_Serial_Number.[Computer Type] LIKE 'Portable' or   
Inv_AeX_HW_Serial_Number.[Computer Type] LIKE 'Notebook' or   
Inv_AeX_HW_Serial_Number.[Computer Type] LIKE 'Laptop')   
and (( Inv_OU_Membership.[Distinguished Name] = 'OU=TEMECULA,OU=WEST COAST,OU=NORTH AMERICA,OU=ENTERPRISE,DC=CORP,DC=MILLIPORE,DC=COM'))

Hope it helps!

Robert Lundsten
Senior Technical Advisor

Asterio | http://www.asterio.se
Symanji | http://www.symanji.se

0 votes
SOLUTION
SegaAges's picture
SegaAges
7 weeks 6 days ago

Hey Robert, I can't believe

Hey Robert,

I can't believe how easy that was...  I am fairly new to Altiris, but I have searched and searched, and just last night realized there was a special way to write collection SQL query's.  Do you know if there is any documentation that describes exactly how to do it, or is it just the same as normal SQL, but you have to include the GUID ? 

That query worked like a charm, you have saved me soooo much heart ache, thank you !!!

Take care,

-Nick

0 votes