IT Management Suite

 View Only
Expand all | Collapse all

Application Metering Name

  • 1.  Application Metering Name

    Posted Jul 22, 2021 08:18 AM
    Hi Experts,
    Where is the DB query that presents the report "Executable Usage" for application metering?

    tnx,
    Hagai 


  • 2.  RE: Application Metering Name
    Best Answer

    Broadcom Employee
    Posted Jul 22, 2021 08:22 AM

    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.
    ------------------------------



  • 3.  RE: Application Metering Name

    Posted Jul 22, 2021 08:25 AM
    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


  • 4.  RE: Application Metering Name

    Posted Jul 25, 2021 10:44 AM
    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
    ------------------------------



  • 5.  RE: Application Metering Name

    Posted Jul 27, 2021 11:38 AM
    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


  • 6.  RE: Application Metering Name

    Posted Jul 28, 2021 04:28 PM
    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
    ------------------------------



  • 7.  RE: Application Metering Name

    Posted Jul 29, 2021 02:55 AM
    Thank you Shawn!, this indeed is comprehensive. I will try to good it to good use :)

    thanks,
    Hagai


  • 8.  RE: Application Metering Name

    Posted Jul 29, 2021 10:54 AM
    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
    ------------------------------



  • 9.  RE: Application Metering Name

    Posted Jul 29, 2021 12:11 PM

    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.






  • 10.  RE: Application Metering Name

    Posted Jul 29, 2021 12:41 PM

    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'

     

     

     

     

     

     

     

     

    Description: Description: Description: <a href=image002.png@01CF6F9B.4453A460">

    Ben Barker

    Systems Engineer | Baptist Health System | 3451 Beach Blvd. Jacksonville, Florida 32207

     

    904.202.0686 (w) | 904.477.2705 (c)

    Benjamin.barker@bmcjax.com

     

     






  • 11.  RE: Application Metering Name

    Posted Jul 29, 2021 12:51 PM

    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.






  • 12.  RE: Application Metering Name

    Posted Aug 02, 2021 10:53 PM
    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
    ------------------------------