Here’s some simple PowerBi dashboard to give quick insight into Imaging and Distribution point usage in your environment.
A Few months back I came across this awesome SCCM Dashboard on Technet by Raf. If you haven’t seen it you need to get it! He makes it really easy to get quick insights into your SCCM environment and Client health. My dashboards or kind of an extension to Rafs. After playing with it I found there were a few dashboards that needed to added for Client imaging and Distribution point reporting! I wont go into detail about how to edit the dashboard but Raf’s post lays it all out very clearly.
Client Imaging dashboard
You’ll need to edit a few things in this code specific to your environment. first is the Deployment ID for each of your Task sequence deployments. This is easy to find int he Console. I also have some custom inventory views there for where we write our OEM info into the registry with our Image version. You can remove this view or point it to where you inventory your image version info. Also because we use the country ISO code in the first part of the computer name, i am able to use that to create the Geo location map. If you don’t have somethign in the name to identify the location of the computer I’ve seen some put Location information on their AD sites or create custom WMI values to specify this information. As with all things SCCM, every environment is different. Let me know what works best for you!
select vrs.ResourceID, Substring(vrs.Name0, 1,3) 'countrycode', sc.SMS_Assigned_Sites0 'Region', bio.Manufacturer0 'Manufacturer', com.Model0 'Model', Substring(cda.packagename, 9,60) 'Image Name', cda.summarizationtime ,
'Status' = CASE StatusType
When '2' Then 'In Progress'
When '5' Then 'Failed'
'ChassisType' = CASE
When ChassisTypes0 in ('3','4','5','6','7','13','16') Then 'Desktop'
When ChassisTypes0 in ('8','9','10','11','14','15','1') Then 'Laptop'
noem.model0 'Image Version'
from v_R_System vrs
left join v_GS_PC_BIOS bio on vrs.ResourceID=bio.ResourceID
left join v_GS_COMPUTER_SYSTEM com on vrs.ResourceID=com.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE vSE on vrs.ResourceID=vSE.ResourceID
left Join v_RA_System_SmsAssignedSites SC on vrs.ResourceID = sc.ResourceID
Left join v_ClassicDeploymentAssetDetails CDA on vrs.ResourceID = cda.deviceID
left join v_GS_ITInfo_OEMInfo0 oem on vrs.resourceID=oem.resourceid
WHERE DeploymentID in ('PRI2017C','PRI201D8','PRI20103','PRI2025F') AND StatusType in (1,2,5)
I like to keep tabs on what hardware is getting imaged the most in the environment which helps me know what models to prioritize when updating driver packs and software packages. The data for this dashboard is collected by the same query as the Client imaging. Its just filtering for the make, model and chassis info.
DP Usage Summary
Another set of data I find to be very useful in my environment is DP usage. I only place distribution points where there is a lot of Imaging going on or there a large amount of Clients but not a lot of bandwidth. This dashboard helps me keep an eye on whats being used the most or not enough which helps me ask the question.. do we still need it there? The nice thing is all the data is already being collected by the DP’s and its easy to see how much Bytes are sent by each DP, how many clients are making requests, etch.
You shouldn’t have to modify too much here. The only thing might be the Sub-string Selection to tie the PKGServer name to the V_R_Systems name. This is how i pull in more information about the DP like Model information and OS.
SELECT Substring(PkgServer, 1, (len(PkgServer) - 8) ) 'Server Name',Substring(vrs.AD_Site_Name0, 1,3) 'countrycode',BytesSent'MB sent', BytesSent 'GB sent', NoUniqueClient, NoRequestProcessed , UsageStartTime, UsageEndTime,
CAse isPxe When 0 Then 'NO'
End as 'IsPXE',
CASE ops.Caption0 when 'Microsoft Windows Server 2012 R2 Standard' then '2012 R2'
when 'Microsoft Windows Server 2008 R2 Standard' Then '2008 R2 Stnd'
When 'Microsoft Windows Server 2012 Standard' Then '2012 Stnd'
When 'Microsoft Windows Server 2012 R2 Datacenter' Then '2012 R2 Datac'
when 'Microsoft Windows Server 2008 R2 Enterprise' Then '2008R2 Ent'
end as 'OSVersion'from v_DPUsageSummary DPU
left join v_GS_COMPUTER_SYSTEM com on Substring(PkgServer, 1, (len(PkgServer) - 8) )=com.Name0
left join v_DistributionPointInfo dpi on dpu.pkgserver=dpi.servername
left join v_r_system vrs on com.ResourceID=vrs.ResourceID
left join v_GS_OPERATING_SYSTEM ops on com.resourceID=ops.resourceID
Thats it for now. Let em comment if you have any questions. Im NOT a sql or PowerBI guru but i hope this helps you