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 4 months 3 Answers 304 views Admin 0

Answers ( 3 )

  1. 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.

  2. 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
  3. We will wait Karthikeyan to reply SSRS super hero

Leave an answer

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