Tag : sql

SCCM Report for Disk Free Space Under 20%

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:

 


SCCM Report – Software Metering Report for Last Usage Over 30 Days Ago

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=?”


SCCM Reports – Software Update Status

This SCCM report is really a complicated one, I used lots of views to do the information I need, such as : AD Site, Machine Name, Last Logon User, Last Update Scan, Last Logon Timestamp, Update Status, Operating System, Email, Department, Job Title, and Manager Name.

 

and here is the table view map:

here is the SQL statement:

SELECT dbo.v_R_System.AD_Site_Name0 AS [AD Site], dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [Last Logon User], CONVERT(VARCHAR(26),
dbo.v_UpdateScanStatus.LastScanTime, 100) AS [Last Update Scan], CONVERT(VARCHAR(26), dbo.v_R_System.Last_Logon_Timestamp0, 100) AS [Last Logon Timestamp],
CASE WHEN dbo.v_Update_ComplianceStatusAll.Status = ‘0’ THEN ‘Unkonwn’ WHEN dbo.v_Update_ComplianceStatusAll.Status = ‘1’ THEN ‘Not Required’ WHEN dbo.v_Update_ComplianceStatusAll.Status = ‘2’ THEN
‘Needs Update’ WHEN dbo.v_Update_ComplianceStatusAll.Status = ‘3’ THEN ‘Update Installed’ END AS ‘Update Status’, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_R_User.Mail0,
dbo.v_R_User.department0, dbo.v_R_User.title0, dbo.v_R_User.manager0
FROM dbo.v_R_System INNER JOIN
dbo.v_Update_ComplianceStatusAll ON dbo.v_R_System.ResourceID = dbo.v_Update_ComplianceStatusAll.ResourceID INNER JOIN
dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID INNER JOIN
dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_Update_ComplianceStatusAll.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
dbo.v_UpdateScanStatus ON dbo.v_Update_ComplianceStatusAll.ResourceID = dbo.v_UpdateScanStatus.ResourceID INNER JOIN
dbo.v_AuthListInfo ON dbo.v_Update_ComplianceStatusAll.CI_ID = dbo.v_AuthListInfo.CI_ID INNER JOIN
dbo.v_R_User ON dbo.v_R_System.User_Name0 = dbo.v_R_User.User_Name0

 

For SSRB, I add these two as the parameters:

where [email protected]_title and [email protected]_name


SCCM Reports – No SCCM agent workstation client

select all SMS_R_System.Name0,SMS_R_System.Last_Logon_Timestamp0,SMS_R_System.Resource_Domain_OR_Workgr0,SMS_R_System.Client0,SMS_R_System.Operating_System_Name_and0 from vSMS_R_System AS SMS_R_System
WHERE (Client0 IS NULL) AND (Operating_System_Name_and0 NOT LIKE N’%OS X%’) AND (Operating_System_Name_and0 LIKE N’%Microsoft Windows NT Workstation%’)
ORDER by 2


SCCM Reports – Last Hardware Scan Over 37 Days

I am working on SCCM reports a lot lately, here is the SQL statement for Last Hardware Scan Over 37 Days

SELECT dbo.v_R_System.AD_Site_Name0, dbo.v_R_System.Name0, dbo.v_R_System.User_Name0, dbo.v_R_System.Operating_System_Name_and0, dbo.v_R_User.department0, dbo.v_R_User.Mail0,
dbo.v_R_User.title0, dbo.v_GS_WORKSTATION_STATUS.LastHWScan
FROM dbo.v_GS_WORKSTATION_STATUS INNER JOIN
dbo.v_R_System ON dbo.v_GS_WORKSTATION_STATUS.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_R_User ON dbo.v_R_System.User_Name0 = dbo.v_R_User.User_Name0
WHERE (DATEDIFF(day, dbo.v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) >= 37)
order by 8