SQL QUERY SSRS Report for Multiple Applications installed ?
Question
Hello Experts,
I am in a requirement for exporting a report for multiple applications installed and their version. I am not able to find a way to get it though.
I have a requirement to get report something like :
Can anyone please help me achieving this ?
Answers ( 2 )
Hi,
Above format template is more difficult to bring using SQL query. You can use the below SQL query to get the list of applications installed machines report then using SSRS you can do the report preparation.
—————————————————————————————————————————-
Declare @Collection varchar(8)
Set @Collection = ‘SMS00001’ — specify scope collection ID
Select
Distinct VRS.Name0 as ‘MachineName’,
Os.Caption0 as ‘OperatingSystem’,
St.SystemType00 as ‘OSType’,
VRS.AD_Site_Name0 as ‘ADSite’,
VRS.Full_Domain_Name0 as ‘Domain’,
VRS.User_Name0 as ‘UserName’,
v_R_User.Mail0 as ‘EMailID’,
App.ARPDisplayName0 as ‘DisplayName’,
App.InstallDate0 as ‘InstalledDate’,
App.ProductVersion0 as ‘Version’
from V_R_System VRS
LEFT JOIN v_GS_INSTALLED_SOFTWARE App ON VRS.ResourceID = App.ResourceID
LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID
LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID
LEFT Join v_FullCollectionMembership as Col on VRS.ResourceID = Col.ResourceID
LEFT JOIN v_R_User on VRS.User_Name0 = v_R_User.User_Name0
Where App.ARPDisplayName0 not in
(
‘Symantec Endpoint Protection’,
‘BMC Software Tools’,
‘WinPcap 4.1.3’,
‘PuTTY release 0.68 (64-bit)’,
‘Dell EMC SupportAssist Enterprise’
)
and Col.CollectionID = @Collection
and VRS.Client0 = 1 and VRS.Obsolete0 = 0
order by VRS.Name0, App.ProductVersion0
—————————————————————————————————————————-
Regards,
Karthikeyan
Hi Naveen,
find below query will helps for your requirement, you need to update application names which you want to collect data for App1 and App2 names
Select
vrs.Name0 AS [Computer Name],
vrs.Resource_Domain_OR_Workgr0 [Domain],
opp.Caption0 [Operating System],
CASE WHEN b.ClientActiveStatus = 1 then ‘Active’ else ‘InActive’ end as ‘CM Client Active Status’,
sof.DisplayName0 [App1 Software],
sof.Version0 [App1 Version],
app.DisplayName0 [App2 Software],
app.Version0 [App2 Version]
FROM
dbo.v_R_System AS VRS LEFT OUTER JOIN
dbo.v_CH_ClientSummary as b on vrs.ResourceID = b.ResourceID left outer join
dbo.v_ADD_REMOVE_PROGRAMS AS SOF ON vrs.ResourceID = sof.ResourceID LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM AS OPP ON vrs.ResourceID = opp.ResourceID left outer join
(Select x.ResourceID, y.DisplayName0, y.Version0, y.Publisher0, y.InstallDate0 FROM dbo.v_R_System AS x LEFT OUTER JOIN dbo.v_ADD_REMOVE_PROGRAMS AS y ON x.ResourceID = y.ResourceID where y.Displayname0 like ‘App2 Name’ AND y.Version0 not like ‘NULL’) as App on app.ResourceID = vrs.ResourceID
where sof.Displayname0 LIKE ‘App1 Name’
AND sof.Version0 not like ‘NULL’