Get from sql view vUpdateEffectiveApprovalPerComputer

[jemontagnier]

Hi,

Is there any way to get from PoshWSUS same view like vUpdateEffectiveApprovalPerComputer for all computers, please ?

With command below, i've get one needed update for client with NotApproved update.

$Computers = Get-PSWSUSUpdateSummaryPerClient -UpdateScope (New-PSWSUSUpdateScope -ApprovedStates LatestRevisionApproved)

Thanks a lot,
Regards

[jemontagnier]

Based from my sql query, i've found 0 update needed (1 not approved)

USE SUSDB
GO

SET NOCOUNT ON

SELECT Name AS ComputerName, mygroup AS "Group", 

COUNT((CASE WHEN (State='2' OR State='3' OR State='6' OR State='5') THEN State ELSE NULL END)) as NeededCount,
COUNT((CASE WHEN State='2' THEN State ELSE NULL END)) as NotInstalled,
COUNT((CASE WHEN State='3' THEN State ELSE NULL END)) as Downloaded,
COUNT((CASE WHEN State='6' THEN State ELSE NULL END)) as PendingReboot,
COUNT((CASE WHEN State='5' THEN State ELSE NULL END)) as FailedCount,

IPAddress, OSMajorVersion, OSMinorVersion, OSBuildNumber,
(
CASE
WHEN OSMajorVersion='5' AND OSMinorVersion='2' THEN 'Windows Server 2003 R2 Standard Edition'
WHEN OSMajorVersion='6' AND OSMinorVersion='0' THEN 'Windows Server 2008'
WHEN OSMajorVersion='6' AND OSMinorVersion='1' THEN 'Windows Server 2008 R2 Standard Edition'
WHEN OSMajorVersion='6' AND OSMinorVersion='2' THEN 'Windows Server 2012'
WHEN OSMajorVersion='6' AND OSMinorVersion='3' THEN 'Windows Server 2012 R2'
WHEN OSMajorVersion='10' AND OSMinorVersion='0' AND OSBuildNumber < 17676 THEN 'Windows Server 2016 Standard'
WHEN OSMajorVersion='10' AND OSMinorVersion='0' AND OSBuildNumber > 17676 THEN 'Windows Server 2019 Standard'
END) as OS, LastSyncTime, LastReportedStatusTime

FROM (

SELECT  PUBLIC_VIEWS.vComputerTargetGroup.Name AS mygroup, PUBLIC_VIEWS.vComputerTarget.Name,
PUBLIC_VIEWS.vUpdateInstallationInfoBasic.State, PUBLIC_VIEWS.vComputerTarget.IPAddress,
PUBLIC_VIEWS.vComputerTarget.OSMajorVersion, PUBLIC_VIEWS.vComputerTarget.OSMinorVersion, PUBLIC_VIEWS.vComputerTarget.OSBuildNumber,
PUBLIC_VIEWS.vComputerTarget.LastReportedStatusTime,
PUBLIC_VIEWS.vComputerTarget.LastSyncTime
FROM  PUBLIC_VIEWS.vUpdateApproval INNER JOIN
PUBLIC_VIEWS.vComputerGroupMembership ON
PUBLIC_VIEWS.vUpdateApproval.ComputerTargetGroupId = PUBLIC_VIEWS.vComputerGroupMembership.ComputerTargetGroupId INNER JOIN
PUBLIC_VIEWS.vComputerTarget ON
PUBLIC_VIEWS.vComputerGroupMembership.ComputerTargetId = PUBLIC_VIEWS.vComputerTarget.ComputerTargetId INNER JOIN
PUBLIC_VIEWS.vUpdateInstallationInfoBasic ON
PUBLIC_VIEWS.vComputerTarget.ComputerTargetId = PUBLIC_VIEWS.vUpdateInstallationInfoBasic.ComputerTargetId AND
PUBLIC_VIEWS.vUpdateApproval.UpdateId = PUBLIC_VIEWS.vUpdateInstallationInfoBasic.UpdateId INNER JOIN
PUBLIC_VIEWS.vComputerGroupMembership AS vComputerGroupMembership_1 ON
PUBLIC_VIEWS.vComputerTarget.ComputerTargetId = vComputerGroupMembership_1.ComputerTargetId INNER JOIN
PUBLIC_VIEWS.vComputerTargetGroup ON
vComputerGroupMembership_1.ComputerTargetGroupId = PUBLIC_VIEWS.vComputerTargetGroup.ComputerTargetGroupId

WHERE  (PUBLIC_VIEWS.vUpdateApproval.Action = 'Install') 
AND PUBLIC_VIEWS.vComputerTargetGroup.Name <> 'All Computers'
-- AND (PUBLIC_VIEWS.vUpdateInstallationInfoBasic.State IN (2, 3, 5, 6))
AND PUBLIC_VIEWS.vComputerTarget.Name='myserver.corp.local'
) AS sourcetbl

GROUP BY mygroup, Name, IPAddress, OSMajorVersion, OSMinorVersion, OSBuildNumber, LastReportedStatusTime, LastSyncTime
ORDER BY sourcetbl.mygroup, sourcetbl.Name;

Give me:

ComputerName	Group	NeededCount	NotInstalled	Downloaded	FailedCount	PendingReboot
myserver.corp.local	PRD-Group	0	0	0	0	0

But with PoshWSUS:

$Computers = Get-PSWSUSUpdateSummaryPerClient -UpdateScope (New-PSWSUSUpdateScope -ApprovedStates LatestRevisionApproved -UpdateApprovalActions Install) | where-object {$_.Computer -eq "myserver.corp.local"}
$Computers

Computer                  Installed Needed Failed PendingReboot 
--------                  --------- ------ ------ ------------- 
myserver.corp.local   4         1      0      0