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