Unfortunately we don't have the metering turned on for me to put something
together for you. What you need to do is build a subselect and join to that
like you would a table.
One example I can show you.. If you wanted the last time a system was used.
"select max(timestamp), resourceid from v_gs_system_console_user group by
resourceid"
If you wanted to join that to some other data, you can join like this:
select sys.netbios_name0, latestuse from v_r_system sys
join (
select max(timestamp) [latestuse], resourceid from
v_gs_system_console_user group by resourceid
) as latestuse on latestuse.resourceid = sys.resourceid
The Case statement added:
select sys.netbios_name0, latestuse,
case
when datediff(d,latestuse,getdate()) > 60 OR datediff(d,latestuse,getdate())
< 30 then 'InactiveUse'
else 'Active'
end as 'UsageType'
from v_r_system sys
join (
select max(timestamp) [latestuse], resourceid from
v_gs_system_console_user group by resourceid
) as latestuse on latestuse.resourceid = sys.resourceid
From: [email protected] [mailto:[email protected]]
On Behalf Of Miriyala, Vasu
Sent: Monday, September 28, 2015 6:54 AM
To: [email protected]
Subject: [mssms] FineTune SW Metering Query
Hi Champs,
I have below query that gives me data of SW metering as per my needs.
Looking for help to accomplish two changes to it.
1) Display lastusage time of oldest per user/app, meaning when user
used application laaaast time, excluding all recent ones. Once after
achieving this,
2) Second need is, add one more column that display result based on a
case statement if
a. Lastusagetime is GE 60 days then InactiveUse
b. Lastusagetime is LE 60 days but Usagetime is LE 30 mins then
InactiveUse else ActiveUsage
Current Query:
select MUR.fullname 'User', sys.name0 'Computer', SUD.productname,
SUD.filedescription, sud.filename, MUS.lastusage, mus.usagetime
from v_MonthlyUsageSummary MUS
join v_MeteredUser MUR on MUR.metereduserid=MUS.metereduserid
join v_r_system sys on sys.resourceid=MUS.resourceid
join v_GS_SoftwareUsageData SUD on SUD.FileID=MUS.FileID
where MUR.fullname in ('corp\vmiriyal')
Result of current/above query:
This message contains information that may be privileged or confidential and
is the property of the Capgemini Group. It is intended only for the person
to whom it is addressed. If you are not the intended recipient, you are not
authorized to read, print, retain, copy, disseminate, distribute, or use
this message or any part thereof. If you receive this message in error,
please notify the sender immediately and delete all copies of this message.
