SSRS Report | CUSTOM Report

Question

Hello All,

I would like to create a report for all the active deployments targeted to specific server. Patch deployment, Advertisement, Application deployment, package deployment, SUG deployment etc.
When i enter server name then it should list all these. May i know if it’s possible? If yes then RDL link appreciated.

Not Restricting myself only to SSRS, PowerBI can also work.

solved 0
Deepak Rai 11 months 2020-06-01T21:31:08+05:30 3 Answers 417 views Admin 0

Answers ( 3 )

    0
    2020-06-02T20:23:34+05:30

    Thanks a lot Karthikeyan.

    Software Update Group Deployment for specific machine :- This one is what i was majorly looking for.

    As Anoop said already , you are really SSRS Super Hero. I am good to close this thread.

    3
    2020-06-02T18:34:18+05:30

    Hi,

    It’s more difficult to bring everything in single SQL query. because all deployments
    information will be stored in unique views and tables.

    For example:
    ———————
    Package deployment view name – vSMS_ClientAdvertisementStatus
    Application deployment view name – vAppDTDeploymentResultsPerClient
    Software Update deployment view name – v_StateNames
    Compliance setting deployment view name – v_CICurrentComplianceStatus

    I will share the individual SQL queries for above deployments. You can use report builder to club everything in single ssrs report.

    ———————————————————————————————————
    –Application Deployment for specific machine
    ———————————————————————————————————
    Declare @Machinename as Varchar(255)
    Set @Machinename = ‘MachineName%’ — Specify Machine name

    select
    aa.ApplicationName as ‘Application Name’,
    aa.CollectionName as ‘Target Collection’,
    CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,
    Vrs.Name0 as ‘ComputerName’,
    case when ae.AppEnforcementState like ‘10%’ then ‘Success’
    when ae.AppEnforcementState like ‘20%’ then ‘Progress’
    when ae.AppEnforcementState like ‘30%’ then ‘ReqNotMet’
    when ae.AppEnforcementState like ‘40%’ then ‘Unknown’
    when ae.AppEnforcementState like ‘50%’ then ‘Failed’
    End as ‘DeploymentStatus’,
    ci2.LastComplianceMessageTime as ‘LastMessageTime’
    from v_R_System Vrs
    Inner join vAppDTDeploymentResultsPerClient ae on ae.ResourceID = Vrs.ResourceID
    Inner join v_CICurrentComplianceStatus ci2 on ci2.CI_ID = ae.CI_ID AND ci2.ResourceID=Vrs.ResourceID
    Inner join v_ApplicationAssignment aa on ae.AssignmentID = aa.AssignmentID
    Inner join v_DeploymentSummary Ds on Ds.AssignmentID = aa.AssignmentID
    where ae.AppEnforcementState is not null
    and Vrs.Name0 like @Machinename

    —————————————————————————————————————
    –Package Deployment for specific machine
    —————————————————————————————————————
    Declare @Machinename as Varchar(255)
    Set @Machinename = ‘MachineName%’ — Specify Machine name

    select distinct
    pack.Name as ‘Package Name’,
    COLL.CollectionName as ‘Target Collection’,
    adv.ProgramName as ‘Purpose’,
    Vrs.Name0 as ‘ComputerName’,
    ADVS.LastStateName as ‘DeploymentStatus’,
    ADVS.LastStatusTime as ‘LastMessageTime’
    from v_R_System Vrs
    INNER JOIN vSMS_ClientAdvertisementStatus ADVS ON Vrs.ResourceID = ADVS.ResourceID
    INNER JOIN v_Advertisement ADV ON ADV.AdvertisementID = ADVS.AdvertisementID
    INNER JOIN v_FullCollectionMembership CM ON Vrs.ResourceID = CM.ResourceID
    LEFT JOIN v_Package Pack on adv.PackageID = pack.PackageID
    LEFT JOIN v_Collections COLL ON ADV.CollectionID = COLL.SiteID
    where Vrs.Name0 like @Machinename
    Order by Vrs.Name0

    ——————————————————————————————————
    — Software Update Group Deployment for specific machine
    ——————————————————————————————————
    Declare @MachineName as Varchar(255)
    Set @MachineName = ‘MachineName%’ –Specify Machine Name

    select
    Li.Title as ‘SUGroupName’,
    a.AssignmentName as ‘DeploymentName’,
    Ds.CollectionName as ‘CollectionName’,
    CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,
    vrs.Name0 as ‘ComputerName’,
    sn.StateName as ‘LastEnforcementState’,
    SUP.LastScanTime as ‘LastSUPScan’,
    SUP.LastErrorCode as ‘LasErrorCode’
    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_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 Vrs.name0 like @MachineName
    and assc.StateType in (300,301)

    —————————————————————————————————————–
    –Compliance Settings Deployment for specific machine
    —————————————————————————————————————–
    Declare @MachineName as Varchar(255)
    Set @MachineName = ‘MachineName%’ –Specify MachineName

    Select
    Lci.DisplayName as ‘ComplianceSettingsName’,
    Vrs.Name0 as ‘MachineName’,
    Case cs.ComplianceState
    when 1 then ‘Compliant’
    when 2 then ‘Non Compliant’
    when 4 then ‘Error’
    Else ‘Unknown’ End as ‘ComplianceStatus’,
    cs.LastComplianceMessageTime as ‘LastUpdateTime’
    FROM v_R_System Vrs
    Left JOIN v_BaselineTargetedComputers Btc ON Vrs.ResourceID = Btc.ResourceID
    Left JOIN v_ConfigurationItems Ci ON Btc.CI_ID = CI.CI_ID
    Left JOIN v_CICurrentComplianceStatus Cs ON cs.CI_ID = Ci.CI_ID and cs.ResourceID = Btc.ResourceID
    Left JOIN v_LocalizedCIProperties_SiteLoc Lci ON Lci.CI_ID = Ci.CI_ID
    WHERE Vrs.Name0 like @MachineName
    ORDER BY cs.ComplianceState
    —————————————————————————————————————-

    Kindly let me know if you need any other help from my side.

    Thanks
    Karthikeyan

    Best answer
    Cancel the best answer
    1
    2020-06-01T22:50:48+05:30

    We will wait Karthikeyan to reply SSRS super hero

Leave an answer

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