Question from configmgr technet forum : Need SQL Query for disk space <500 MB (windows drive) for servers from most recent 10 records.
Whenever there is any such request on reporting ,i always make use of SQL views documentation https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b. This excel sheet contains list of all SQL view+some functions that would help you to identify the information stored in where so it would be easier for you pick the right SQL views to join with other views to prepare your query.
History of disk space information is stored in v_HS_LOGICAL_DISK ,where as the current inventory information is stored in v_GS_LOGICAL_DISK .
By Default,when you query v_HS_LOGICAL_DISK ,it gives you information with all the existing rows but how do i select the most recent 10 records for each computer ?
Below query gives you the information with most recent 10 records based on the timestamp for each computers.
with diskspace as (
SELECT SYS.Name AS [Server Name], CASE WHEN RSYS.Is_Virtual_Machine0 = 1 THEN 'Virtual Machine' ELSE 'Physical Machine' END AS [Hardware Type],
vHSLDISK.TimeStamp AS [Scaned Date], vHSLDISK.DeviceID0 AS [System Drive C:], vHSLDISK.FreeSpace0 AS [Free space (MB)]
, ROW_NUMBER() over (partition by SYS.Name order by vHSLDISK.TimeStamp desc) rno
FROM v_FullCollectionMembership AS SYS INNER JOIN
v_HS_LOGICAL_DISK AS vHSLDISK ON SYS.ResourceID = vHSLDISK.ResourceID INNER JOIN
v_R_System AS RSYS ON SYS.ResourceID = RSYS.ResourceID inner join
v_GS_LOGICAL_DISK AS LDISK ON SYS.ResourceID = LDISK.ResourceID
WHERE (vHSLDISK.DeviceID0 = 'c:') AND (SYS.CollectionID = 'P0100831') AND (vHSLDISK.FreeSpace0 <= 500)
)
select [Server Name],[Hardware Type],[Scaned Date],[System Drive C:],[Free space (MB)] from diskspace
where rno<=10
Change the Collection ID ,Drive letter and disk Space value.
Reference TechNet Article : http://technet.microsoft.com/en-us/library/ms186734.aspx