Query for SCCM required updates

[sjanacek]

Hi all!

I'm building in Power Bi reports for our SCCM (System Center Configuration Manager).

It's already connected and I'm pulling all data from the SCCM SQL server.

Now I want to have a table which is showing me all the updates which are required and also a column with the number of the devices where the update is required.

I found this article: https://thesleepyadmins.com/2020/03/30/mecm-check-for-all-updates-that-are-required-but-not-deploy-s...

But there is only this SQL query:

select distinct
UI.DatePosted as ‘Release Date’,
UI.articleid as ‘ArticalID’,
UI.Title,
Update_Required=(case when UCS.Status=2 then ‘Yes’ end),
CS.NumMissing,
Updates_Deployed=(case when UI.IsDeployed=0 then ‘NotDeployed’ end),
UI.InfoURL as InformationURL

from V_UpdateComplianceStatus UCS
join v_UpdateInfo UI on UI.CI_ID=UCS.CI_ID
join v_Update_ComplianceSummary CS on CS.CI_ID=UCS.CI_ID
WHERE UCS.Status=2 and UI.IsDeployed=0
order by NumMissing desc

I was thinking about to create a new table and running this query to get the data I need. What is the easiest way to "translate" this SQL query into Power Bi (DAX). Or any other idea how I can get this results?

[amitchandak]

@sjanacek , you can bring these tables in power bi and join CI_ID

you can create a calculated column for this

if (V_UpdateComplianceStatus [Status]=2 , "Yes",blank())

[sjanacek · Accepted solution]

Hi,

I found a pbix-template for SCCM and got now another solution, where I'm using this at the beginning:

= SQL.database ("servername", "databasename", [query=..........])

With this I can work now 🙂

Thanks and have a greate new year!