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
.
.
.

Decoding the SMS_ScheduleToken in T-SQL

Edit: SMS_ScheduleToken (document with the SQL)

We wanted to have a report that shows the Assignment Schedule(s) for Task Sequences.  I found that information is contained in the vAdvertisement view; it contains the column MandatorySched, which holds the mandatory schedule information.  There could be multiple Assignment Schedules, and the data looks like:

65BC8B4000100600
00253A000008000000263A0000080000
023E3A400008000002FE3A4000080000003F3A40000800008D3F3A4000080000

One schedule, two schedules, four schedules, respectively.

This first function takes a 16 character string and converts it to the schedule information

create function dbo.ParseScheduleToken (@SMS_ScheduleToken varchar (16))

returns varchar (max)

as

begin

declare @ReturnString varchar (max)

declare @SMS_ScheduleToken1 bigint = convert(bigint, convert(varbinary, substring (@SMS_ScheduleToken, 1, 4), 2))

declare @SMS_ScheduleToken2 bigint = convert(bigint, convert(varbinary, substring (@SMS_ScheduleToken, 5, 4), 2))

declare @SMS_ScheduleToken3 bigint = convert(bigint, convert(varbinary, substring (@SMS_ScheduleToken, 9, 4), 2))

declare @SMS_ScheduleToken4 bigint = convert(bigint, convert(varbinary, substring (@SMS_ScheduleToken, 13, 4), 2))

declare @SMS_ScheduleToken00 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 15) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken01 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 14) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken02 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 13) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken03 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 12) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken04 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 11) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken05 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 10) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken06 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 09) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken07 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 08) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken08 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 07) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken09 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 06) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken10 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 05) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken11 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 04) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken12 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 03) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken13 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 02) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken14 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 01) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken15 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 00) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken16 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 15) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken17 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 14) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken18 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 13) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken19 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 12) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken20 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 11) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken21 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 10) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken22 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 09) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken23 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 08) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken24 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 07) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken25 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 06) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken26 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 05) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken27 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 04) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken28 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 03) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken29 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 02) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken30 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 01) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken31 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 00) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken32 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 15) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken33 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 14) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken34 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 13) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken35 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 12) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken36 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 11) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken37 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 10) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken38 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 09) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken39 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 08) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken40 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 07) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken41 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 06) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken42 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 05) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken43 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 04) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken44 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 03) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken45 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 02) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken46 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 01) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken47 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 00) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken48 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 15) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken49 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 14) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken50 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 13) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken51 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 12) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken52 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 11) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken53 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 10) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken54 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 09) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken55 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 08) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken56 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 07) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken57 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 06) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken58 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 05) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken59 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 04) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken60 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 03) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken61 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 02) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken62 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 01) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken63 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 00) as bigint) = 0 then 0 else 1 end

declare @startminute int = (@SMS_ScheduleToken00 * power (2, 05)) + (@SMS_ScheduleToken01 * power (2, 04)) + (@SMS_ScheduleToken02 * power (2, 03)) + (@SMS_ScheduleToken03 * power (2, 02)) + (@SMS_ScheduleToken04 * power (2, 01)) + (@SMS_ScheduleToken05 * power (2, 00))

declare @starthour int = (@SMS_ScheduleToken06 * power (2, 04)) + (@SMS_ScheduleToken07 * power (2, 03)) + (@SMS_ScheduleToken08 * power (2, 02)) + (@SMS_ScheduleToken09 * power (2, 01)) + (@SMS_ScheduleToken10 * power (2, 00))

declare @startday int = (@SMS_ScheduleToken11 * power (2, 04)) + (@SMS_ScheduleToken12 * power (2, 03)) + (@SMS_ScheduleToken13 * power (2, 02)) + (@SMS_ScheduleToken14 * power (2, 01)) + (@SMS_ScheduleToken15 * power (2, 00))

declare @startmonth int = (@SMS_ScheduleToken16 * power (2, 03)) + (@SMS_ScheduleToken17 * power (2, 02)) + (@SMS_ScheduleToken18 * power (2, 01)) + (@SMS_ScheduleToken19 * power (2, 00))

declare @startyear int = (@SMS_ScheduleToken20 * power (2, 05)) + (@SMS_ScheduleToken21 * power (2, 04)) + (@SMS_ScheduleToken22 * power (2, 03)) + (@SMS_ScheduleToken23 * power (2, 02)) + (@SMS_ScheduleToken24 * power (2, 01)) + (@SMS_ScheduleToken25 * power (2, 00)) + 1970

declare @minuteduration int = (@SMS_ScheduleToken26 * power (2, 05)) + (@SMS_ScheduleToken27 * power (2, 04)) + (@SMS_ScheduleToken28 * power (2, 03)) + (@SMS_ScheduleToken29 * power (2, 02)) + (@SMS_ScheduleToken30 * power (2, 01)) + (@SMS_ScheduleToken31 * power (2, 00))

declare @hourduration int = (@SMS_ScheduleToken32 * power (2, 04)) + (@SMS_ScheduleToken33 * power (2, 03)) + (@SMS_ScheduleToken34 * power (2, 02)) + (@SMS_ScheduleToken35 * power (2, 01)) + (@SMS_ScheduleToken36 * power (2, 00))

declare @dayduration int = (@SMS_ScheduleToken37 * power (2, 04)) + (@SMS_ScheduleToken38 * power (2, 03)) + (@SMS_ScheduleToken39 * power (2, 02)) + (@SMS_ScheduleToken40 * power (2, 01)) + (@SMS_ScheduleToken41 * power (2, 00))

declare @recurrencetype int = (@SMS_ScheduleToken42 * power (2, 02)) + (@SMS_ScheduleToken43 * power (2, 01)) + (@SMS_ScheduleToken44 * power (2, 00))

declare @isGMT int = (@SMS_ScheduleToken63 * power (2, 00))

declare @minutespan int = 1

declare @hourspan int = 1

declare @dayspan int = 1

declare @day int = 1

declare @fornumberofweeks int = 1

declare @fornumberofmonths int = 1

declare @weekorder int = 1

declare @monthday int = 1

if @recurrencetype = 2 — 010 SMS_ST_RecurInterval

begin

set @minutespan = (@SMS_ScheduleToken45 * power (2, 05)) + (@SMS_ScheduleToken46 * power (2, 04)) + (@SMS_ScheduleToken47 * power (2, 03)) + (@SMS_ScheduleToken48 * power (2, 02)) + (@SMS_ScheduleToken49 * power (2, 01)) + (@SMS_ScheduleToken50 * power (2, 00))

set @hourspan = (@SMS_ScheduleToken51 * power (2, 04)) + (@SMS_ScheduleToken52 * power (2, 03)) + (@SMS_ScheduleToken53 * power (2, 02)) + (@SMS_ScheduleToken54 * power (2, 01)) + (@SMS_ScheduleToken55 * power (2, 00))

set @dayspan = (@SMS_ScheduleToken56 * power (2, 04)) + (@SMS_ScheduleToken57 * power (2, 03)) + (@SMS_ScheduleToken58 * power (2, 02)) + (@SMS_ScheduleToken59 * power (2, 01)) + (@SMS_ScheduleToken60 * power (2, 00))

end

if @recurrencetype = 3 — 011 SMS_ST_RecurWeekly

begin

set @day = (@SMS_ScheduleToken45 * power (2, 02)) + (@SMS_ScheduleToken46 * power (2, 01)) + (@SMS_ScheduleToken47 * power (2, 00))

set @fornumberofweeks = (@SMS_ScheduleToken48 * power (2, 02)) + (@SMS_ScheduleToken49 * power (2, 01)) + (@SMS_ScheduleToken50 * power (2, 00))

end

if @recurrencetype = 4 — 100 SMS_ST_RecurMonthlyByWeekday

begin

set @day = (@SMS_ScheduleToken45 * power (2, 02)) + (@SMS_ScheduleToken46 * power (2, 01)) + (@SMS_ScheduleToken47 * power (2, 00))

set @fornumberofmonths = (@SMS_ScheduleToken48 * power (2, 03)) + (@SMS_ScheduleToken49 * power (2, 02)) + (@SMS_ScheduleToken50 * power (2, 01)) + (@SMS_ScheduleToken60 * power (2, 00))

set @weekorder = (@SMS_ScheduleToken51 * power (2, 02)) + (@SMS_ScheduleToken52 * power (2, 01)) + (@SMS_ScheduleToken52 * power (2, 00))

end

if @recurrencetype = 5 — 101 SMS_ST_RecurMonthlyByDate

begin

set @monthday = (@SMS_ScheduleToken45 * power (2, 04)) + (@SMS_ScheduleToken46 * power (2, 03)) + (@SMS_ScheduleToken47 * power (2, 02)) + (@SMS_ScheduleToken48 * power (2, 01)) + (@SMS_ScheduleToken49 * power (2, 00))

set @fornumberofmonths = (@SMS_ScheduleToken50 * power (2, 03)) + (@SMS_ScheduleToken51 * power (2, 02)) + (@SMS_ScheduleToken52 * power (2, 01)) + (@SMS_ScheduleToken63 * power (2, 00))

end

set @ReturnString =

case @recurrencetype

when 1 then ‘SMS_ST_NonRecurring’

when 2 then ‘SMS_ST_RecurInterval’

when 3 then ‘SMS_ST_RecurWeekly’

when 4 then ‘SMS_ST_RecurMonthlyByWeekday’

when 5 then ‘SMS_ST_RecurMonthlyByDate’

end +

Start Time : ‘ + convert (varchar, format (@startmonth, ’00’) + ‘/’ + format (@startday, ’00’) + ‘/’ + format (@startyear, ‘0000’) + ‘ ‘ + format (@starthour, ’00’) + ‘:’ + format (@startminute, ’00’))+

+ case when @minutespan = 1 then else ‘Minute span : ‘ + format (@minutespan, ’00’) +

end

+ case when @hourspan = 1 then else ‘Hour span : ‘ + format (@hourspan, ’00’) +

end

+ case when @dayspan = 1 then else ‘Day span : ‘ + format (@dayspan, ’00’) +

end

+ case when @day = 1 then

when @day = 1 then ‘Day : Sunday

when @day = 2 then ‘Day : Monday

when @day = 3 then ‘Day : Tueday

when @day = 4 then ‘Day : Wednesday

when @day = 5 then ‘Day : Thursday

when @day = 6 then ‘Day : Friday

when @day = 7 then ‘Day : Saturday

end

+ case when @fornumberofweeks = 1 then else ‘For number of weeks : ‘ + convert (char (1), @fornumberofweeks) +

end

+ case when @fornumberofmonths = 1 then else ‘For number of months: ‘ + convert (char (2), @fornumberofmonths) +

end

+ case when @weekorder = 1 then when @weekorder = 0 then ‘Week order : Last

else ‘Week order : ‘ + format (@weekorder, ’00’) +

end

+ case when @monthday = 1 then when @monthday = 0 then ‘Month day : Last

else ‘Month day : ‘ + format (@monthday, ’00’) +

end

+ case when @isGMT = 0 then ‘IsGMT : False

else ‘IsGMT : True

end

return (@ReturnString)

end

To use this function, I have a second function that takes the Schedule string and breaks it off in 16 character chunks, and then returns all the schedules in one varchar (max).

create function dbo.ParseScheduleTokenString (@InputString varchar (max))

returns varchar (max)

as

begin

declare @Counter int = 0

declare @TokenString varchar (16) = @InputString

declare @ReturnString varchar (max) =

while len (@TokenString) = 16

begin

set @ReturnString = @ReturnString + dbo.ParseScheduleToken (@TokenString)

set @Counter = @Counter + 1

set @TokenString = SUBSTRING (@InputString, 16 * @Counter + 1, 16)

end

return (@ReturnString)

end

Use this function in SQL to give results like:

select dbo.ParseScheduleTokenString (‘023E3A400008000002FE3A4000080000003F3A40000800008D3F3A4000080000’)

SMS_ST_NonRecurring

Start Time : 03/30/2011 17:00

IsGMT : False

SMS_ST_NonRecurring

Start Time : 03/30/2011 23:00

IsGMT : False

SMS_ST_NonRecurring

Start Time : 03/31/2011 01:00

IsGMT : False

SMS_ST_NonRecurring

Start Time : 03/31/2011 09:35

IsGMT : False

 

Deconstructing the SMS_ScheduleToken Server WMI Class

The SMS_ScheduleToken is an 8-byte field with data like “02C86BC000080000”. I found that to parse this requires looking at each byte bit-by-bit.

For example, 02C86BC000080000 in bytes is 001011001000011010111100000000000000000010000000000000000000 in bits.

Using the powershell Convert-CMSchedule command, we get:

PS XYZ:\> Convert-CMSchedule -ScheduleString 02C86BC000080000

SmsProviderObjectPath : SMS_ST_NonRecurring
DayDuration           : 0
HourDuration         : 0
IsGMT                 : False
MinuteDuration       : 0
StartTime             : 6/8/2017 10:00:00 PM

Fields common to all recurrence types

start minute   0 – 59 (000000 – 111011)
xxxxxx0000000000000000000000000000000000000000000000000000000000
start hour     0 – 23 (00000 – 10111)
000000xxxxx00000000000000000000000000000000000000000000000000000
start day       1 – 31 (00001 – 11111)
00000000000xxxxx000000000000000000000000000000000000000000000000
start month     1 – 12 (0001 – 1100)
0000000000000000xxxx00000000000000000000000000000000000000000000
years from 1970 0 – 63 (000000 – 111111) (ends with 2033)
00000000000000000000xxxxxx00000000000000000000000000000000000000
minute duration 0 – 59 (000000 – 111011)
00000000000000000000000000xxxxxx00000000000000000000000000000000
hour duration   0 – 23 (00000 – 10111)
00000000000000000000000000000000xxxxx000000000000000000000000000
day duration   0 – 31 (00000 – 11111)
0000000000000000000000000000000000000xxxxx0000000000000000000000
recurrence type 0 – 7 (see below)
000000000000000000000000000000000000000000xxx0000000000000000000
is GMT         0 – 1 (0 = False, 1 = True)
000000000000000000000000000000000000000000000000000000000000000x

recurrence type

001 SMS_ST_NonRecurring
unused
000000000000000000000000000000000000000000000xxxxxxxxxxxxxxxxxx0

010 SMS_ST_RecurInterval
minute span     0 – 59 (000000 – 111011)
000000000000000000000000000000000000000000000xxxxxx0000000000000
hour span       0 – 23 (00000 – 10111)
000000000000000000000000000000000000000000000000000xxxxx00000000
day span       0 – 31 (00000 – 11111)
00000000000000000000000000000000000000000000000000000000xxxxx000
unused
0000000000000000000000000000000000000000000000000000000000000xx0

011 SMS_ST_RecurWeekly
day                 1 – 7 (001 – 111)
000000000000000000000000000000000000000000000xxx0000000000000000
for number of weeks 1 – 4 (001 – 100)
000000000000000000000000000000000000000000000000xxx0000000000000
unused
000000000000000000000000000000000000000000000000000xxxxxxxxxxxx0

100 SMS_ST_RecurMonthlyByWeekday
day                 1 –   7 (001 – 111)
000000000000000000000000000000000000000000000xxx0000000000000000
for number of months 1 – 12 (0001 – 1100)
000000000000000000000000000000000000000000000000xxxx000000000000
week order           0 –   4 (000 – 100) (0 = Last)
0000000000000000000000000000000000000000000000000000xxx000000000
unused
0000000000000000000000000000000000000000000000000000000xxxxxxxx0

101 SMS_ST_RecurMonthlyByDate
month day           0 – 31 (00001 – 11111) (0 = Last)
000000000000000000000000000000000000000000000xxxxx00000000000000
for number of months 1 – 12 (0001 – 1100)
00000000000000000000000000000000000000000000000000xxxx0000000000
unused
000000000000000000000000000000000000000000000000000000xxxxxxxxx0

000 invalid schedule type
110 invalid schedule type
111 invalid schedule type

SMS_ScheduleToken Server WMI Class
https://msdn.microsoft.com/en-us/library/cc145924.aspx
SMS_ST_NonRecurring Server WMI Class
https://msdn.microsoft.com/en-us/library/cc143487.aspx
SMS_ST_RecurInterval Server WMI Class
https://msdn.microsoft.com/en-us/library/cc146489.aspx
SMS_ST_RecurWeekly Server WMI Class
https://msdn.microsoft.com/en-us/library/cc146527.aspx
SMS_ST_RecurMonthlyByWeekday Server WMI Class
https://msdn.microsoft.com/en-us/library/cc144566.aspx
SMS_ST_RecurMonthlyByDate Server WMI Class
https://msdn.microsoft.com/en-us/library/hh442787.aspx