How can I make a report from a collection

brentp · 05-19-2009, 05:53 AM

I have to make a report for someone who only has access to view reports.

I want a report to show the info in a few collections.

Say I have 5 collections that are defined by IP range eg 10.117.8.% and are named by the physical site name.
then a 6th collection which is the total of the 5 other collections.

This info from the collection is what I want the report to say.

I managed to create a report which includes all the IP ranges of the 5 collections, and it would list all the computers from the 5 collections, but I need it to say the actual site name so when you see the full list of computers you can see where they are based.

The collections that are there have the info that I need, but its a collection not a report.

How can I make a report from a collection like this?

Thanks

Brandon · 05-19-2009, 08:19 PM

Option 1. Make them shortcuts and give them access to the shortcut tab.

Option 2. Make a generic report like select * from vcomputer but then join it on the collection guids.

Code:

SELECT * from vcomputer vc 
  JOIN CollectionMembership T1  
   ON T1.ResourceGuid = vc.[GUID] 
  JOIN vCollection T2  
   ON T2.Guid = T1.CollectionGuid  
 WHERE T2.Guid = '{GUID1}'
OR  T2.Guid = '{GUID2}' -- etc...
GROUP BY vc.[Name]

Get the guids by right clicking on the collection and 'view as xml' then grab it from the url.

Cut me some slack if there is a syntax issue since I just wrote this on here without running it.

TRYDL · 05-19-2009, 09:44 PM

Brandon is certainly correct, but not all of us can translate his SQL that easily

. So I added a quick URL to hopefully get you pointed in the right direction

https://kb.altiris.com/article.asp?article=24744&p=1

brentp · 05-20-2009, 01:38 AM

Im sorry but, both of those are over my head. I have been playing around for awhile now still no luck.

what I think I will do, is just do a report for each of the site and the guy can export each site into a spread sheet.

what I have got is
select [Name], [User], [IP Address], [OS Name], [MAC Address], Guid from vComputer where [IP Address] like '10.117.225.%,

How can I get it to include Manufacture, Computer Model, Serial number and even OS would be nice

TRYDL · 05-20-2009, 09:36 AM

Code:

 
select distinct
vc.[Name], vc.[User], vc.[IP Address],
hwsn.[System Manufacturer], hwsn.[Computer Model],
hwsn.[Serial Number], 
vc.[OS Name], vc.[MAC Address], vc.[Guid] 
from vcomputer vc
join wrksta on
wrksta.[Name] = vc.[Name]
join AeXInv_AeX_HW_Serial_Number hwsn on
hwsn.wrkstaid = wrksta.wrkstaid

This should give what you want. And yes I know the JOINed views are depracated but that's ok for now.

Teach a man to fish =
Amazon.com: SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL (2nd Edition): John L. Viescas, Michael J. Hernandez: Books

Brandon · 05-20-2009, 11:15 AM

Thanks TRYDL, I thought I had seen an article but couldn't find it with a search.

brentp · 05-20-2009, 06:02 PM

TRYDL,
Where do I work in the part that states the IP range
"where [IP Address] like '10.117.225.%,"

Edit,
Wait. I shouldnt be doing IP I am supposed to be doing it by subnet