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]] On Behalf Of Nick Sent: Monday, September 28, 2015 1:52 PM To: [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.
