SOLVED SSRS Report | CUSTOM Report

[Deepak Rai]

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.

 

[Garth]

Is it possible? Sure it is possible.

Does this exist now? Maybe but it is not publicly available or not free. For example, this one exist but it is not free and doesn't give you everything that you asked for in one report.
As such you will need to create this yourself. i would start by looking at the built-in report and build up from there.

 

[Deepak Rai]

Is it possible? Sure it is possible.

Does this exist now? Maybe but it is not publicly available or not free. For example, this one exist but it is not free and doesn't give you everything that you asked for in one report.
As such you will need to create this yourself. i would start by looking at the built-in report and build up from there.

Hello Sir,
Thanks a lot for the reply. I already went through all the built-in reports and found the closest one for my requirement is "Software Distribution - Package and Program Deployment - All package and program deployments to a specified computer"

It has detailed information about
Distribution State, Deployment Comment, Package Name, Program Name, Source Site and Deployment ID.

The only challenge now is i am not finding the "Software Update Deployment" targeted to "Software Update Group" but rest all (Application and Package) deployments are available.

Below is the query::- Built-in Report query.

SELECT adv.AdvertisementName,
stat.LastStateName,
adv.Comment AS C072,
pkg.Name AS C062,
adv.ProgramName AS C071,
adv.SourceSite,
adv.AdvertisementID,
adv.PackageID,
adv.CollectionID,
pkg.PackageID + adv.ProgramName as PackageProgramID
FROM fn_rbac_Advertisement(@UserSIDs) adv
JOIN fn_rbac_Package2(@UserSIDs) pkg ON adv.PackageID = pkg.PackageID
JOIN fn_rbac_ClientAdvertisementStatus(@UserSIDs) stat ON stat.AdvertisementID = adv.AdvertisementID
JOIN fn_rbac_R_System(@UserSIDs) sys ON stat.ResourceID=sys.ResourceID
WHERE sys.Netbios_Name0=@ComputerName

================================================
PRAJWAL DESAI
================================================

Below one written by Prajwal few years back but that also not covering "Software Update Deployment"


Select
Softwarename,CollectionName,Collectionid,ds.DeploymentTime,NumberSuccess,NumberInProgress,NumberErrors,NumberUnknown,NumberTotal
from v_DeploymentSummary DS
where softwarename <> ' '
group by SoftwareName,
collectionname,DeploymentTime,NumberSuccess,NumberInProgress,NumberErrors,NumberUnknown,NumberTotal,Collectionid

 

[Garth]

Hello Sir,
Thanks a lot for the reply. I already went through all the built-in reports and found the closest one for my requirement is "Software Distribution - Package and Program Deployment - All package and program deployments to a specified computer"

It has detailed information about
Distribution State, Deployment Comment, Package Name, Program Name, Source Site and Deployment ID.

The only challenge now is i am not finding the "Software Update Deployment" targeted to "Software Update Group" but rest all (Application and Package) deployments are available.

Below is the query::- Built-in Report query.

SELECT adv.AdvertisementName,
stat.LastStateName,
adv.Comment AS C072,
pkg.Name AS C062,
adv.ProgramName AS C071,
adv.SourceSite,
adv.AdvertisementID,
adv.PackageID,
adv.CollectionID,
pkg.PackageID + adv.ProgramName as PackageProgramID
FROM fn_rbac_Advertisement(@UserSIDs) adv
JOIN fn_rbac_Package2(@UserSIDs) pkg ON adv.PackageID = pkg.PackageID
JOIN fn_rbac_ClientAdvertisementStatus(@UserSIDs) stat ON stat.AdvertisementID = adv.AdvertisementID
JOIN fn_rbac_R_System(@UserSIDs) sys ON stat.ResourceID=sys.ResourceID
WHERE sys.Netbios_Name0=@ComputerName

================================================
PRAJWAL DESAI
================================================

Below one written by Prajwal few years back but that also not covering "Software Update Deployment"


Select
Softwarename,CollectionName,Collectionid,ds.DeploymentTime,NumberSuccess,NumberInProgress,NumberErrors,NumberUnknown,NumberTotal
from v_DeploymentSummary DS
where softwarename <> ' '
group by SoftwareName,
collectionname,DeploymentTime,NumberSuccess,NumberInProgress,NumberErrors,NumberUnknown,NumberTotal,Collectionid

There is a built-in report for SU too. Comp #5.

 

[Deepak Rai]

There is a built-in report for SU too. Comp #5.

All separated but works for me. Thanks a lot for your time and we can mark this thread as resolved.


———————————————————————————————————
–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
—————————————————————————————————————-