Tag : sccm reporting

Here is the SQL  statement for my SCCM Report on disk free space under 20%, it will display the user machine AD Site, PC Name, Last Logon User, Total Disk Space, Free Space, Free Space Percent( All in GB), Drive Name, Model Name ( Lenovo models will be on Model #1, the rest will be on Model #2), OS, User Work Email, Title, Department, Employee Location, and Employee Manger information.

SELECT TOP (100) PERCENT RV.AD_Site_Name0, v_R_User_1.description0, RV.Netbios_Name0, RV.User_Name0, dbo.v_GS_LOGICAL_DISK.Size0 / 1024 AS [Size(GB)],
dbo.v_GS_LOGICAL_DISK.FreeSpace0 / 1024 AS [Free Space], 100 * dbo.v_GS_LOGICAL_DISK.FreeSpace0 / dbo.v_GS_LOGICAL_DISK.Size0 AS [Free Space Percent], dbo.v_GS_LOGICAL_DISK.Name0,
dbo.v_GS_COMPUTER_SYSTEM_PRODUCT.Version0, dbo.v_GS_COMPUTER_SYSTEM.Model0, OS.Caption0, v_R_User_1.department0, v_R_User_1.Mail0, v_R_User_1.manager0, v_R_User_1.title0
FROM dbo.v_R_System_Valid AS RV INNER JOIN
dbo.v_R_User AS v_R_User_1 ON RV.User_Name0 = v_R_User_1.User_Name0 INNER JOIN
dbo.v_GS_LOGICAL_DISK ON RV.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM_PRODUCT ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM AS OS ON RV.ResourceID = OS.ResourceID CROSS JOIN
dbo.v_R_User
WHERE (100 * dbo.v_GS_LOGICAL_DISK.FreeSpace0 / dbo.v_GS_LOGICAL_DISK.Size0 < 20)
GROUP BY RV.Netbios_Name0, RV.User_Name0, OS.Caption0, RV.AD_Site_Name0, v_R_User_1.department0, v_R_User_1.description0, v_R_User_1.Mail0, v_R_User_1.manager0, v_R_User_1.title0,
dbo.v_GS_LOGICAL_DISK.FreeSpace0, dbo.v_GS_LOGICAL_DISK.Name0, dbo.v_GS_LOGICAL_DISK.Size0, dbo.v_GS_COMPUTER_SYSTEM_PRODUCT.Version0, dbo.v_GS_COMPUTER_SYSTEM.Model0
HAVING (dbo.v_GS_LOGICAL_DISK.Name0 = N’C:’)
ORDER BY [Free Space Percent]

Also, it will show red if the free space under 5%, orange under 10%, and the rest in green color. Here is how you can color coding the data in SSRB:

Here is my SQL Statement for SCCM Report – Software Metering Report for Last Usage Over 30 Days Ago

SELECT DISTINCT
TOP (100) PERCENT RV.AD_Site_Name0, v_R_User_1.description0, RV.Netbios_Name0, RV.User_Name0, MAX(MUS.LastUsage) AS Expr1, OS.Caption0, RV.Creation_Date0, v_R_User_1.department0,
v_R_User_1.Mail0, v_R_User_1.manager0, v_R_User_1.title0, MF.MeteredFileName, MF.MeteredFileVersion
FROM dbo.v_R_System_Valid AS RV INNER JOIN
dbo.v_MonthlyUsageSummary AS MUS ON RV.ResourceID = MUS.ResourceID INNER JOIN
dbo.v_MeteredFiles AS MF ON MUS.FileID = MF.MeteredFileID INNER JOIN
dbo.v_R_User AS v_R_User_1 ON RV.User_Name0 = v_R_User_1.User_Name0 LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM AS OS ON RV.ResourceID = OS.ResourceID CROSS JOIN
dbo.v_R_User
WHERE (DATEDIFF(day, MUS.LastUsage, GETDATE()) > 30) AND (MF.RuleID = ? OR
MF.RuleID = ? OR
MF.RuleID = ? )
GROUP BY RV.Netbios_Name0, RV.User_Name0, OS.Caption0, RV.AD_Site_Name0, RV.Creation_Date0, v_R_User_1.department0, v_R_User_1.description0, v_R_User_1.Mail0, v_R_User_1.manager0, v_R_User_1.title0,
MF.MeteredFileName, MF.MeteredFileVersion

ORDER BY Expr1

Add your internal Metering Rule in ” MF.RuleID=?”