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.
1
WITH NewestOS AS (
2
SELECT GS_OS_Data.ResourceID
3
, TimeStamp
4
, Caption0
5
FROM v_GS_Operating_System as GS_OS_Data
6
INNER JOIN (SELECT ResourceID
7
, MAX(TimeStamp) as MaxDate
8
FROM v_GS_Operating_System
9
GROUP BY ResourceID
10
) MaxFilter on GS_OS_Data.ResourceID = MaxFilter.ResourceID and MaxFilter.MaxDate = GS_OS_Data.TimeStamp
11
)
12
SELECT v_R_System.ResourceID
13
, v_R_System.Name0
14
, v_R_System.Name0 + '.' + v_R_System.Full_Domain_Name0 as Resource_Names0
15
, v_R_System.Resource_Domain_OR_Workgr0 AS 'DOMAIN'
16
, CASE v_R_System.Client0
17
WHEN 1 THEN 'INSTALLED'
18
ELSE 'MISSING'
19
END AS ClientStatus
20
, v_R_System.Client_Version0
21
, v_R_System.Last_Logon_Timestamp0
22
, CASE NewestOS.Caption0
23
WHEN NULL THEN v_R_System.Operating_System_Name_and0
24
ELSE NewestOS.Caption0
25
END AS OSNAME
26
, V_GS_System.SystemRole0
27
FROM v_R_System
28
LEFT OUTER JOIN NewestOS on v_R_System.ResourceID = NewestOS.ResourceID
29
LEFT OUTER JOIN v_GS_System ON v_R_System.ResourceID = v_GS_System.ResourceID
30
WHERE v_R_System.Operating_System_Name_and0 LIKE '%Microsoft%'
Copied!

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.
1
SELECT CASE
2
WHEN v_Update_ComplianceStatus.Status = '0' THEN 'UNKNOWN'
3
WHEN v_UPDATE_ComplianceStatus.Status = '1' THEN 'NOT REQUIRED'
4
WHEN v_Update_ComplianceStatus.Status = '2' THEN 'NON COMPLIANT'
5
WHEN v_Update_ComplianceStatus.Status = '3' THEN 'COMPLIANT'
6
ELSE 'NA'
7
END AS 'PatchStatus'
8
, v_Update_ComplianceStatus.CI_ID
9
, v_UpdateInfo.CIType_ID
10
, v_Update_ComplianceStatus.resourceID
11
, v_Update_complianceStatus.LastStatusCheckTime
12
FROM v_Update_ComplianceStatus
13
LEFT OUTER JOIN v_UpdateInfo on v_Update_ComplianceStatus.CI_ID = v_UpdateInfo.CI_ID
14
WHERE v_UpdateInfo.CIType_ID = '8' or v_UpdateInfo.CIType_ID = '1'
Copied!

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.
1
SELECT v_UpdateInfo.CI_ID
2
, v_UpdateInfo.Title
3
FROM v_UpdateInfo
4
WHERE v_UpdateInfo.CIType_ID = '9'
5
UNION ALL
6
SELECT '0', '--- Not Limited by Sug ---'
Copied!

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.
1
WITH SUGInfo AS
2
(
3
SELECT v_UpdateInfo.CI_ID
4
FROM v_UpdateInfo
5
WHERE v_UpdateInfo.CIType_ID = '9'
6
)
7
SELECT v_CIRelation.FromCIID
8
, v_CIRelation.ToCIID
9
FROM SUGInfo
10
LEFT OUTER JOIN v_CIRelation ON SUGInfo.CI_ID = v_CIRelation.FromCIID
11
UNION ALL
12
select '0' AS [FromCIID], CI_ID FROM v_UpdateInfo
13
where CIType_ID != 9
Copied!

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.
1
SELECT v_FullCollectionMembership.ResourceID
2
, v_FullCollectionMembership.Name
3
, v_FullCollectionMembership.ResourceType
4
, v_Collection.CollectionID
5
, v_Collection.Name AS 'CollectionName'
6
FROM v_FullCollectionMembership
7
LEFT OUTER JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionId
8
WHERE v_FullCollectionMembership.ResourceType <> '4' AND v_Collection.Name LIKE 'CollectionFilter'
Copied!

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.
1
SELECT v_UpdateInfo.CI_ID
2
, v_UpdateInfo.CI_UniqueID
3
, v_UpdateInfo.ArticleID
4
, v_UpdateInfo.Title
5
, v_UpdateInfo.InfoURL
6
, v_UpdateInfo.DatePosted
7
, v_UpdateInfo.DateCreated
8
, CASE v_UpdateInfo.IsDeployed
9
WHEN 1 THEN 'TRUE'
10
ELSE 'FALSE'
11
END AS DeploymentState
12
FROM v_UpdateInfo
13
WHERE v_UpdateInfo.ArticleID IS NOT NULL AND v_UpdateInfo.ArticleID !=''
Copied!

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.
1
WITH CATS as(
2
select all SMS_UpdateCategoryInstance.LocaleID
3
,SMS_UpdateCategoryInstance.CategoryInstanceName
4
,SMS_UpdateCategoryInstance.CategoryInstance_UniqueID
5
,SMS_UpdateCategoryInstance.CategoryInstanceID
6
from fn_ListUpdateCategoryInstances(1033) AS SMS_UpdateCategoryInstance
7
where (SMS_UpdateCategoryInstance.CategoryTypeName = N'Company' AND SMS_UpdateCategoryInstance.AllowSubscription = 1)
8
)
9
​
10
Select CATS.CategoryInstanceName
11
, v_updateINfo.CI_ID
12
from fn_ListCICategoriesAll(1033) as CI_LIST
13
INNER Join CATS on CI_LIST.CategoryInstanceID = cats.CategoryInstanceID
14
INNER JOIN v_updateinfo on v_updateinfo.ci_id = ci_list.ci_id
Copied!
​
Last modified 4mo ago