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 :

Report Example - HTMD Forum - Welcome to the world of Device Management! This is community build by Device Management Admins for Device Management Admins❤️ Ask your questions!! We are here to help you! - SQL QUERY SSRS Report for Multiple Applications installed ?

 

Can anyone please help me achieving this ?

solved 0
Naveen Joshi 2020-05-19T11:44:18+05:30 2 Answers 281 views Beginner
0

Answers ( 2 )

  2. karthikeyan
    0
    2020-05-20T10:39:16+05:30
    Reply

    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

    Best answer
  4. Mohan Kumar
    3
    2020-05-19T13:46:04+05:30
    Reply

    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’

Leave an answer

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