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

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

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