Forum Discussion
Microsoft EmployeeValue in table 1 exist/does not exist in table 2 for each entity
I have 3 tables:
Servers
| Server_Name | Server_OS |
| |
Server_1 |
| Windows Server 2016 |
|
Server_2 |
| Windows Server 2019 |
|
Server_3 |
| Windows Server 2016 |
|
All Updates
| Server_Name | Patch_ID |
| |
Server_1 |
| KB123 |
|
Server_1 |
| KB456 |
|
Server_2 |
| KB123 |
|
Critical_Patch
OS |
| KB_ID |
Windows Server 2016 |
| KB456 |
Windows Server 2019 |
| KB789 |
I would like to have the conditional column like so
Servers
Server_Name |
| Server_OS |
| Critical_Patch_Installed |
Server_1 |
| Windows Server 2016 |
| Yes |
Server_2 |
| Windows Server 2019 |
| No |
Server_3 |
| Windows Server 2016 |
| No |
Essentially, For each server in SERVERS,
if list of all KB_IDs in ALL UPDATES for matching [Server_Name] contains mathing [KB_ID] in CRITICAL_PATCH for matching [OS]then [Critical_Patch_Installed] = "yes"
Really appreciate the help!
PowerBeeEye
Please check now:You can download the file: HERE
Critical_Patch_Installed = VAR _UPDATE = SELECTCOLUMNS( FILTER( GENERATE( 'CVE-2020-1350', Q_Domain_Controllers_Hotfix_Latest ), 'CVE-2020-1350'[KBID] = Q_Domain_Controllers_Hotfix_Latest[HotFixID] ), "_SERVER", Q_Domain_Controllers_Hotfix_Latest[ComputerDNS] ) RETURN IF( SELECTEDVALUE(Q_Domain_Controllers_Latest[dnshostname]) IN _UPDATE, "Yes", "No" )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
13 Replies
- Fowmy
Super User
PowerBeeEye
Please check this solution, I did not create any relationships between Tables.Critical_Patch_Installed = VAR _UPDATE = SELECTCOLUMNS( FILTER( GENERATE( 'All Updates', Critical_Patch ), 'All Updates'[Patch_ID] = Critical_Patch[KB_ID] ), "_SERVER", 'All Updates'[Server_Name] ) RETURN IF( SELECTEDVALUE(Servers[Server_OS]) IN _UPDATE, "Yes", "No" )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
- PowerBeeEye
Sorry, but I don't think that worked. Perhaps I made a mitake with the column names, but here are the tables once again:
CVE-2020-1350
KBID OS KB4565541 Windows Server 2012 R2 KB3000483 Windows Server 2016 KB4558998 Windows Server 2019 Q_Domain_Controllers_Latest
operatingsystem dnshostname Windows Server 2016 Server1.domain.ext Windows Server 2019 Server2.domain.ext Windows Server 2012 R2 Server3.domain.ext Q_Domain_Controllers_Hotfix_Latest
HotFixID ComputerDNS KB3000483 Server1.domain.ext KB3003057 Server1.domain.ext KB3011780 Server1.domain.ext KB3019978 Server2.domain.ext KB3023266 Server2.domain.ext KB3035126 Server2.domain.ext KB3045685 Server3.domain.ext KB3045999 Server3.domain.ext KB4565541 Server3.domain.ext What I'm looking for:
Q_Domain_Controllers_Latest
operatingsystem dnshostname CC_DNSVulnerabilityPatchInstalledWindows Server 2016 Server1.domain.ext Yes Windows Server 2019 Server2.domain.ext No Windows Server 2012 R2 Server3.domain.ext Yes - Fowmy
PowerBeeEye
Please check now:You can download the file: HERE
Critical_Patch_Installed = VAR _UPDATE = SELECTCOLUMNS( FILTER( GENERATE( 'CVE-2020-1350', Q_Domain_Controllers_Hotfix_Latest ), 'CVE-2020-1350'[KBID] = Q_Domain_Controllers_Hotfix_Latest[HotFixID] ), "_SERVER", Q_Domain_Controllers_Hotfix_Latest[ComputerDNS] ) RETURN IF( SELECTEDVALUE(Q_Domain_Controllers_Latest[dnshostname]) IN _UPDATE, "Yes", "No" )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
