Query based on collection

[Barnekipha]

I have created a report which looks like it might be working. I get info from it.

Username (last logged on user), Production year, Serial number, Producer, Model, and memory / disk space.

 

One of the last things remaining: How do I run this on a collection?

 

SELECT distinct

CS.name0 as 'Namen',

substring(CS.UserName0,11,8) as 'User',

Convert (varChar(30), BIOS.ReleaseDate0,102) 'Production Year',

BIOS.SerialNumber0 as 'Serial number',

CS.Manufacturer0 as 'Producer',

CS.Model0 as 'Model',

sum(isnull(RAM.TotalPhysicalMemory0/1024,'0')) as 'Memory MB',

sum(isnull(LDisk.Size0/1024,'0')) as 'Disk Drive GB',

sum(isnull(LDisk.FreeSpace0/1024,'0')) AS 'Free Diskspace'

from

v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID

right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID

right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID

right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID

right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID

right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID

right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID

right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID

where

LDisk.DriveType0 =3

group by

BIOS.ReleaseDate0,

CS.Name0,

CS.domain0,

CS.Username0,

BIOS.SerialNumber0,

SE.SerialNumber0,

CS.Manufacturer0,

CS.Model0,

OS.Caption0,

RAA.SMS_Assigned_Sites0,

RAM.TotalPhysicalMemory0

ORDER BY Convert (varChar(30), BIOS.ReleaseDate0,102) ASC

[GarthMJ]

Here is an example on how to limit a query to a collection.

http://smsug.ca/blogs/garth_jones/archive/2014/01/13/limiting-a-report-to-a-collection.aspx