reports
A consolidated Report for Patch Deployments
to get the consolidated Report for all your deployments / instead of depending on dashboard you can view this PVT Report Source:- https://blog.coretech.dk/kea/status-report-for-software-update-deployments/ Select Deploymentname, Available, Deadline, cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + … Read more
SCCM / SMS Backup Report
To know what sites haven’t backed up within a given date range (a day and a half since last backup), and those that have never backed up (never reported a backup). Groovy stuff, not so easy to determine though, or so it would seem, unless you fall back entirely onto the Status Message sub-system. When … Read more
Checking Patch Statuses through WMI
Normally the optimal and quickest way to determine if a patch has definitely been installed on a system is to use WMI. That’s where the SCCM client primarily gets its information from to report back to its parent. There are several ways you can go about but the ways i’ve found to be ideal for … Read more
SQL Report with the systems Names and Architecture for specific collection
finding the systems Names and Architecture for specific collection SELECT dbo.v_Collection.CollectionID, dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_GS_COMPUTER_SYSTEM.SystemType0, dbo.v_GS_COMPUTER_SYSTEM.UserName0, dbo.v_R_System.Operating_System_Name_and0FROM dbo.v_Collection INNER JOIN dbo.v_FullCollectionMembership ON dbo.v_Collection.CollectionID = dbo.v_FullCollectionMembership.CollectionID INNER JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceIDWHERE (dbo.v_Collection.CollectionID = ‘CollectionID’)
Required Patches based on collection ID
SELECT DISTINCT TOP (100) PERCENT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code], CASE WHEN UCS.Status = ‘2’ THEN ‘Applicable’ WHEN UCS.Status = ‘3’ THEN ‘Installed’ ELSE ” END AS ‘Patch Status’, UI.BulletinID, UI.ArticleID, UI.Title, dbo.v_FullCollectionMembership.ResourceID, dbo.v_Collection.CollectionIDFROM dbo.v_FullCollectionMembership INNER JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN dbo.v_R_System AS SYS LEFT … Read more
sccm For Desktops only report
For Desktops only report SELECT TOP (100) PERCENT dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name], dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type], dbo.v_GS_SYSTEM.SystemType0 AS [System Type]FROM dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_SYSTEM ON … Read more
SQL Report for only Laptop Computers
If your boss ask to get list of laptops which are managed by SMS or SCCM.what do you do and how do you get that. Right click on computer and go to resource explorer to identify the computer is Laptop or Desktop ? You can identify if the computer is Laptop or Desktop … Read more
SCCM report for hardware specs of all desktops and laptops on the domain
—select * from v_GS_SYSTEM_ENCLOSURE SELECT distinct CS.name0 as ‘Computer Name’, CS.domain0 as ‘Domain’, CS.UserName0 as ‘User’, BIOS.SerialNumber0 as ‘Bios serial’, SE.SerialNumber0 as ‘System Enclosure serial’, CS.Manufacturer0 as ‘Manufacturer’, CS.Model0 as ‘model’, OS.Caption0 as ‘OS’, RAA.SMS_Assigned_Sites0 as ‘Site’, RAM.TotalPhysicalMemory0 as ‘Total Memory’, sum(isnull(LDisk.Size0,’0′)) as ‘Hardrive Size’, sum(isnull(LDisk.FreeSpace0,’0′)) AS ‘Free Space’, CPU.MaxClockSpeed0 as ‘Max … Read more
