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.
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 (
FROM v_GS_Operating_System as GS_OS_Data
INNER JOIN (SELECT ResourceID
, MAX(TimeStamp) as MaxDate
GROUP BY ResourceID
) MaxFilter on GS_OS_Data.ResourceID = MaxFilter.ResourceID and MaxFilter.MaxDate = GS_OS_Data.TimeStamp
, 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'
END AS ClientStatus
, CASE NewestOS.Caption0
WHEN NULL THEN v_R_System.Operating_System_Name_and0
END AS OSNAME
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%'
Collect patch compliance data. Technically the 0 management is not required here, as 0 should never appear in
We also filter out some information to ensure we are avoiding accidentally capturing software update groups.
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'
END AS 'PatchStatus'
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'
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.
WHERE v_UpdateInfo.CIType_ID = '9'
SELECT '0', '--- Not Limited by Sug ---'
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
WHERE v_UpdateInfo.CIType_ID = '9'
LEFT OUTER JOIN v_CIRelation ON SUGInfo.CI_ID = v_CIRelation.FromCIID
select '0' AS [FromCIID], CI_ID FROM v_UpdateInfo
where CIType_ID != 9
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.
, v_Collection.Name AS 'CollectionName'
LEFT OUTER JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionId
WHERE v_FullCollectionMembership.ResourceType <> '4' AND v_Collection.Name LIKE 'CollectionFilter'
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.
, CASE v_UpdateInfo.IsDeployed
WHEN 1 THEN 'TRUE'
END AS DeploymentState
WHERE v_UpdateInfo.ArticleID IS NOT NULL AND v_UpdateInfo.ArticleID !=''
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
from fn_ListUpdateCategoryInstances(1033) AS SMS_UpdateCategoryInstance
where (SMS_UpdateCategoryInstance.CategoryTypeName = N'Company' AND SMS_UpdateCategoryInstance.AllowSubscription = 1)
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