Finding software updates in multiple packages

We have done several deployments of software updates in emergency situations where a software update package might contain the files for only one Article ID, such as a package for https://support.microsoft.com/en-us/help/4012598.  These files might also be contained in a second package with other updates, so the first package is no longer necessary… how can this be determined?

I took the Package ID for the software update package containing the one Article ID – XXX00BE9 and listed out the CI_IDs that package contains:

select su.CI_ID from fn_ListUpdateCIs(1033) as su

inner join vSMS_CIToContent as cc on su.CI_ID = cc.CI_ID

inner join vCI_ContentPackages as pc on pc.Content_ID = cc.ContentID

where pc.PkgID = N’XXX00BE9′

Then use that in a where clause to find other packages:

 

select

pc.PkgID

,su.CI_ID

,su.ArticleID

,su.BulletinID

,su.CI_ID

,su.Description

,su.DisplayName

,su.CIInformativeURL

from fn_ListUpdateCIs(1033) as su

inner join vSMS_CIToContent as cc on su.CI_ID = cc.CI_ID

inner join vCI_ContentPackages as pc on pc.Content_ID = cc.ContentID

where su.CI_ID in

(

select su.CI_ID from fn_ListUpdateCIs(1033) as su

inner join vSMS_CIToContent as cc on su.CI_ID = cc.CI_ID

inner join vCI_ContentPackages as pc on pc.Content_ID = cc.ContentID

where pc.PkgID = N’XXX00BE9′

)

Viola!  It’s in another package…

KB012598

SQL Profiler is a wonderful thing…

 

Advertisements

Listing the OUs that contains each machine

select s.Name0, ou.System_OU_Name0 from v_R_System s left join

(

select soun.ResourceID, soun.System_OU_Name0 from v_RA_System_SystemOUName soun inner join

(

select ResourceID, MAX (LEN (System_OU_Name0)) as LongestOULength from v_RA_System_SystemOUName

group by ResourceID

)

as ou on ou.ResourceID = soun.ResourceID and LEN (soun.System_OU_Name0) = ou.LongestOULength

)

as ou

on ou.ResourceID = s.ResourceID

order by s.Name0

 

Finding all collections that are ultimately limited by a collection

We sometimes have to update a particular collection, but how many collections are ultimately limited by that one?  For instance, for “All Systems”:

declare @LimitingCollection nvarchar (8) = ‘SMS00001’;

WITH Recursive_CTE AS

(

SELECT

child.SiteID,

CAST(child.SiteID as varchar(max)) SiteID,

CAST(‘>> ‘ as varchar(max)) LVL,

CAST(child.SiteID as varchar(max)) Hierarchy,

1 AS RecursionLevel

FROM v_Collections child

WHERE SiteID = @LimitingCollection

UNION ALL

SELECT

child.SiteID,

CAST(LVL + child.SiteID as varchar(max)) AS SiteID,

CAST(‘>> ‘ + LVL as varchar(max)) AS LVL,

CAST(Hierarchy + ‘:’ + CAST(child.SiteID as varchar(max)) as varchar(max)) Hierarchy,

RecursionLevel + 1 AS RecursionLevel

FROM Recursive_CTE parent

INNER JOIN v_Collections child ON child.LimitToCollectionID = parent.SiteID

)

SELECT SiteID, Hierarchy, RecursionLevel FROM Recursive_CTE ORDER BY Hierarchy

Change the parameter to give the results for a sub-limiter:

declare @LimitingCollection nvarchar (8) = ‘XXX0067F’;

Results look like:

 

SiteID   Hierarchy                             RecursionLevel
-------- ------------------------------------- --------------
XXX0067F XXX0067F                              1
XXX00680 XXX0067F:XXX00680                     2
XXX00681 XXX0067F:XXX00681                     2
XXX00682 XXX0067F:XXX00681:XXX00682            3
XXX009A8 XXX0067F:XXX00681:XXX00682:XXX009A8   4
XXX00C5F XXX0067F:XXX00681:XXX00682:XXX00C5F   4
XXX01020 XXX0067F:XXX00681:XXX00682:XXX01020   4
XXX013E5 XXX0067F:XXX00681:XXX00682:XXX013E5   4
XXX0152A XXX0067F:XXX00681:XXX00682:XXX0152A   4
XXX0156D XXX0067F:XXX00681:XXX00682:XXX0156D   4
XXX01581 XXX0067F:XXX00681:XXX00682:XXX01581   4
XXX01586 XXX0067F:XXX00681:XXX00682:XXX01586   4
XXX015AC XXX0067F:XXX00681:XXX00682:XXX015AC   4
XXX00683 XXX0067F:XXX00681:XXX00683            3
XXX008B8 XXX0067F:XXX00681:XXX008B8            3
XXX008DC XXX0067F:XXX00681:XXX008DC            3
XXX013D5 XXX0067F:XXX00681:XXX008DC:XXX013D5   4
XXX0141C XXX0067F:XXX00681:XXX008DC:XXX0141C   4
XXX00DD1 XXX0067F:XXX00681:XXX00DD1            3
XXX01342 XXX0067F:XXX00681:XXX01342            3
XXX015C6 XXX0067F:XXX00681:XXX015C6            3

(21 rows affected)

 

Thanks to http://www.kodyaz.com/t-sql/sql-server-recursive-query-with-recursive-cte.aspx for the recursive query logic.

Finding enabled/disabled Task Sequences using a specific package

select

p.packageid

, p.name

, p.description

, case (programflags & 4096) / 4096 when 1 then ‘Disabled’ else ‘Enabled’ end

, c.collectionid

, c.name

, a.advertisementid

, a.advertisementname

from v_advertisement a

inner join v_package p on p.packageid = a.packageid

inner join v_collection c on c.collectionid = a.collectionid

inner join v_tasksequencepackage tsp on tsp.packageid = p.packageid

inner join v_TaskSequencePackageReferences tspr on tspr.PackageID = tsp.PackageID

where tspr.RefPackageID = ‘XXX0056A’

order by p.packageid, c.collectionid

Finding the limiting collection chain for a collection

declare @SiteID varchar (8) = ‘XXX01586’

declare @LimitToCollectionID varchar (8) = (select LimitToCollectionID from vCollections where SiteID = @SiteID)

declare @LinkedList nvarchar (max) = (select SiteID + ‘ – ‘ + CollectionName + CHAR(13) from vCollections where SiteID = @SiteID)

while (1 = 1)

begin

select @SiteID = (select LimitToCollectionID from vCollections where SiteID = @SiteID)

if @SiteID is null

break

set @LinkedList = @LinkedList + (select SiteID + ‘ – ‘ + CollectionName + CHAR(13) from vCollections where SiteID = @SiteID)

end

select @LinkedList

——————————————————————————————-
XXX01586 – ITS OUs in the XXX.ABCDEF.NET Domain

XXX00682 – XXX Workstations with Client

XXX00681 – XXX Workstations

XXX0067F – XXX Devices

XXX0078D – All Systems without Sensitive Data Machines

SMS00001 – All Systems

(1 row(s) affected)

Combined Application Deployment Status Report

Application Deployment Status

It seems that the built-in application deployment status reports require looking at a separate report for each status – success, waiting, error, unknown, requirements not met (I could be wrong, I didn’t look that hard).

I wanted to build a “complete” report for application deployments.  First, I created a function in the secondary database I use (not touching the CM database itself) to display the enforcement state in user friendly text.

— User Friendly Enforcement State

CREATE FUNCTION dbo.EnforcementState (@EnforcementState INT)

RETURNS VARCHAR (50)

AS

BEGIN

DECLARE @ReturnString VARCHAR (50) =

CASE

WHEN @EnforcementState = 1000 THEN ‘Success’

WHEN @EnforcementState = 1001 THEN ‘Already Compliant’

WHEN @EnforcementState = 1002 THEN ‘Simulate Success’

WHEN @EnforcementState = 2000 THEN ‘In progress’

WHEN @EnforcementState = 2001 THEN ‘Waiting for content’

WHEN @EnforcementState = 2002 THEN ‘Installing’

WHEN @EnforcementState = 2003 THEN ‘Restart to continue’

WHEN @EnforcementState = 2004 THEN ‘Waiting for maintenance window’

WHEN @EnforcementState = 2005 THEN ‘Waiting for schedule’

WHEN @EnforcementState = 2006 THEN ‘Downloading dependent content’

WHEN @EnforcementState = 2007 THEN ‘Installing dependent content’

WHEN @EnforcementState = 2008 THEN ‘Restart to complete’

WHEN @EnforcementState = 2009 THEN ‘Content downloaded’

WHEN @EnforcementState = 2010 THEN ‘Waiting for update’

WHEN @EnforcementState = 2011 THEN ‘Waiting for user session reconnect’

WHEN @EnforcementState = 2012 THEN ‘Waiting for user logoff’

WHEN @EnforcementState = 2013 THEN ‘Waiting for user logon’

WHEN @EnforcementState = 2014 THEN ‘Waiting To Install’

WHEN @EnforcementState = 2015 THEN ‘Waiting Retry’

WHEN @EnforcementState = 2016 THEN ‘Waiting For Presentation Mode’

WHEN @EnforcementState = 2017 THEN ‘Waiting For Orchestration’

WHEN @EnforcementState = 2018 THEN ‘Waiting For Network’

WHEN @EnforcementState = 2019 THEN ‘Pending App-V Virtual Environment Update’

WHEN @EnforcementState = 2020 THEN ‘Updating App-V Virtual Environment’

WHEN @EnforcementState = 3000 THEN ‘Requirements not met’

WHEN @EnforcementState = 3001 THEN ‘Host Platform Not Applicable’

WHEN @EnforcementState = 4000 THEN ‘Unknown’

WHEN @EnforcementState = 5000 THEN ‘Deployment failed’

WHEN @EnforcementState = 5001 THEN ‘Evaluation failed’

WHEN @EnforcementState = 5002 THEN ‘Deployment failed’

WHEN @EnforcementState = 5003 THEN ‘Failed to locate content’

WHEN @EnforcementState = 5004 THEN ‘Dependency installation failed’

WHEN @EnforcementState = 5005 THEN ‘Failed to download dependent content’

WHEN @EnforcementState = 5006 THEN ‘Conflicts with another application deployment’

WHEN @EnforcementState = 5007 THEN ‘Waiting Retry’

WHEN @EnforcementState = 5008 THEN ‘Failed to uninstall superseded deployment type’

WHEN @EnforcementState = 5009 THEN ‘Failed to download superseded deployment type’

WHEN @EnforcementState = 5010 THEN ‘Failed to updating App-V Virtual Environment’

WHEN @EnforcementState is null THEN ‘Unknown’

ELSE Cast(@EnforcementState AS VARCHAR(50))

END

RETURN (@ReturnString)

END

I then define the report, specifying a parameter of AssignmentID.  The user sees “Software – Collection (Collection ID)”

— First, get the assignment ID

SELECT SoftwareName + ‘ – ‘ + CollectionName + ‘ (‘ + CollectionID + ‘)’ ApplicationCollection, AssignmentID FROM v_DeploymentSummary ds

WHERE ds.FeatureType = 1 AND SoftwareName <> ”

ORDER BY 1

I took the SQL behind the built in report ConfigMgr_XXX > Software Distribution – Application Monitoring > Software Distribution – Application Monitoring – Hidden > Application deployment detailed status and basically replaced the IF – ELSE IF… structure with UNION ALL (removing some fields I didn’t think were all that relevant, adding others).

— Then pass the assignment ID to the following

DECLARE @PolicyModelID INT

DECLARE @SoftwareName VARCHAR (1000)

DECLARE @CollectionName VARCHAR (1000)

 

SELECT @PolicyModelID = PolicyModelID, @SoftwareName = SoftwareName, @CollectionName = CollectionID + ‘ – ‘ + CollectionName

FROM vDeploymentSummary

WHERE AssignmentID = @AssignmentID

 

SELECT

@SoftwareName AS SoftwareName

, @CollectionName AS CollectionName

, AppState.UserName

, AppState.MachineName

, s.Full_Domain_Name0

, CASE WHEN AppState.StatusType = 1 THEN ‘Success’ ELSE ‘Waiting’ END StatusType

, ” AS RequirementName

, 0 AS RequirementType

, ” AS CurrentReqValue

, NULL AS ErrorCode

, NULL AS HexErrorCode

, AppState.EnforcementState

, CM_Supplemental.dbo.EnforcementState(AppState.EnforcementState) EnforcementStateText

, NULL AS IsDependencyRule

, ” AS UniqueRequirementName

, 0 AS RuleID

FROM vAppDeploymentAssetDetails AppState inner join v_R_System s ON s.ResourceID = AppState.MachineID

WHERE

AppState.PolicyModelID = @PolicyModelID AND

AppState.AssignmentID = @AssignmentID AND

AppState.StatusType in (1, 2) AND

AppState.StatusType = AppState.AppStatusType

UNION ALL

SELECT

@SoftwareName AS SoftwareName

, @CollectionName AS CollectionName

, AppState.UserName

, AppState.MachineName

, s.Full_Domain_Name0

, ‘Requirements Not Met’ AS StatusType

, AppState.RequirementName

, AppState.RequirementType

, AppState.CurrentReqValue

, ” AS ErrorCode

, ” AS HexErrorCode

, AppState.EnforcementState

, CM_Supplemental.dbo.EnforcementState(AppState.EnforcementState) EnforcementStateText

, AppState.IsDependencyRule

, AppState.UniqueRequirementName

, AppState.RuleID

FROM vAppDeploymentRNMAssetDetails AppState inner join v_R_System s ON s.ResourceID = AppState.MachineID

WHERE

(AppState.PolicyModelID = @PolicyModelID) AND

(AppState.AssignmentID = @AssignmentID) AND

(AppState.StatusType = 3) AND

(AppState.StatusType = AppState.AppStatusType)

UNION ALL

SELECT

@SoftwareName AS SoftwareName

, @CollectionName AS CollectionName

, AppState.UserName

, AppState.MachineName

, s.Full_Domain_Name0

, ‘Unknown’ AS StatusType

, ” AS RequirementName

, 0 AS RequirementType

, ” AS CurrentReqValue

, NULL AS ErrorCode

, NULL AS HexErrorCode

, 4000 AS EnforcementState

, CM_Supplemental.dbo.EnforcementState(4000) EnforcementStateText

, NULL AS IsDependencyRule

, ” AS UniqueRequirementName

, 0 AS RuleID

FROM

vCIDeploymentUnknownAssetDetails AppState inner join v_R_System s on s.ResourceID = AppState.MachineID

WHERE

AppState.PolicyModelID = @PolicyModelID AND

AppState.AssignmentID = @AssignmentID

UNION ALL

SELECT

@SoftwareName AS SoftwareName

, @CollectionName AS CollectionName

, AppState.UserName

, AppState.MachineName

, s.Full_Domain_Name0

, ‘Error’ AS StatusType

, ” AS RequirementName

, 0 AS RequirementType

, ” AS CurrentReqValue

, AppState.ErrorCode

, replace (replace (master.dbo.fn_varbintohexstr(AppState.ErrorCode), ‘0xffffffff’, ‘0x’), ‘0x00000000’, ‘0x’) HexExitCode

, AppState.EnforcementState

, CM_Supplemental.dbo.EnforcementState(AppState.EnforcementState) EnforcementStateText

, NULL AS IsDependencyRule

, ” AS UniqueRequirementName

, 0 AS RuleID

FROM

vAppDeploymentErrorAssetDetails AppState INNER JOIN v_R_System s ON s.ResourceID = AppState.MachineID

WHERE

AppState.PolicyModelID = @PolicyModelID AND

AppState.AssignmentID = @AssignmentID AND

AppState.StatusType = 5 AND

AppState.StatusType = AppState.AppStatusType

Remotely query SMS_InstalledSoftware with the command line

C:\Users\Public\Documents>wmic /NODE:COMPUTER.NAME /NAMESPACE:\\root\cimv2\SMS path sms_installedsoftware get Publisher, ProductName
ProductName                                                     Publisher
Tableau 10.2 (10200.17.0505.1445) (32-bit)                      Tableau Software
Configuration Manager Client                                    Microsoft Corporation
Microsoft Endpoint Protection Management Components             Microsoft Corporation
.
.
.