SCCM 2012 SQL Query: Report on Content Distribution Duration

Sep 29, 2015 | | Say something

I was recently asked to check how long it takes for a package to successfully reach all of our sites. The thumb suck answer  was 2 to 3 days for a 2GB package, based on previous distribution reports, however the client required a report to show the actual time from the start of the content distribution to the end of the content distribution.

So the query below is based on the status messages that were received from the SMS_Distribution_Manager  component. To get started I used the “Status Message Queries” in the SCCM console, to view “All Status Messages”.

allstatusmessages

Once all the status messages were displayed for a particular time frame, I filtered the status messages to only display status messages for 1 distribution point. I then looked for the messages that stated “Distribution Manager instructed Package Transfer manager to send package…” and noted the Message ID, 2357 as the start of the distribution and did the same to find the status message “Distribution Manager successfully distributed package”, 2330, as an indication that the package has successfully been distributed.

 

All the rest was done in SQL query below:

–SQL Query Start—

–Declare Variable for Package ID

Declare @PackageID varchar(10)

SET @PackageID = packageid –>change PackageID<–

–Drop the temp table–

drop table #TempDST

–Insert data into the Temp table–

Select *

Into #TempDST

From

(select substring(att1.AttributeValue,13,36) as [Distribution Point], min(stat.Time) as [Distribution Start Time], ins.InsStrValue as PackageID, stat.MessageID,

case

when stat.MessageID = ‘2357’ then ‘Package Distribution Started’

else ‘Package Distribution has not started’

end as [Task Description],

case

when ins.InsStrValue = @PackageID then ‘Require_Updates_2012’

else ‘Package Distribution has not started’

end as [Package Name]

from v_StatusMessage as stat

left join v_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID

left join v_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID

where stat.Time > ‘2015-01-01 00:00:00’ and

Component = ‘SMS_Distribution_Manager’ and

stat.MessageID = ‘2357’ and

att1.AttributeID = ‘404’ and

ins.InsStrValue = @PackageID

Group By att1.AttributeValue,stat.MessageID,ins.InsStrValue,att1.AttributeID) as DST

–Drop the Temp Table–

drop table #TempDET

–Insert data into the Temp table–

Select *

Into #TempDET

From

(select substring(att1.AttributeValue,13,36) as [Distribution Point], max(stat.Time) as [Distribution End Time], ins.InsStrValue as PackageID, stat.MessageID,

case

when stat.MessageID = ‘2330’ then ‘Package Distribution Completed’

else ‘Package Distribution InProgress’

end as [Task Description]

from v_StatusMessage as stat

left join v_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID

left join v_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID

where stat.Time > ‘2015-01-01 00:00:00’ and

Component = ‘SMS_Distribution_Manager’ and

stat.MessageID = ‘2330’ and

att1.AttributeID = ‘404’ and

ins.InsStrValue = @PackageID

Group By att1.AttributeValue,stat.MessageID,ins.InsStrValue,att1.AttributeID) as DET

–Select the required data from the Temp Tables—

Select a.[Distribution Point], a.[Package Name], a.packageid, (select convert(varchar(50),a.[Distribution Start Time],120)) as [Distribution Start Time], (select convert(varchar(50),b.[Distribution End Time],120)) as [Distribution End Time], DateDiff(MI,a.[Distribution Start Time],b.[Distribution End Time]) as [Duration (Minutes)], (select convert(varchar(12),dateadd(minute, Datediff(minute,a.[Distribution Start Time],b.[Distribution End Time]),0),114)) as [Duration (Hours)]

from #tempdst a, #tempdet b

where a.[Distribution Point] = b.[Distribution Point]

–SQL Query End—

 

Results:

Extract from report (server names and packages have been omitted for security purposes):

asmresults

1,771 total views, 1 views today

Posted in: Configuration Manger | Tags: , , , , , , , ,

Leave a Reply