ok spent the last couple of hours drilling into this - in smp8.6 GA its not using the executable report correctly. vAMMonthlySummary is not populating correctly for me - I don't know if its because i have the exe wrong or Symantec is not counting it correctly . actually for my personal machine it shows no usage of any executable.
I know that is not correct In my previous example it is only showing 6 machines running snagit - my machine is not showing - and i use it all the time
i broke out the usage report from the software licensing report and came up with the following using snagit as a example. The amount of machines is correct - 2300 , the last used time is correct as is the install date. For my machine and all my other machines
the Run Count is wrong -
This really bugs me as i really want that cpu utilzation and run count to be accurate - still digging
SELECT
ResourceGuid,
Name,
SoftwareProductGuid,
[Software Product Name],
MAX([Install Date]) AS [Install Date],
MAX([Last Used]) AS [Last Used],
[Run Count],
CASE ISNULL(MAX(LicenseStatus), 0)
WHEN 0x0001 THEN 'USED' WHEN 0x0002 THEN 'UNTRACKED' WHEN 0x0004 THEN 'UNUSED' WHEN 0x0100
THEN 'UNAUTHORIZED' ELSE 'UNKNOWN' END AS [License Status Text],
[Usage Status Text],
[User]
FROM (SELECT u.ResourceGuid, u.Name, u.SoftwareProductGuid, u.[Software Product Name], u.[Install Date],
u.[Last Used],u.[Run Count], u.LicenseStatus, u.[Usage Status Text], ISNULL(userdetails.[Display Name],
ISNULL(NULLIF (LTRIM(RTRIM(ISNULL(userdetails.[Given Name], '') + ' ' + ISNULL(userdetails.Surname, ''))), ''), usr.Name)) AS [User]
FROM (SELECT c.Guid AS ResourceGuid, c.Name, productusage.SoftwareProductGuid, swpi.Name AS [Software Product Name], DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()),
productusage.[Last Start]) AS [Last Used], productusage.[Run Count], productusage.[Install Date],
CASE WHEN (dbo.fnAC_IsProductLicensed(productusage.SoftwareProductGuid) = 2 AND lu.[User] IS NOT NULL)
THEN lu.[User] ELSE primuser.ChildResourceGuid END AS UserGuid,
ISNULL(lu.State, 0) AS LicenseStatus, CASE WHEN ISNULL(spu.IsUsageTracked, 0) = 0 OR
productusage.[Run Count] IS NULL OR
productusage.[Last Start] IS NULL THEN 'UNTRACKED' ELSE
CASE WHEN productusage.[Run Count] > 0
AND DATEDIFF(dd, DATEADD(dd, - ISNULL(spu.UsageCount, 90), DATEADD(DAY, DATEDIFF(DAY,
0, GETUTCDATE()), 0)),
productusage.[Last Start]) >= 0 THEN 'USED' ELSE 'UNUSED' END END AS [Usage Status Text]
FROM
(SELECT inst._ResourceGuid AS ResourceGuid,
pcc.ParentResourceGuid AS SoftwareProductGuid,
cusage.UserGuid,
SUM(cusage.[Run Count]) AS [Run Count],
MAX(cusage.[Last Start]) AS [Last Start],
MAX(inst.InstallDate) AS [Install Date]
FROM dbo.ResourceAssociation AS pcc INNER JOIN
dbo.Inv_InstalledSoftware AS inst
ON inst._SoftwareComponentGuid = pcc.ChildResourceGuid
AND inst.InstallFlag = 1
LEFT OUTER JOIN dbo.vAC_MeteringData AS cusage
ON cusage._ResourceGuid IS NOT NULL
AND cusage._ResourceGuid = inst._ResourceGuid
AND cusage.component = pcc.ChildResourceGuid AND
cusage.FileMetered = 1
WHERE (pcc.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483')
GROUP BY inst._ResourceGuid,
pcc.ParentResourceGuid, cusage.component, cusage._ResourceGuid, cusage.UserGuid) AS productusage
INNER JOIN
dbo.vRM_Software_Product_Item AS swpi ON productusage.SoftwareProductGuid = swpi.Guid INNER JOIN
dbo.vComputer AS c ON c.Guid = productusage.ResourceGuid LEFT OUTER JOIN
dbo.Inv_Software_Product_Usage AS spu ON productusage.SoftwareProductGuid = spu._ResourceGuid LEFT OUTER JOIN
dbo.vAssetLicenseUsage AS lu ON lu.Product = productusage.SoftwareProductGuid
AND lu.Device = productusage.ResourceGuid AND (lu.[User] = productusage.UserGuid OR
productusage.UserGuid IS NULL) LEFT OUTER JOIN
dbo.vAssetMaster AS primuser ON productusage.ResourceGuid = primuser.ParentResourceGuid) AS u LEFT OUTER JOIN
dbo.RM_ResourceUser AS usr ON u.UserGuid = usr.Guid LEFT OUTER JOIN
dbo.Inv_Global_User_General_Details AS userdetails ON usr.Guid = userdetails._ResourceGuid) AS g
where [Software Product Name] like '%snag%'
GROUP BY ResourceGuid, Name, SoftwareProductGuid, [Software Product Name], [Run Count],[Usage Status Text], [User]
------------------------------
Altiris Admin since 2002
------------------------------
Original Message:
Sent: 07-29-2021 12:50 PM
From: Hagai Nachmani
Subject: Application Metering Name
Thank you Benjamin, this looks promising ��
Tnx,
Hagai
---------------------------------------------------------------------
A member of the Intel Corporation group of companies
This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
Original Message:
Sent: 7/29/2021 12:41:00 PM
From: benjamin.barker
Subject: RE: Application Metering Name
use Symantec_CMDB_prod
select
vfile.Name
,i.Name as [Computer Name]
,i.[OS Name]
,ms.[Average CPU Usage]
--,ms.[Last Start]
,ms.[Peak Memory]
,ms.[Run Count]
,ms.[Total Run Time]
,ms.[Total Run Time] / 60 / 60 as [Hours Run Time]
, vfile.Name
,ms.[Month Year]
,ms.[Month End Summary]
, u.Surname as [Last]
,u.[Given Name] as [First]
,u.[Job Title]
,u.Department
,u.Email
from vAMMonthlySummary ms
join vFile vFile on vFile.Guid = ms.FileResourceGuid
left join vComputer i on i.guid = ms._ResourceGuid
join vUser u on u.Guid = ms.UserGuid
where
i.IsManaged = 1
and ms.[Month Year] like 'JULY 2021'
Original Message:
Sent: 7/29/2021 12:11:00 PM
From: Hagai Nachmani
Subject: RE: Application Metering Name
Thanks again Shawn,
By the way, how would you tackle the query if you wish to monitor more that one EXE file at once.
And could you please explain about the monthly metering action you mentioned:
Does this means every month the report resets it's self after collecting a daily info, or it just collects the info once a month every month?
Tnx,
Hagai
---------------------------------------------------------------------
A member of the Intel Corporation group of companies
This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
Original Message:
Sent: 7/29/2021 10:54:00 AM
From: Shawn Mayhew
Subject: RE: Application Metering Name
Also remember how app metering works - it summarizes run start and end times into a localized table on the host machine. You can get data mid month but its best to let it bake in. IF you can get management to let you do this.
also verify what EXE is the primary - i have gone back and marked multiple exe as the ones to track start and run time.
------------------------------
Altiris Admin since 2002
Original Message:
Sent: 07-29-2021 02:55 AM
From: Hagai Nachmani
Subject: Application Metering Name
Thank you Shawn!, this indeed is comprehensive. I will try to good it to good use :)
thanks,
Hagai
Original Message:
Sent: 07-28-2021 04:27 PM
From: Shawn Mayhew
Subject: Application Metering Name
Mines a touch more involved
select distinct
Right(LEft (ac.name,3),2) as 'Location Code',
CASE when (ac.Name = prime.[primary user]) then 'Clinical Machine' else 'Information Worder' end as 'Machine Type',
ac.name,
convert(varchar, ac.[client date], 101) as 'ScanDate',
ac.[OS Name] AS 'OS Name',
vasset.Manufacturer,
vAsset.Model,
vAsset.[Serial Number],
Prime.[primary user],
T10.[Given Name] as 'First Name',
ISNULL(T10.[Surname],'') AS 'Last Name',
T10.[office telephone],
T10.department,
i.[Name], ac.[Client Date],
filen.ProductName,
left(filen.ProductVersion,1)as 'Version' ,
vms.[Run Count],
vms.[Total Run Time],
vms.[Last Start] AS [Last Run],vms.[Month Year],
vms.[peak memory], vms.[average cpu usage]
FROM dbo.vComputer i
left join dbo.Inv_AeX_AC_Identification ac on i.guid = ac._resourceguid
INNER JOIN (select * from dbo.Inv_Installed_File_Details det2 where det2.Name = 'snagit.exe' ) prog
ON ac._ResourceGuid = prog._ResourceGuid
inner join dbo.Inv_Windows_File filen on filen._ResourceGuid = prog.FileResourceGuid
LEFT JOIN vAMMonthlySummary vms ON vms.FileResourceGuid = prog.FileResourceGuid
and vms._ResourceGuid =ac._ResourceGuid
left join vAsset on vasset._ResourceGuid = ac._ResourceGuid
left join vComputer vc on vc.Guid =ac._ResourceGuid
left join (SELECT _ResourceGuid, MAX([user]) AS [primary user]
FROM dbo.Inv_AeX_AC_Primary_User
WHERE ([User] IS NOT NULL) AND ([User] <> '')
GROUP BY _ResourceGuid ) Prime
on ac._resourceguid = Prime._ResourceGuid
LEFT JOIN (select distinct vuser.* from vuser where [Given Name] !='' )t20
ON Prime.[primary user] = T20.name
LEFT OUTER JOIN dbo.Inv_Global_User_General_Details T10
ON t20.Guid = T10._ResourceGuid
WHERE
ac.[Client Date] > GETDATE ()-20
order by i.Name asc
------------------------------
Altiris Admin since 2002
Original Message:
Sent: 07-27-2021 11:37 AM
From: Hagai Nachmani
Subject: Application Metering Name
Thank you Joe, it looks very helpful, and I am trying to also combine the RunCount number into to this as well (maybe count it after the report created, and then summarize the data).
thanks,
Hagai
Original Message:
Sent: 07-25-2021 10:44 AM
From: JOE VANHOLLEBEKE
Subject: Application Metering Name
Hagai,
Here is a query I used to report a list of machines based on a specific metering policy. Replace 'Oracle Java' with the policy name you want to report on. Hope this helps:
--Java JRE execution details
SELECT DISTINCT
appstart.[Start Date],
vc.[Name] as 'Computer Name',
vc.[IP Address],
vc.[User] [Machine User],
vu.[Name] [App User],
vu.[Display Name],
appstart.[Policy Name],
appstart.[FilePath],
appstart.[Command Line]
FROM [Evt_Application_Start] appstart
JOIN VComputer vc on vc.[Guid] = appstart.[_ResourceGuid]
LEFT JOIN vUser vu on vu.Guid = appstart.UserGuid
WHERE appstart.[Policy Name] = 'Oracle Java'
ORDER BY appstart.[Start Date] desc
------------------------------
Joe
Original Message:
Sent: 07-22-2021 08:25 AM
From: Hagai Nachmani
Subject: Application Metering Name
Hi Igor, great! thanks, i will try and use this.
we are trying to add more info to the original table, but when comparing _ResourceGUID and FileResourceGuid with Inv_Monthly_summary table, the numbers don't add up.
so return to basics it is :)
thanks again,
Hagai
Original Message:
Sent: 07-22-2021 08:22 AM
From: Igor Perevozchikov
Subject: Application Metering Name
Hi Hagai Nachmani!
You can clone existing report, then open cloned report in edit mode and check "Parameterized Query" tab or get a resolved query
Thanks,
IP.
------------------------------
Software QA Engineer
Broadcom Inc.
Original Message:
Sent: 07-22-2021 08:18 AM
From: Hagai Nachmani
Subject: Application Metering Name
Hi Experts,
Where is the DB query that presents the report "Executable Usage" for application metering?
tnx,
Hagai