Thanks Garth and Nick,
I had zero'd in on below. I still need to validate more on this one, but
structurally I got what I want. Data validation on what I am joining views
correct or left outer etc still needs to be done.
select distinct MUR.fullname 'User', sys.name0 'Computer', SUD.productname,
SUD.filedescription, sud.filename, max(MUS.lastusage) 'LastUsed'
,case
when datediff (day, max(MUS.lastusage), GETDATE() ) > 60 then 'Inactive'
when datediff (day, max(MUS.lastusage), GETDATE() ) <= 60 and
min(mus.usagetime) < 1800 then 'Inactive'
else 'Active' end ActiveStatus
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')
group by MUR.fullname, sys.name0, SUD.productname, SUD.filedescription,
sud.filename
Thank, Vasu
From: [email protected] [mailto:[email protected]] On
Behalf Of Nick
Sent: Tuesday, September 29, 2015 6:17 PM
To: [email protected]
Subject: RE: [mssms] FineTune SW Metering Query
Thanks Garth - I had meant to use the lastconsoleuse0 column, not the
timestamp. Doh
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(lastconsoleuse0) [latestuse], resourceid from
v_gs_system_console_user group by resourceid
) as latestuse on latestuse.resourceid = sys.resourceid
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Garth Jones
Sent: Monday, September 28, 2015 5:17 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] FineTune SW Metering Query
Keep in mind that Timestamp Column is NOT when the event happen, it is when the
data is enter into the database.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Nick
Sent: Monday, September 28, 2015 1:52 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] FineTune SW Metering Query
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]>
[mailto:[email protected]] On Behalf Of Miriyala, Vasu
Sent: Monday, September 28, 2015 6:54 AM
To: [email protected]<mailto:[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:
[cid:[email protected]]
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.
