Software Update Group Patch Status

Hi Team.

Currently he gave the client a patch status report of the month, where he shows me all the computers and their different patch status (Installed, Not required, Error, in progress, pending restart or unknown status).

More than 100 software update group (SUG) including SSU are deployed.

I have a query that I use to query each SUG for the deployment ID, how do I integrate more than one deployment ID? It takes me a long time to query all SUGs.

There are many integrated reports but the customer wants with those features.

Could you guide me to improve that query?

QUERY___________________

DECLARE @DEPLOYMENTID NVARCHAR(38)
SET @DEPLOYMENTID=N'{415972C3-61EE-40D4-BAC3-DAA61F66ACD9}’ — deployment id
DECLARE @params NVARCHAR(500)
SET @params = N’@DEPLOYMENTID nvarchar(38)’;

DECLARE @columnas varchar(max)
SET @columnas = ”

SELECT @columnas = coalesce(@columnas + ‘[KB’ + cast(ui.ArticleID as varchar(20))
+’-‘+ cast(ui.CI_ID as varchar(20))+’],’, ”)
FROM v_CIAssignment a
JOIN v_CIAssignmentToCI atc
JOIN CI_CIAssignments ass
ON ass.AssignmentID = atc.AssignmentID
JOIN v_ConfigurationItems cis
ON cis.CI_ID = atc.CI_ID
JOIN v_Collection coll
ON coll.CollID = ass.TargetCollectionID
ON atc.AssignmentID=a.AssignmentID
JOIN v_UpdateCIs ui
ON ui.CI_ID=atc.CI_ID
WHERE a.Assignment_UniqueID=@DEPLOYMENTID and a.AssignmentType in (1,5)
ORDER BY ui.ArticleID, ui.CI_ID
–select @columnas column1

set @columnas = left(@columnas,LEN(@columnas)-1)

–select @columnas column2

DECLARE @SQLString nvarchar(max);

set @SQLString = N’
SELECT aci.CI_ID, uc.CI_ID CI_IDuc, aci.AssignmentID, uc.ResourceID, uc.StateType, uc.StateID
, uc.LastEnforcementStatusMsgID
INTO #TABLE1
FROM v_UpdateState_Combined uc
JOIN v_CIAssignmentToCI aci
ON aci.CI_ID = uc.CI_ID

SELECT *
FROM
(

SELECT
m.Name0 as Equipo
, ”KB”+ ui.ArticleID +”-”+ cast(ui.CI_ID as varchar(10)) UpdateID
, case when sn.StateName = ”Update is Installed” then ”Installed”
when sn.StateName = ”Pending System Restart” then ”Restart”
when sn.StateName = ”Successfully installed update” then ”Installed”
when sn.StateName = ”Update is Required” then ”in progress”
when sn.StateName = ”Update is not Required”then ”not Required”
when sn.StateName = ”Failed to download update”then ”error”
when sn.StateName = ”Failed to install update” then ”error”
when sn.StateName = ”General Failure” then ”error”
when sn.StateName = ”Detection State Unknown”then ”State Unknown”
else sn.StateName end as Status
FROM #TABLE1 AS uc
JOIN v_ConfigurationItems cis
ON cis.CI_ID = uc.CI_IDuc
JOIN CI_CIAssignments ass
ON ass.AssignmentID = uc.AssignmentID
JOIN v_Collection coll
ON coll.CollID = ass.TargetCollectionID
JOIN v_UpdateCIs ui
ON ui.CI_ID=uc.CI_ID
JOIN v_CIAssignmentTargetedMachines ast
ON ast.ResourceID=uc.ResourceID and ast.AssignmentID=uc.AssignmentID
JOIN v_R_System m
ON m.ResourceID=uc.ResourceID and isnull(m.Obsolete0,0)=0
JOIN v_CIAssignment a
ON uc.AssignmentID=a.AssignmentID and a.Assignment_UniqueID=@DEPLOYMENTID
and a.AssignmentType in (1,5)
LEFT JOIN v_StateNames sn
ON sn.TopicType = uc.StateType and sn.StateID = uc.StateID
LEFT JOIN v_RA_System_SMSAssignedSites asite
ON m.ResourceID = asite.ResourceID
LEFT JOIN v_AdvertisementStatusInformation statusinfo
ON statusinfo.MessageID=nullif(uc.LastEnforcementStatusMsgID&0x0000FFFF, 0)
) AS SourceTable
PIVOT
(
MAX(Status)
FOR UpdateID IN (‘ + @columnas + ‘)
) AS PivotTable
Order by Equipo;

DROP TABLE #TABLE1′

EXECUTE sp_executesql @SQLString, @params, @DEPLOYMENTID