SSRS – Reporting -Patching

Question

Hi Team,

I am looking to prepare simple report which gives patching status for end users on monthly basis over email.

Do we have any article which gives sample report on this.

Will the below columns be sufficient for the report :

  1. Device name

2. Collection name

3. Status – Should show compliant or error or in progress

solved 0
ram kumar 2020-05-19T10:59:50+05:30 4 Answers 268 views Beginner
0

Answers ( 4 )

  2. ram kumar
    1
    2020-05-19T16:01:02+05:30
    Reply

    Hi Mohan

    i have updated the software update group deployment name and collection ID as below

    I am getting line 5 incorrect syntax error

    select
    cs.name0 ‘Computer_name’,
    os.Caption0 as ‘Operating System’,
    col.Name as [Collection Name],
    sum(case when UCS.status=3 then 1 else 0 end) ‘Installed’,
    sum(case when UCS.status=3 then 1 else 0 end)+sum(case when UCS.status=2 then 1 else 0 end) ‘Applicable’,
    sum(case when UCS.status=2 then 1 else 0 end) ‘Not Installed’,
    concat(CONVERT(DECIMAL(18,2),round((1.0*sum(case when UCS.status=3 then 1 else 0 end))/(1.0*(sum(case when UCS.status=3 then 1 else 0 end)+sum(case when UCS.status=2 then 1 else 0 end))),5,5)*100),’%’) ‘Compliant%’,
    case
    when (sum(case when UCS.LastEnforcementMessageID=9 then 1 else 0 end))>0
    then ‘Yes’
    else ‘No’
    end as ‘Pending restart’
    from
    v_UpdateComplianceStatus UCS
    left outer join dbo.v_R_System CS on CS.ResourceID = UCS.ResourceID
    left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
    left join v_collection col on fcm.CollectionID = col.CollectionID
    left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
    left join v_AuthListInfo LI on ucs.ci_id=li.ci_id
    left join v_GS_OPERATING_SYSTEM as os on CS.ResourceID = os.ResourceID
    Where
    FCM.collectionid in (‘PRI00014’)
    and li.Title like ‘Microsoft Software updates’
    Group by
    CS.Name0, COL.Name, OS.Caption0

  4. Mohan Kumar
    1
    2020-05-19T14:09:22+05:30
    Reply

    Hi Ramkumar,

    I have prepared custom SQL query for your requirement, pleas use below query i think it will works for you.

    2 thing needs to update on this deployment,
    Add all you patch deployment collection ID
    Add your software update group deployment name.

    select
    cs.name0 ‘Computer_name’,
    os.Caption0 as ‘Operating System’,
    col.Name as [Collection Name],
    sum(case when UCS.status=3 then 1 else 0 end) ‘Installed’,
    sum(case when UCS.status=3 then 1 else 0 end)+sum(case when UCS.status=2 then 1 else 0 end) ‘Applicable’,
    sum(case when UCS.status=2 then 1 else 0 end) ‘Not Installed’,
    concat(CONVERT(DECIMAL(18,2),round((1.0*sum(case when UCS.status=3 then 1 else 0 end))/(1.0*(sum(case when UCS.status=3 then 1 else 0 end)+sum(case when UCS.status=2 then 1 else 0 end))),5,5)*100),’%’) ‘Compliant%’,
    case
    when (sum(case when UCS.LastEnforcementMessageID=9 then 1 else 0 end))>0
    then ‘Yes’
    else ‘No’
    end as ‘Pending restart’
    from
    v_UpdateComplianceStatus UCS
    left outer join dbo.v_R_System CS on CS.ResourceID = UCS.ResourceID
    left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
    left join v_collection col on fcm.CollectionID = col.CollectionID
    left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
    left join v_AuthListInfo LI on ucs.ci_id=li.ci_id
    left join v_GS_OPERATING_SYSTEM as os on CS.ResourceID = os.ResourceID
    Where
    FCM.collectionid in (‘PRI0001’, ‘PRI0002’, ‘PRI0003’, ‘PRI0004’, ‘PRI0005’, ‘PRI0006’, ‘PRI0007’, ‘PRI0008’, ‘PRI0009’, ‘PRI0010’, ‘PRI0011’, ‘PRI00012’, ‘PRI0013’, ‘PRI0014’)
    and li.Title like ‘SUP Group Deployment Name’
    Group by
    CS.Name0, COL.Name, OS.Caption0

    Best answer
  6. karthikeyan
    1
    2020-05-19T11:40:51+05:30
    Reply

    Hi,

    Use below link to create custom SSRS Reports and configure SMTP channel with email subscription alert.

    https://gallery.technet.microsoft.com/TI095-Custom-Report-2b051812

    USe below SQL query to get the status for specific software update group

    –All Deployments status for Specific Software Update Group
    ————————————————————————————————————–
    Declare @SoftwareUpdateGroupName as varchar(255)
    Set @SoftwareUpdateGroupName = ‘All Updates SRWP2’ –Specify Software Update Group Name

    Select
    Vaa.AssignmentName as ‘DeploymentName’,
    Right(Ds.CollectionName,3) as ‘Stage’,
    Li.Title as ‘SUGroupName’,
    CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DepType’,
    Ds.CollectionName as ‘CollectionName’,
    CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,
    Ds.DeploymentTime as ‘AvailableTime’,
    Ds.EnforcementDeadline as ‘RequiredTime’,
    Ds.NumberTotal as ‘Target’,
    Ds.NumberSuccess as ‘Success’,
    Ds.NumberInProgress as ‘Progress’,
    Ds.NumberErrors as ‘Errors’,
    Ds.NumberOther as ‘Others’,
    Ds.NumberUnknown as ‘Unknown’,
    case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then ‘0’ Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as ‘Success%’,
    DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,
    DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’,
    DateDiff(D,Ds.CreationTime, GetDate()) as ‘CreatedDays’,
    Vaa.CreationTime as ‘CreationTime’,
    Vaa.LastModificationTime as ‘LastModifiedTime’,
    Vaa.LastModifiedBy as ‘LastModifiedBy’
    from v_DeploymentSummary Ds
    left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
    left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID
    Where Ds.FeatureType = 5
    and Li.Title like @SoftwareUpdateGroupName
    order by Ds.DeploymentTime desc
    ————————————————————————————————————–

    –Deployment Detailed status for specific software Update Group
    ————————————————————————————————–
    Declare @SoftwareUpdateGroupName as Varchar(255)
    Set @SoftwareUpdateGroupName = ‘SU WKS Security Updates 1803’ –Specify Software Update GroupName

    select
    vrs.Name0 as ‘ComputerName’,
    vrs.User_Name0 as ‘LastLogonUser’,
    vos.Caption0 as ‘Operating Sytem’,
    a.AssignmentName as ‘DeploymentName’,
    Right(Ds.CollectionName,3) as ‘Stage’,
    Li.Title as ‘SUGroupName’,
    CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,
    Ds.CollectionName as ‘CollectionName’,
    Ds.DeploymentTime as ‘AvailableTime’,
    Ds.EnforcementDeadline as ‘RequiredTime’,
    DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,
    DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’,
    DateDiff(D,Ds.CreationTime, GetDate()) as ‘CreatedDays’,
    a.StartTime as Available,
    a.EnforcementDeadline as Deadline,
    sn.StateName as LastEnforcementState,
    SUP.LastErrorCode as ‘LasErrorCode’,
    SUP.LastScanTime as ‘LastSUPScan’,
    DateDiff(D, SUP.LastScanTime, GetDate()) as ‘LastSUPScan Age’,
    wks.LastHWScan,
    DateDiff(D, wks.LastHwScan, GetDate()) as ‘LastHWScan Age’
    from v_CIAssignment a
    left join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
    left join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
    left join v_R_System vrs on vrs.ResourceID = assc.ResourceID
    Left Join V_GS_Operating_System Vos on Vrs.ResourceID = vos.resourceID
    left join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID
    left join v_UpdateScanStatus SUP on SUP.ResourceID = assc.ResourceID
    left join v_DeploymentSummary Ds on Ds.AssignmentID=assc.AssignmentID
    left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID
    where Li.Title = @SoftwareUpdateGroupName
    and assc.StateType in (300,301)
    order by 11 desc
    ————————————————————————————————–
    Thanks
    Karthikeyan

Leave an answer

Sorry, you do not have permission to answer to this question .