Ms Visio MS Project Custom Report

Question

Hi All, Is there any SQL query for add or remove Ms Visio and MS Project application with Last login detail of user that is locally installed on user device?

SQL query

solved 0
Saurabh Sharma 2020-06-20T20:34:22+05:30 7 Answers 272 views Beginner
0

Answers ( 7 )

  2. Saurabh Sharma
    0
    2020-06-24T10:11:43+05:30
    Reply

    Thanks Karthik & Guru,

    Still it’s not giving me exact detail that is required.

    Thanks
    Saurabh

  4. Karthikeyan
    0
    2020-06-21T07:12:35+05:30
    Reply

    Hi,

    Starting from SCCM 1906 version (v_Add_Remove_Programs and v_Add_Remove_Programs_X64 views are no more exists in SCCM Database).

    You need to use only V_GS_Installed_Software view to get the installed applications details. using this view either 32 bit and 64 bit applications will be captured on same view.

    I will share the updated SQL query for your requirement ASAP.

    Thanks
    Karthikeyan

    • Karthikeyan
      1
      2020-06-21T07:55:19+05:30
      Reply

      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 like ‘%visio%’ or App.ARPDisplayName0 like ‘%project%’)
      and Col.CollectionID = @Collection
      and VRS.Client0 = 1 and VRS.Obsolete0 = 0
      order by VRS.Name0, App.ProductVersion0

      Best answer
    • Saurabh Sharma
      0
      2020-06-22T07:42:50+05:30
      Reply

      Thanks Karthik,

      I will wait for the query.

      • Karthikeyan
        0
        2020-06-22T08:05:59+05:30
        Reply

        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 like ‘%visio%’ or App.ARPDisplayName0 like ‘%project%’)
        and Col.CollectionID = @Collection
        and VRS.Client0 = 1 and VRS.Obsolete0 = 0
        order by VRS.Name0, App.ProductVersion0

  6. Guru Vaidya
    1
    2020-06-21T04:47:34+05:30
    Reply

    You may try below one and tweak per your needs. Hope it helps.

    SELECT DISTINCT
    sys.Netbios_Name0 AS [Host name],
    arp.DisplayName0 AS [Software],
    arp.ProdID0 AS [Product Code],
    arp.Version0 AS [Software Version],
    arp.InstallDate0 AS [INstalled Date],
    arp.Publisher0 AS [Manufacturer],
    last.LastHardwareScan AS [Last HW Scan],
    –sys.User_Name0 AS [LastLogOn],
    –sys.AD_Site_Name0 AS [AD Site],
    –os.Caption0 AS [OperatingSystem],
    –OS.Installdate0 as OSInstallDate,
    –os.CSDVersion0 AS [ServicePack],
    fcm.Domain AS [Domain]

    FROM v_R_System sys
    INNER JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    INNER JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
    INNER JOIN v_GS_OPERATING_SYSTEM os ON sys.ResourceID = os.ResourceID
    INNER JOIN vWorkstationStatus Last ON sys.ResourceID = last.ResourceID
    WHERE
    fcm.CollectionID=’SMS00001′
    AND (arp.DisplayName0 LIKE (‘Visio%’)
    OR arp.DisplayName0 LIKE (‘Project%’)
    OR arp.DisplayName0 LIKE (‘Winzip%’))

    ORDER BY arp.DisplayName0, [Software Version]

Leave an answer

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