If you're familiar with the ConfigMgr GUI you know you can get this information from the console. However, that's a lot of clicking. This method let's you get it direct from SQL instead.
## What are we doing?
Due to the speed organizations release patches, and their cumulative nature in base operating system patches, and third party updates, CVE's fixed in OLD updates, are also fixed in NEW updates. However, vendors commonly don't include all previously fixed CVE's. This can make deploying the right patches challenging. As admins we typically have to ensure compliance against the newest update, but how do we know when all we are given is a CVE, or a KB, from six months ago.
```mermaid
graph LR
A[Old Update]
B[New Update]
C[CVE Fixed]
A --> B
C --> A
C --> B
```
While you can usually determine this pretty quickly through a google search, you might sometimes have to step through multiple CVE ID's, or multiple KB's.
## Enter SQL
The Configuration Manager Database stores a large amount of data. One of the most commonly underused views in the database in my opinion is the V_CIRelation_All table. This table tracks ALL of the possible relationships and their meaning! One of these meanings includes the meaning type - 6 or superseded.
|Value|Relationship type|
|---|---|
|1|Bundled|
|2|Required|
|3|Prohibited|
|4|Optional|
|5|Derived|
|6|Superseded|
|7|Self|
|8|Reference|
|9|AppToDTReference|
|10|AppDependence|
|11|Intention|
|12|Platform|
|13|GlobalConditionReference|
|15|ApplicationSuperSeded|
|16|ApplicationType|
|17|ApplicationHost|
|18|ApplicationInstaller|
|19|SupersedOrDependent|
|20|VirtualEnvironmentReference|
|21|AppDCMReference|
|22|DeploymentTypeToPolicyTemplateReference|
|23|CIInheritanceRelation|
[SMS_CIRelation Class - Configuration Manager | Microsoft Learn](https://learn.microsoft.com/en-us/mem/configmgr/develop/reference/sum/sms_cirelation-server-wmi-class)
With this in mind we can query the relationship table to find all of the relationships, and their CI_ID's (unique ID's) - and then harvest information about both sides of the relationship.
### Query
```SQL
SELECT SourceKB.Title
, SourceKB.IsSuperseded
, SourceKB.InfoURL
, SourceKB.CI_ID
, SupersededList.Title [Supserseded By Title]
, SupersededList.ArticleID [Superseded By ArticleID]
, SupersededList.CI_ID [Superseded By CI_ID]
, SupersededList.IsSuperseded
, SupersededList.InfoURL [Supserseded By InfoURL]
, SupersededList.DatePosted [Date Published by Vendor]
, SupersededList.DateCreated [Date Created]
FROM v_CIRelation_all CA
LEFT JOIN v_UpdateInfo SourceKB on CA.ReferencedCI_ID = SourceKB.CI_ID
LEFT JOIN v_UpdateInfo SupersededList on ca.CI_ID = SupersededList.CI_ID
WHERE RelationType = 6 AND SourceKB.ArticleID = 'PMPC-2022-09-07'
```
>[!Warning]
>It's important to note, ArticleID's are not unique for Microsoft or other vendors. However, the supersedence chains of those article ID's may also not be unique (servers and workstations sometimes follow different chains upwards).
### Joining a View On itself?
In the query above, I'm doing something which might seem a bit weird. I'm joining a view back on itself, by creating an alias of the view's name. This allows me to re-use information from the View for different purposes and different ID's based on the Join.
#### SourceKB
The Source KB alias in this query is the KB ID of the update that HAS BEEN superseded (the older update) - the information displayed from this view will be about the older update.
#### SupersededList
The SupersededList alias is the "NEWER" update or updates. This will include all of the needed information about the newer update.
### Example Output
Below is an example of what this looks like.
![[SQL_QueryExample.png]]
## OK, but what's the point?
In the above example, if a critical CVE came out for Notepad++ 8.4.5 two months ago, and the security team could only tell us "patch for this CVE". If the CVE is only publicly known to be resolved in the older version of the update, correlating to the newest version might be challenging.
This is important for proving you are no longer vulnerable to a risk. Re-using our example.
If 8.4.5 initially fixed a vulnerability, and 8.4.6, 8.4.7 and 8.4.8 have all been released being "compliant" for any of those means you are protected from the vulnerability itself. So your reporting model goes from one to one, to many to one.