SCCM Report Builder Query - Display Information on one row
Posted on 2013-06-25
I have a custom report in SCCM 2012 created with Report Builder 2.0. The report pulls out all of the information I want, but it doesn't display the information for each machine the way I would like.
The report loops through the drives on machines in a specific collection, and returns the drive names (typically C: and D: in our environment with D: being a logical partition, and then return the size of the drive, free space and used space. The query below does all of that, except that it separates the C: and D: drive data into two rows for each pc. I tried 'inner join', but that didn't help. I'm not great with SQL.
SELECT SYS.Name, SYS.SiteCode, LDISK.DeviceID0, LDISK.Description0,
FROM v_FullCollectionMembership SYS
join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID
LDISK.DriveType0 =3 AND
LDISK.Size0 > 0
AND SYS.CollectionID = 'xxx00127'
ORDER BY SYS.Name, LDISK.DeviceID0
The main reason I would like this information on one row is that I am trying to do a simple calculation. I need to be able to verify if there is enough free space on C: to copy all of the used space or data on D: to C:? I think if I can get the information on one row I could make this calculation, but maybe there's an easier way? Regardless, displaying the information on one row would make the report look cleaner. I will take a simple query, anything to have this calculate rather than exporting to Excel and calculating it there.
Any help would be appreciated!