Blog

Get the Status of All Update Deployment Packages

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

Showing all Computers (With Boot Time)

Showing All computers in SCCM that have software installed, with the last boot time listed



DECLARE @lookup as varchar (50)
set @lookup = ‘%’
SELECT     TOP (100) PERCENT dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0,
DATEDIFF(Day,dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) AS [Days since last boot],
dbo.v_GS_OPERATING_SYSTEM.Caption0

FROM         dbo.v_GS_OPERATING_SYSTEM 
INNER JOIN dbo.v_R_System ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE     dbo.v_R_System.Name0 in 

(SELECT     vrs.Name0 FROM v_r_system AS vrs
JOIN v_GS_INSTALLED_SOFTWARE AS VIS ON VIS.resourceid = vrs.resourceid
JOIN v_GS_SoftwareFile AS sf ON SF.resourceid = vrs.resourceid

WHERE     VIS.productname0 LIKE @lookup 

GROUP BY vrs.Name0)


ORDER BY [Days since last boot]


Getting All Machines Containing a Specific Software Package

So, Let’s say you need to see all machines that contain a specific software package, you can use sccm’s gui, or you can do it in SQL, where you can act on the results:

 
declare @LookupString as nvarchar 
set @LookupString = '%'
 
SELECT     vrs.Name0
FROM       v_r_system AS vrs 
JOIN   v_GS_INSTALLED_SOFTWARE AS VIS ON VIS.resourceid = vrs.resourceid 
JOIN  v_GS_SoftwareFile AS sf ON SF.resourceid = vrs.resourceid                         
WHERE     VIS.productname0 LIKE @LookupString
GROUP BY vrs.Name0

NAME0

LSEU0778
LSTW0631
MACS4268
LSAM9049
LSGR0590