Software Update Group Patch Status Query SQL

Question

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?

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

Answer

First create a table type:

CREATE TYPE DeploymentIDs AS TABLE (DeploymentID nvarchar(38) PRIMARY KEY)

Then change these lines:

 DECLARE @DEPLOYMENTID NVARCHAR(38)
 SET @DEPLOYMENTID=N'{415972C3-61EE-40D4-BAC3-DAA61F66ACD9}' -

To

 DECLARE @DEPLOYMENTIDS DEPLOYMENTIDs;
INSERT @DEPLOYMENTIDS 
   VALUES (N'{415972C3-61EE-40D4-BAC3-DAA61F66ACD9}'),
              (N'41000000-6100-40D4-BAC3-DAA61F66ACD9'),
              etc

Then change the line

 SET @params = N'@DEPLOYMENTID nvarchar(38)';

to

 SET @params = N'@DEPLOYMENTID DeploymentIDs READONLY;

Change this line:

a.Assignment_UniqueID=@DEPLOYMENTID

to

a.Assignment_UniqueID IN (SELECT DeploymentID FROM @DEPLOYMENTIDS)

And finally add an S to this line:

 EXECUTE sp_executesql @SQLString, @params, @DEPLOYMENTID

It may be that you need to make other modifications to the query to handle the multiple IDs. I am not familiar with your tables/views, so I cannot tell.

Answer

Thank you for responding.

Yes, I have to make other modifications, when trying I get this error. I am already validating I am not an expert in SQL.
If someone guides me grateful.

Answer

You need to create the table type separately. And you do that once. The CREATE TYPE statement should not be part of your script.

Share via

Software Update Group Patch Status Query SQL

3 answers