2 Replies Latest reply on Nov 2, 2017 10:16 AM by Joe Sarausad

    New to using Calculated Fields.

    Michael Staton

      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.

       

      I got the idea from here:  SCCM Configmgr SQL query to find Top X missing updates for specific collection for specific update group | Eswar Koneti'…

       

      #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.

       

      michael

       

      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

        • 1. Re: New to using Calculated Fields.

          Hey Michael,

           

          Have you found a solution to this issue? If so, please share! If you're still hoping for help, I would contact support.

           

          Thanks,

           

          -Diego

          • 2. Re: New to using Calculated Fields.
            Joe Sarausad

            Hi Michael,

             

            I am new to Tableau software but have experience querying SCCM DB.

             

            You might find this query useful in identifying systems that are out of compliance when you deploy your monthly windows security updates. The great thing about this query is that you do not have to worry about inputting the collection ID since that is already associated with the Assignment ID when you create the deployment:

             

            --Select * from v_CIAssignment order by AssignmentID --use this to grab proper collection ID and replace below

             

            SELECT v_R_System.Name0 as [Server],

            REPLACE

            (REPLACE

            (REPLACE

              (REPLACE

               (REPLACE

                (REPLACE

                 (REPLACE

                  (REPLACE

                   (REPLACE

                    (REPLACE(v_AssignmentState_Combined.StateID,'0','enf state unknown')

                    ,'1','Compliant')

                    ,'2','Non compliant')

                    ,'4','Succesfully installed updates')

                    ,'5','Pending restart')

                    ,'6','Failed to install updates')

                    ,'7','Downloading updates')

                    ,'8','Downloaded updates')

                    ,'9','Failed to download updates')

                    ,'10','Waiting for maintenance window before installing') as [State]

                    ,v_R_System.Full_Domain_Name0 as [Domain]

                    ,v_R_System.description0 as [Contact]

            from v_R_System,v_AssignmentState_Combined

            where v_R_System.ResourceID = v_AssignmentState_Combined.ResourceID and

            v_AssignmentState_Combined.AssignmentID = '16777689' --Change this number to appropriate assignment ID from running query at the top

            and v_AssignmentState_Combined.StateID <> '0'

            and v_AssignmentState_Combined.StateID <> '1'