Symantec Management Platform (Notification Server)

Hi All,

My requirement is to generate report for specific software based on add/remove entry with the column computer name, software display name,software display version, Computer model, Manfacturer, subnet address,primary user,last logged on user and last start date of the software.

My current report displays computer name, Primary user,last logged on user, software name & version with install date. Need help in adding other column such as model, manufacturer, last start date of software and last subnet address.

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca3_AddRemoveProgram].[DisplayName],
   [dca3_AddRemoveProgram].[DisplayVersion],
   [dca3_AddRemoveProgram].[InstallDate],
   [dca5_AeX AC Primary User].[User],
   [dca4_AeX AC Identification].[Last Logon User]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AddRemoveProgram] AS [dca3_AddRemoveProgram]
         ON ([vri2_Computer].[Guid] = [dca3_AddRemoveProgram].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca4_AeX AC Identification]
         ON ([vri2_Computer].[Guid] = [dca4_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca5_AeX AC Primary User]
         ON ([vri2_Computer].[Guid] = [dca5_AeX AC Primary User].[_ResourceGuid])
WHERE
   (
      (
         ([dca3_AddRemoveProgram].[DisplayName] LIKE N'%AdminStudio 2015%')
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )

Your help on this would be much appreciated.

Thanks,

Sam

You should be able to do the following...

SELECT 	[vC].[Guid],
	[vC].[Name],
	[ARP].[DisplayName],
	[ARP].[DisplayVersion],
	[ARP].[InstallDate],
	[vC].[User],
	[ACI].[Last Logon User],
	[CS].[Model],
	[CS].[Manufacturer],
	[MS].[Last Start],
	[AIP].[Subnet]
FROM vComputer [vC]
OUTER APPLY (SELECT TOP 1 [AIP].Subnet FROM Inv_Aex_AC_TCPIP AIP  WHERE [AIP]._ResourceGuid = [vC].Guid) AS [AIP]
LEFT JOIN Inv_AddRemoveProgram [ARP] ON [vC].[Guid] = [ARP]._ResourceGuid
LEFT JOIN Inv_AeX_AC_Identification [ACI] ON [ACI]._ResourceGuid = [vC].Guid
LEFT JOIN vHWComputerSystem [CS] ON [vC].Guid = [CS].[_ResourceGuid]
OUTER APPLY (
	SELECT MAX(MS.[Last Start]) as [Last Start]
	FROM Inv_Monthly_summary MS
	JOIN ResourceAssociation RA ON RA.[ParentResourceGuid] = [ARP]._SoftwareComponentGuid AND RA.ResourceAssociationTypeGuid = 'eabe86d3-aafd-487a-af63-5c95d7511af6'
	WHERE MS._ResourceGuid = [vC].Guid AND MS.FileResourceGuid = RA.ChildResourceGuid
) AS [MS]
WHERE [ARP].DisplayName like '%Symantec Endpoint Protection%'

You can technically have multiple subnets since a computer can have multiple network adapters. In the query above I am simply returning the first one.

Hope this helps

Chris

Hi Vis1234,

try this query.

DECLARE @v1_TrusteeScope nvarchar(max)
    SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca3_AddRemoveProgram].[DisplayName],
   [dca3_AddRemoveProgram].[DisplayVersion],
   [dca3_AddRemoveProgram].[InstallDate],
   [dca5_AeX AC Primary User].[User],
   [dca4_AeX AC Identification].[Last Logon User],
   [dca6_Monthly summary].[Last Start] AS [Last Start],
   [dca2_AeX AC TCPIP].[Subnet] AS [Client Subnet]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AddRemoveProgram] AS [dca3_AddRemoveProgram]
         ON ([vri2_Computer].[Guid] = [dca3_AddRemoveProgram].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca4_AeX AC Identification]
         ON ([vri2_Computer].[Guid] = [dca4_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca5_AeX AC Primary User]
         ON ([vri2_Computer].[Guid] = [dca5_AeX AC Primary User].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_Monthly_summary] AS [dca6_Monthly summary]
         ON ([vri2_Computer].[Guid] = [dca6_Monthly summary].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca2_AeX AC TCPIP]
         ON ([vri2_Computer].[Guid] = [dca2_AeX AC TCPIP].[_ResourceGuid])
WHERE
   (
      (
         ([dca3_AddRemoveProgram].[DisplayName] LIKE N'%AdminStudio 2015%')
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )

Thanks,

IP.

Hi Christopher,

Thanks for your help.

Is there a way to add last usage % to your existing report?

Igor - Thanks for your response too.

Regards,

Sam