Client Management Suite

 View Only

  • 1.  Modifying a Report

    Posted Nov 11, 2021 03:43 PM
    Hello,

    I need some help adding a column to an existing customer report.  The report pulls the device name, User, and Software Product Name.  I need to add in a column showing what OU the computer is in. Below is my current report.  I am very new to reports, and any help would be appreciated. 

    Thank you for your help

    Kris 
     


    SELECT DISTINCT
    [vri1_Computer].[Name] AS [Name],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[Software Product Name] AS [Software Product Name],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[User] AS [User],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[Install Date] AS [Install Date]

    FROM
    [vRM_Computer_Item] AS [vri1_Computer]
    LEFT OUTER JOIN [vAC_ComputerLicenseAndUsageByProduct] AS [ajs2_vAC_ComputerLicenseAndUsageByProduct]
    ON ([vri1_Computer].[Guid] = [ajs2_vAC_ComputerLicenseAndUsageByProduct].[ResourceGuid])

    WHERE
    ([ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '58113f11-959b-4408-82ad-c72ced0739d1'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '8a2bc769-3017-44a5-a288-d2eebd979b4c'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '7deaa3a1-262c-4f73-859b-f8f2744d365e'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'e9056d92-ae6f-41bb-9d66-c02ce836cf3e'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '74fa3f22-4e44-47c8-bb4e-8ddc24d3817e'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '3f50b153-ab14-468d-8dc6-a14ee19e1b38'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '1979afa6-8802-4793-ad97-91565704d818'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '0b0091e6-1f72-4486-9c63-adec52425b39'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'e320910f-9b7d-4b79-9517-d7a96312ada1'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'ca2cb000-95cf-4c2c-8519-c90a11d93853'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '79b9d58f-8fc2-4b94-8f79-988e3a741196'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'fce46f42-5404-4254-b202-3c8afedb4690'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '44d73238-7871-400e-94ec-d43da4c0f105'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '813d0fce-5680-4a41-8e4a-01d79b248cdc'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '0092a9b1-d453-43c5-8e3b-a59182970d5d'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '40190395-51eb-4473-991d-8aa18da12576'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'c784a7d9-d09f-45d1-a8eb-21a17a9f7a2f'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '53aef681-6612-43f6-b470-0d7d6885b2d6'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '33dd735e-d4fb-4100-81cc-c42d64c67c4e'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'afce50e2-0839-4ee8-9590-382809454d0c'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '37864800-9f8a-452f-b274-7a8fecd92f19'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '5a4f7b90-89c6-4014-b799-855de9e57917'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '8464955e-464d-49c5-8ed7-835890e300af'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'dac79258-157d-4b48-8765-d8a2e70e0184'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'b67bb52f-f4ee-477e-9c86-d8738520b7ee'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '0ea907db-1ace-4910-ab8d-f3d400991cc6')

    GROUP BY
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[Software Product Name],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[Install Date],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[User],
    [vri1_Computer].[Name]
    ORDER BY
    [Software Product Name],
    [Install Date],
    [User]


  • 2.  RE: Modifying a Report

    Broadcom Employee
    Posted Nov 11, 2021 09:50 PM

    Hi

    If you meant OU that is OU from Active Directory and you have AD import done, then you can try this one

    SELECT DISTINCT
    [vri1_Computer].[Name] AS [Name],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[Software Product Name] AS [Software Product Name],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[User] AS [User],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[Install Date] AS [Install Date],
    [dca2_OU Membership].[Distinguished Name] AS [Distinguished Name]

    FROM
    [vRM_Computer_Item] AS [vri1_Computer]
    LEFT OUTER JOIN [vAC_ComputerLicenseAndUsageByProduct] AS [ajs2_vAC_ComputerLicenseAndUsageByProduct]
    ON ([vri1_Computer].[Guid] = [ajs2_vAC_ComputerLicenseAndUsageByProduct].[ResourceGuid])
    LEFT OUTER JOIN [Inv_OU_Membership] AS [dca2_OU Membership]
    ON ([vri1_Computer].[Guid] = [dca2_OU Membership].[_ResourceGuid])

    WHERE
    ([ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '58113f11-959b-4408-82ad-c72ced0739d1'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '8a2bc769-3017-44a5-a288-d2eebd979b4c'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '7deaa3a1-262c-4f73-859b-f8f2744d365e'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'e9056d92-ae6f-41bb-9d66-c02ce836cf3e'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '74fa3f22-4e44-47c8-bb4e-8ddc24d3817e'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '3f50b153-ab14-468d-8dc6-a14ee19e1b38'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '1979afa6-8802-4793-ad97-91565704d818'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '0b0091e6-1f72-4486-9c63-adec52425b39'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'e320910f-9b7d-4b79-9517-d7a96312ada1'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'ca2cb000-95cf-4c2c-8519-c90a11d93853'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '79b9d58f-8fc2-4b94-8f79-988e3a741196'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'fce46f42-5404-4254-b202-3c8afedb4690'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '44d73238-7871-400e-94ec-d43da4c0f105'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '813d0fce-5680-4a41-8e4a-01d79b248cdc'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '0092a9b1-d453-43c5-8e3b-a59182970d5d'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '40190395-51eb-4473-991d-8aa18da12576'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'c784a7d9-d09f-45d1-a8eb-21a17a9f7a2f'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '53aef681-6612-43f6-b470-0d7d6885b2d6'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '33dd735e-d4fb-4100-81cc-c42d64c67c4e'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'afce50e2-0839-4ee8-9590-382809454d0c'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '37864800-9f8a-452f-b274-7a8fecd92f19'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '5a4f7b90-89c6-4014-b799-855de9e57917'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '8464955e-464d-49c5-8ed7-835890e300af'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'dac79258-157d-4b48-8765-d8a2e70e0184'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = 'b67bb52f-f4ee-477e-9c86-d8738520b7ee'
    OR [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid] = '0ea907db-1ace-4910-ab8d-f3d400991cc6')

    GROUP BY
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[SoftwareProductGuid],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[Software Product Name],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[Install Date],
    [ajs2_vAC_ComputerLicenseAndUsageByProduct].[User],
    [vri1_Computer].[Name],
    [Distinguished Name]
    ORDER BY
    [Software Product Name],
    [Install Date],
    [User]


    Just a reminder that in case if you need to build own report including there required data, you can use this report builder in ITMS views page. In search field type name of required data class or resource associations, etc and in real time you can create required report

    Best regards,
    IP.



    ------------------------------
    [JobTitle]
    [CompanyName]
    [State]
    ------------------------------

    Original Message