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.
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
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
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 )
This is great share …thnx
Do you mind sharing it here so that it will helpful for the community. thnx
no its all mine lol.
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’)
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]
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
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
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
anoop i got the report giving me the right data now to get that to happen for a collection is another story.
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.
I think it’s better to use start time and end time !!
And we might need to make some logic to work around this
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.