SQL Query for Getting Started with Power BI Desktop and SCCM

I was asked in a tweet to post my SQL query that I used within Getting Started with Power BI Desktop and SCCM. Since it didn’t make sense to post it to the Enhansoft Blog. I thought that I would post it on SCCMUG.ca site. It took a bit longer to get this out as I need to upgrade the site to the latest version of WP. 🙂

Select 
	Coll.Name, 
	RV.ResourceID as 'ResourceID',
	RV.Netbios_Name0 AS 'PC Name',
	isnull(SCUM.TopConsoleUser0, isnull(RV.User_Domain0 +'\'+RV.User_Name0,'n/a')) AS 'UserName',  
	WS.HWStartDateTime0 as 'Warranty Start Date', 
	WS.HWEndDateTime0 as 'Warranty End Date', 
	isnull(WS.Manufacture0,'n/a') AS 'Manufacturers', 
	isnull(WS.Model0,'n/a') AS 'Model', 
	isnull(WS.Serial_Number0,'n/a') AS 'Serial Number', 
	isnull(WS.Line0,'n/a') AS 'Line', 
	WS.CheckedDate0  as 'Date Checked' 
From 
	dbo.v_R_System_Valid RV
	left outer JOIN dbo.v_GS_Warranty3_Summary0 AS WS ON WS.ResourceID = RV.ResourceID 
	left outer join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on RV.ResourceID = SCUM.ResourceID 
	join dbo.v_FullCollectionMembership FCM on FCM.ResourceID = RV.ResourceID
	join dbo.v_Collection Coll on FCM.CollectionID = Coll.CollectionID

Leave a Reply

Your email address will not be published. Required fields are marked *