I'm trying to get Software Update Compliance information from SCCM. I've never used Calculated Fields but I've been playing with them for 6 hours and struggling hard trying to convert a microsoft sql query to work with them.
Maybe I should be using calculated fields, but perhaps these are parameters I'm not sure yet.
#1 is my primary question I'm trying to answer: How to get a list of top 10 missing software updates per sccm device collection?
As you see in #4 they show a Pie Chart with a compliance wheel. Is that possible in Tableau? I tried google searching but can't hit on anything.
The ultimate goal is to build a single pane of glass to show company wide software update compliance but I'm struggling getting the ball rolling.
Below is a copy / paste of the code that works in SQL Server Management Studio for SQL 2014.
It's referencing an existing SCCM Collection and a Existing Software Update Group.
If I could put this in a compliance wheel like #4 I might not be able to contain the excitement inside my body.
thanks for any help on getting this going.
Declare @CollID nvarchar (255),@SUG nvarchar(255);
Set @CollID='TAB00211';set @SUG='ADR - Monthly - Windows';
--CollID=Collection ID and SUG=Software update group Name
Select CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
ui.Title, ui.ArticleID, ui.BulletinID, ui.DateRevised,
case when ui.IsDeployed='1' then 'Yes' else 'No' end as 'Deployed',
SUM (CASE WHEN ucs.status=3 or ucs.status=1 then 1 ELSE 0 END ) as 'Installed/Not Required',
sum( case When ucs.status=2 Then 1 ELSE 0 END ) as 'Required'
From v_UpdateInfo ui
JOIN v_Update_ComplianceStatus ucs on ucs.CI_ID = ui.CI_ID --AND ui.IsExpired = 0 AND ui.IsSuperseded = 0
--If you want display the expired and superdeded patches, remove the -- line in the above query
JOIN v_BundledConfigurationItems bci on ui.CI_ID = bci.BundledCI_ID
JOIN v_FullCollectionMembership fcm on ucs.ResourceID = fcm.ResourceID
join v_R_System sys on sys.ResourceID=ucs.ResourceID
where bci.CI_ID = (SELECT CI_ID FROM v_AuthListInfo where title=@SUG)
and fcm.CollectionID =@CollID
group by CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2),
ui.Title, ui.ArticleID, ui.BulletinID, ui.DateRevised, ui.IsDeployed
order by sum( case When ucs.status=2 Then 1 ELSE 0 END ) desc