Software Metering

Question

I am having an issue with creating a custom report and collection based on software metering for firefox. since firefox doesnt auto update unless its being used we are looking to remove firefox from systems if its not used. so i am tring to remove it from systems that havent used it in more then say 200 days to start. when i run the sql query it gives me different results for the collection i am running it against depending on the days i pick.

select
RV.Netbios_Name0,
RV.User_Name0,
RV.Resource_Domain_OR_Workgr0,
OS.Caption0,
—MUS.LastUsage,
max(MUS.LastUsage),
datediff(dd,MUS.LastUsage,getdate())

from
v_R_SYSTEM RV
inner join v_MonthlyUsageSummary MUS on RV.ResourceID = MUS.ResourceID
inner join v_MeteredFiles MF ON MUS.FileID = MF.MeteredFileID
JOIN v_FullCollectionMembership ON rv.ResourceID = v_FullCollectionMembership.ResourceID
left outer join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID

WHERE

MF.RuleID = 16777418 and rv.Netbios_Name0 = ‘xxxx’

Group by RV.Netbios_Name0, RV.User_Name0, RV.Resource_Domain_OR_Workgr0, OS.Caption0, MUS.LastUsage

Capture - HTMD Forum - Welcome to the world of Device Management! This is community build by Device Management Admins for Device Management Admins❤️ Ask your questions!! We are here to help you! - Software Metering

as you see i get multiple dates. what would be the best way to truly get the systems that havent used software in more then x amount of days.

Answers ( 8 )

    0
    2020-05-29T16:30:43+05:30

    This is great share …thnx

    0
    2020-05-28T20:29:39+05:30

    Do you mind sharing it here so that it will helpful for the community. thnx

      1
      2020-05-29T14:47:05+05:30

      no its all mine lol.

      select Distinct
      sys.Name0,
      sys.User_Name0,
      sys.Resource_Domain_OR_Workgr0,
      OS.Caption0,
      max(MUS.LastUsage) as ‘Last Usage’,
      DateDiff(dd,max(mus.lastusage),getdate()) as [Days Last Used],

      [Top Console User] = CASE
      when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = ‘-1’)
      then ‘Unknown’
      Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
      End,

      v_R_User.Full_User_Name0 AS [Full User Name],
      v_R_User.Department0 AS [Department],
      v_R_User.Title0 AS [Title],
      v_R_User.mail0 AS [Email Address],
      v_R_User.physicalDeliveryOfficeNam0 AS [Office]

      from
      v_R_SYSTEM sys
      join v_MonthlyUsageSummary MUS on sys.ResourceID = MUS.ResourceID
      join v_MeteredFiles MF ON MUS.FileID = MF.MeteredFileID
      join dbo.v_GS_OPERATING_SYSTEM OS on sys.ResourceID = OS.ResourceID
      join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = sys.ResourceID)
      JOIN v_R_User ON v_R_User.Unique_User_Name0 = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
      JOIN v_FullCollectionMembership ON sys.ResourceID = v_FullCollectionMembership.ResourceID
      WHERE
      v_FullCollectionMembership.CollectionID = @CollID AND MF.RuleID = @SoftwareMeteringAPP
      — Mf.RuleID = 16777418 and v_FullCollectionMembership.CollectionID = ‘hib000f3’

      Group by sys.Name0, sys.User_Name0, sys.Resource_Domain_OR_Workgr0, OS.Caption0, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0, v_R_User.Full_User_Name0, v_R_User.Department0,v_R_User.Title0,v_R_User.mail0,v_R_User.physicalDeliveryOfficeNam0
      having datediff(dd,max(mus.lastusage),getdate()) >@numdays
      Order by sys.Name0

    0
    2020-05-28T18:55:47+05:30

    I got you ..I don’t know much about that ..apart from playing around with excel 🙁 may be there are SQL experts around here could help you better

    0
    2020-05-28T18:42:16+05:30

    if i use that i have the same issue i cant do a datediff. i think my logic is wrong but not to sure how to fix it.

    0
    2020-05-28T18:20:52+05:30

    I think it’s better to use start time and end time !!

    And we might need to make some logic to work around this

    https://snipboard.io/4tlL7r.jpg

      1
      2020-05-28T20:25:56+05:30

      I found out the issue i spoke to one of our sql guru’s and i had to add in a having clause and now my report is giving me the data i want but i have to figure out how to get the collection to give me the right data.

Leave an answer

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