Patching reports C.1 Report sample

Patching reports C.1 Report sample

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:- http://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) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) as varchar(256)) + '%' AS '%Compliant',

  [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)]

From

(select

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

count(*) as NumberOfComputers

from v_CIAssignment a

join v_AssignmentState_Combined assc

on a.AssignmentID=assc.AssignmentID

join v_StateNames sn

on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,

      sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)])

) AS pvt

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 a SMS2003 or ConfigMgr backup completes, a Status Message is produced by the SMS_SITE_BACKUP component and processed by the Site server. As you probably know, Status Messages replicate up the hierarchy, so the Central Site server will know all Status Messages being "sent up".

I've had this code running for a few days, it seems to work a charm.

There is one thing that can get in the way though, customised Status Filter Rules. If you have them, you understand them (unless you've inherited in which case GIYF), and know that they can block Status Messages from being processed or delivered to the Parent Site server (drop all informational for example, as the last rule in the rule set, yes some folks do this!).

The T-SQL to bring back the Status Messages, and to render a list of machines that do not fit the logic (no backup reported, ever) is here:

SELECT vsite.ServerName AS Servername, vsite.SiteCode, MAX(stat.Time) AS LastTime

FROM vStatusMessages AS stat LEFT OUTER JOIN

StatusMessageInsStrs AS ins ON ins.RecordID = stat.RecordID LEFT OUTER JOIN

StatusMessageAttributes AS att1 ON att1.RecordID = stat.RecordID LEFT OUTER JOIN

v_Site AS vsite ON vsite.ServerName = stat.MachineName

WHERE (vsite.Type = 2) AND (stat.Component = 'SMS_SITE_BACKUP') AND (stat.MessageID = 5035)

GROUP BY vsite.ServerName, vsite.SiteCode

HAVING (MAX(stat.Time) < DATEADD(day, - 1.5 , GETDATE()))

ORDER BY LastTime

select 'Sites that have never reported a backup (Investigate any server that is listed)'

select servername from v_site where type = 2 and servername not in

(SELECT vsite.ServerName AS Servername

FROM vStatusMessages AS stat LEFT OUTER JOIN

StatusMessageInsStrs AS ins ON ins.RecordID = stat.RecordID LEFT OUTER JOIN

StatusMessageAttributes AS att1 ON att1.RecordID = stat.RecordID LEFT OUTER JOIN

v_Site AS vsite ON vsite.ServerName = stat.MachineName

WHERE (vsite.Type = 2) AND (stat.Component = 'SMS_SITE_BACKUP') AND (stat.MessageID = 5035)

GROUP BY vsite.ServerName, vsite.SiteCode

HAVING (MAX(stat.Time) >= DATEADD(day, - 1.5 , GETDATE())))

 

 

The Query itself is entirely harmless, in that it doesn't UPDATE or DELETE, but you should be aware it can put a load on your DB instance while running. Nothing to big to worry about, unless you are seriously underspec'd hardware-wise.

And finally, be aware that as long as Status Filter rules are not inhibiting the delivery of the Status Message from down-level sites up the hierarchy, and that Sites in your hierarchy are communicating with their Parent's correctly, then you should be OK to rely on this report. I would suggest having some more reports configured to check for any sites that haven't reported in within the last 24hrs (indicative of some kind of failure taking place) and also to look at using dedicated Monitoring tools such as OpMgr to monitor Site servers and Site systems health using the Management Pack.

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 me are described below:

Through the command prompt:

1. On the system which you wish to query, open up command prompt

2. Run "Wmic /namespace:\\root\ccm\softwareupdates\updatesstore path CCM_UpdateStatus get status, Article, Bulletin, UniqueId"

3. This will show you ALL updates on the particular system. I've added additional information for reference but you can always play around with what you wish to see returned.

4. This is best for an overview and you can quickly do a find or filter to determine a patch status if you pipe it to a txt file.

Through the Wbemtest User Interface:

1. Start "WBEMTEST" from a run prompt

2. Connect to the namespace "\\<computername>\root\ccm\softwareupdates\updatesstore

3. Select the Query button and using WQL, create a statement that suits your needs. Some examples are below:

   1. • Searching for a Missing Patch that is for Web Components would be:

        • select * from ccm_updatestatus where status = "missing" and title like "%web%"

      • Searching for all installed patches would be:

        • select * from ccm_updatestatus where status = 'installed'

Depending on your requirements / needs, have fun! Wbemtest is very useful for quick references to WMI to determine patch statuses whenever you are suspecting the integrity of data received from SCCM due to possible sync issues.

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_and0
FROM         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.ResourceID
WHERE     (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.CollectionID
FROM         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 OUTER JOIN
                      dbo.v_Update_ComplianceStatusAll AS UCS ON SYS.ResourceID = UCS.ResourceID INNER JOIN
                      dbo.v_UpdateInfo AS UI ON UCS.CI_ID = UI.CI_ID ON dbo.v_FullCollectionMembership.ResourceID = UCS.ResourceID
WHERE     (UCS.Status IN ('3', '2')) AND (dbo.v_Collection.CollectionID = 'CollectionID')
ORDER BY UI.ArticleID

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 dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE     (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '3') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '4') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '5') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '6') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '7') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '15') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '16')
ORDER BY [Computer Name]

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 based on its chassis Types.

Below are listed the Chassis types available to create SCCM collection or reports.

For Laptops Chassis Types : 8 , 9, 10, 11, 12, 14, 18, 21

For Desktop Chassis Type : 3, 4, 5, 6, 7, 15, 16

For server Chassis  Type: 23

 

 

 

 

 

 

 

 

SELECT     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 dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE     (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '8') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '9') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '10') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '11') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '12') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '14') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '18') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '21')

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 CPU Speed',
CPU.Name0 as 'CPU Model',
CPU.Is64Bit0 as '64 Bit Compatible'
from 
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID 
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
where
LDisk.DriveType0 =3
group by
CS.Name0,
CS.domain0,
CS.Username0,
BIOS.SerialNumber0,
SE.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
RAA.SMS_Assigned_Sites0,
RAM.TotalPhysicalMemory0,
CPU.MaxClockSpeed0,
CPU.Name0,
CPU.Is64Bit0
ORDER BY CS.name0