NEW Importing Software Update Group Compliance SQL Query into PowerBI with Pie Chart

[zcaa0g]

Greetings,

When I import the following SQL query into Power BI and tick the "Compliance" tickbox for this SQL query in the "Fields" section at the very far right of the Power BI application along with the "Client-" tickbo in the v_R_System field using a pie chart, instead of the pie chart showing the accurate results for the compliance status of 0, 1, 2, or 3, the pie chart is divided into quarters i.e. 25% for each compliance status instead of the accurate number. Anyone know what I happen to be missing?

The short story is I just want to be able to import the compliance status of a software update group that I specify in the code into Power BI and have a pie chart show the percentage compliant versus non-compliant. I am going to have a dedicated software update group per pie chart, so I DO NOT want to have a drop-down list of the software update groups that dynamically updates the pie-chart based on the software update group. I just want a simple one where I put the software update group into the code as I have below. Thanks for your time.

The software update group name and collections are specified near the bottom of the code as shown in this example:
where li.title IN ('Windows 10 Servicing Stack 2021-10-02 09:17:11') and coll.collectionID in ('SP1000C2','SP1000C3','SP1000C4','SP1000C5')

select sys.name0 [Computer Name],sys.User_Name0 [User Name], os.caption0 [OS],
CONVERT(VARCHAR(26), ws.lasthwscan, 100) as [LastHWScan],
CONVERT(VARCHAR(26), uss.lastscantime, 100) AS 'LastSUScanTime',
CONVERT(VARCHAR(26), sys.last_logon_timestamp0, 100) AS 'Last Logon Time',
CONVERT(VARCHAR(26), ucs.status, 100) AS 'Compliance',
case when sys.client0='1' then 'Yes' else 'No'
end as 'Client (Yes/No)', c.IPAddress AS [IP Address]
From v_Update_ComplianceStatusAll UCS
left join v_r_system sys on ucs.resourceid=sys.resourceid
left join v_FullCollectionMembership fcm on sys.resourceid=fcm.resourceid
left join v_collection coll on coll.collectionid=fcm.collectionid
left join v_GS_OPERATING_SYSTEM os on ucs.resourceid=os.resourceid
left join v_gs_workstation_status ws on ucs.resourceid=ws.resourceid
left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
left join v_AuthListInfo LI on ucs.ci_id=li.ci_id
INNER JOIN (SELECT IP1.resourceid AS rsid2, IPAddress = substring
((SELECT (IP_Addresses0 + ', ')
FROM v_RA_System_IPAddresses IP2
WHERE IP2.IP_Addresses0 NOT LIKE '169%' AND IP2.IP_Addresses0 NOT LIKE '0.%' AND IP2.IP_Addresses0 NOT LIKE '%::%' AND
IP_Addresses0 NOT LIKE '192.%' AND IP1.resourceid = IP2.resourceid
ORDER BY resourceid FOR xml path('')), 1, 50000)
FROM v_RA_System_IPAddresses IP1
GROUP BY resourceid) c ON c.rsid2 = ucs.resourceid
where li.title IN ('Windows 10 Servicing Stack 2021-10-02 09:17:11') and coll.collectionID in ('SP1000C2','SP1000C3','SP1000C4','SP1000C5')
group by sys.name0,sys.User_Name0,os.Caption0,ws.LastHWScan ,uss.LastScanTime,sys.Last_Logon_Timestamp0,ucs.status,sys.client0,c.IPAddress
order by 1

 

[Garth]

Based solely on your description the problem is going to be based on HOW you build your report. Without your report there is no way for anyone to answer this.

 

[zcaa0g]

Thanks for the response Garth. First, I fully acknowledge I do not know what I am doing, but I am trying to learn through trial and error. These are the exact steps I am going through with Power BI.
________________________________________________________________
Open Power BI

Click on SQL Server, which brings up the SQL Server database dialog box in Power BI

Specify the SCCM server name and DB

Paste the aforementioned SQL query into the "SQL statement" input field

Then click on OK

Processes and then brings up the Load, Transform Data, or Cancel dialog box.

This shows the Computer Name, User Name, OS, LastHWScan, LastUScanTime, Last Logon Time, Compliance (the numerical value), Client, and IP address columns and all the device information is accurate underneath those columns

Then click on Transform Data

Then the Power Query Editor is displayed and I leave the information as-is and then click on Close & Apply

Add pie chart

Expand the query from the "Fields" pane where I have the following options:

Client (Yes/No)
Compliance
Computer Name
IP Address
Last Logon Time
LastHWScan
LastSUScanTime
OS
User Name

Click on Compliance and it will show the legend that I temporarily have named "Compliance" and it shows the compliance values of 0, 1, 2, 3 and nothing else as expected up to this point

Click on SQL Server, which brings up the SQL Server database dialog box in Power BI

Specify the SCCM server name and DB

Then click on OK and it brings up the Navigator that shows all of the tables for the SCCM database

Select v_R_System and then click on Transform Data which brings up the Power Query Editor

Click on Close & Apply

Now v_R_System is also displayed in the Fields ane

Expand out the v_R_System field and select the Client0 tickbox

Now the pie chart appears, but it is showing the total device count of 531 devices, however, the pie chart is broken into 4 quarters and is showing 531 devices in each quarter
instead of showing the proper percentage based on the compliance result.