Patch My PC Power BI Queries

When running a Power BI report, you are prompted to trust the SQL native queries. Here we provide the queries, and an explanation on their usage.

SQL Queries

Client Info

Collection of information about devices including what version of the operating system they are running, and FQDN. Originally we used to gather this from a different view, however this caused numerous reports of duplicate values for large environments due to maintenance tasks not running quickly enough. Instead we dynamically build the content. We also have to handle duplicates caused by OS caption.

WITH NewestOS AS (
SELECT GS_OS_Data.ResourceID
	, TimeStamp
	, Caption0
FROM v_GS_Operating_System as GS_OS_Data
INNER JOIN (SELECT ResourceID
	, MAX(TimeStamp) as MaxDate
	FROM v_GS_Operating_System
	GROUP BY ResourceID
) MaxFilter on  GS_OS_Data.ResourceID = MaxFilter.ResourceID and MaxFilter.MaxDate = GS_OS_Data.TimeStamp
)
SELECT v_R_System.ResourceID 
  , v_R_System.Name0    
    , v_R_System.Name0 + '.' + v_R_System.Full_Domain_Name0 as Resource_Names0
    , v_R_System.Resource_Domain_OR_Workgr0 AS 'DOMAIN'
    , CASE v_R_System.Client0
            WHEN 1 THEN 'INSTALLED'
            ELSE 'MISSING'
        END AS ClientStatus
    , v_R_System.Client_Version0
    , v_R_System.Last_Logon_Timestamp0
    , CASE NewestOS.Caption0
        WHEN NULL THEN v_R_System.Operating_System_Name_and0
        ELSE NewestOS.Caption0
    END AS OSNAME
    , V_GS_System.SystemRole0
FROM v_R_System
LEFT OUTER JOIN NewestOS on v_R_System.ResourceID = NewestOS.ResourceID
LEFT OUTER JOIN v_GS_System ON v_R_System.ResourceID = v_GS_System.ResourceID
WHERE v_R_System.Operating_System_Name_and0 LIKE '%Microsoft%'

Compliance Info

Collect patch compliance data. Technically the 0 management is not required here, as 0 should never appear in v_Update_ComplianceStatus

We also filter out some information to ensure we are avoiding accidentally capturing software update groups.

SELECT CASE
			WHEN v_Update_ComplianceStatus.Status = '0' THEN 'UNKNOWN'
			WHEN v_UPDATE_ComplianceStatus.Status = '1' THEN 'NOT REQUIRED'
			WHEN v_Update_ComplianceStatus.Status = '2' THEN 'NON COMPLIANT'
			WHEN v_Update_ComplianceStatus.Status = '3' THEN 'COMPLIANT'
			ELSE 'NA'
		END AS 'PatchStatus'
    , v_Update_ComplianceStatus.CI_ID
    , v_UpdateInfo.CIType_ID
    , v_Update_ComplianceStatus.resourceID
	, v_Update_complianceStatus.LastStatusCheckTime
FROM v_Update_ComplianceStatus
LEFT OUTER JOIN v_UpdateInfo on v_Update_ComplianceStatus.CI_ID = v_UpdateInfo.CI_ID
WHERE v_UpdateInfo.CIType_ID = '8' or v_UpdateInfo.CIType_ID = '1'

SUG Info

This query gathers the names of each software update group, and creates a dummy row, for updates that are not in any software update group. This is done to handle a use case with Power BI.

SELECT v_UpdateInfo.CI_ID
, v_UpdateInfo.Title
FROM v_UpdateInfo
WHERE v_UpdateInfo.CIType_ID = '9'
UNION ALL
SELECT '0', '--- Not Limited by Sug ---'

SUG Relation Map

This builds what we call the relation map, and tracks what updates are a member of what group. This is how we then are able to filter our report by software update groups. This data is used as a connection point in the PowerBI data model to join data together.

WITH SUGInfo AS
(
SELECT v_UpdateInfo.CI_ID
FROM v_UpdateInfo
WHERE v_UpdateInfo.CIType_ID = '9'
)
SELECT v_CIRelation.FromCIID
, v_CIRelation.ToCIID
FROM SUGInfo
LEFT OUTER JOIN v_CIRelation ON SUGInfo.CI_ID = v_CIRelation.FromCIID
UNION ALL
select '0' AS [FromCIID], CI_ID FROM v_UpdateInfo
where CIType_ID != 9

Collection Membership

Collections, often represent logical groupings of machines we really care about. As a result, we use this query to build out what machines are a member of what collection so we can allow filtering based on the collection names.

SELECT v_FullCollectionMembership.ResourceID
    , v_FullCollectionMembership.Name
    , v_FullCollectionMembership.ResourceType
    , v_Collection.CollectionID
    , v_Collection.Name AS 'CollectionName'
FROM v_FullCollectionMembership
LEFT OUTER JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionId
WHERE v_FullCollectionMembership.ResourceType <> '4' AND v_Collection.Name LIKE 'CollectionFilter'

Update Info

While knowing a CI_ID of an update is nice, you typically want more detailed information. This, collects data for things like the URL for information about the update, the date it was released, and more.

SELECT v_UpdateInfo.CI_ID
	, v_UpdateInfo.CI_UniqueID
    , v_UpdateInfo.ArticleID
	, v_UpdateInfo.Title
    , v_UpdateInfo.InfoURL
	, v_UpdateInfo.DatePosted
	, v_UpdateInfo.DateCreated
	, CASE v_UpdateInfo.IsDeployed
			WHEN 1 THEN 'TRUE'
			ELSE 'FALSE'
		END AS DeploymentState
FROM v_UpdateInfo
WHERE v_UpdateInfo.ArticleID IS NOT NULL AND v_UpdateInfo.ArticleID !=''

Category Info

Some times it really helps to be able to filter a report by the patching vendors. This section gathers who the original author of the patch was.

WITH CATS as(
select  all SMS_UpdateCategoryInstance.LocaleID
    ,SMS_UpdateCategoryInstance.CategoryInstanceName
    ,SMS_UpdateCategoryInstance.CategoryInstance_UniqueID 
	,SMS_UpdateCategoryInstance.CategoryInstanceID
from fn_ListUpdateCategoryInstances(1033) AS SMS_UpdateCategoryInstance  
where (SMS_UpdateCategoryInstance.CategoryTypeName = N'Company' AND SMS_UpdateCategoryInstance.AllowSubscription = 1)
)

Select CATS.CategoryInstanceName
	, v_updateINfo.CI_ID
from fn_ListCICategoriesAll(1033) as CI_LIST
INNER Join CATS on CI_LIST.CategoryInstanceID = cats.CategoryInstanceID
INNER JOIN v_updateinfo on v_updateinfo.ci_id = ci_list.ci_id