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