Find a last used date

[cajos]

Hi I'm an system admin and not a SQL expert. On our Citrix environment I want to find the last date an application is used. In the Citrix SQL database I have found the following tables.

APP
AppName APPID
Acrobat 1
Word 2
Outlook 3
,,

User
UserName UserID
Peter 1
Frank 2
Jane 3
,,

Session
AppID UserID Date
1 2 2002-1-1 "time"
1 2 2002-2-1 "time"
2 1 2003-3-2
1 1 2003-5-6
,,

I'm having trouble translating the ID to Names and find per user the last time they used an application.
Actualy I want to find all users that did not use an application for the last 60 day's and revoke the licence, save some money.

kind regards

Cajos@xs4all.nl

 

[meydz]

try this query.

select *
from user
where userID not in(select user_ID from session where datediff(d, date, getdate()) <= 60)
hope this will help!
meydz

 

[cajos]

Works fine I can find all last dates and user names. Only thing missing is the application name.

 

[SQLBill]

Try:

select u.username, a.appname, s.[date]
from user u
join session s
on u.userid = s.userid
join app a
on a.appid = s.appid
where userID not in(select user_ID from session where datediff(d, [date], getdate()) <= 60)

-SQLBill

 

[cajos]

SQLBILL

Thanks, but your script is generating Error in the fist line. could it have something to do with the aliases u use.

 

[cajos]

Dear Repliers,

After cracking my brain on your statement. I have to conclude that it is not right. I will find all dates before 60 day's.That does not exclude the dates after 60 day's. I Want to find the last DATE a USER used an APPLICATION. And IF this date is older then &quot;let's say&quot; 60 day's I want to know what Application,USER DATE but only the last date not all previous. Currently (5% load) I have got 15000 Records in the SESSION table, so you can imagine that there are a lot of records I don't want to know about.

b.t.w: I appriciate your effords

 

[zhavic]

Try to do it this way:

SELECT u.UserName,
a.AppName,
MAX( s.[date] ) AS last_use
FROM [user] u
JOIN session s ON u.userid = s.userid
JOIN app a ON a.appid = s.appid
GROUP BY u.UserName, a.AppName
HAVING DATEDIFF( d, MAX( s.[date] ), GETDATE()) > 60

May work, if AppName for all AppID is different.
If no ( some applications has different AppID but the same AppName ), you must change GROUP BY clause to:
GROUP BY u.UserName, a.AppID, a.AppName


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[cajos]

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'userid'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'userid'.

After rebuild
SELECT u.UserName,
a.pk_appnameid,
MAX( s.[SESSIONEND] ) AS last_use
FROM [LU_USER] u
JOIN SDB_SESSION s ON u.userid = s.userid
JOIN LU_APPNAME a ON a.PK_APPNAMEID = s.FK_APPNAMEID
GROUP BY u.UserName, a.AppName
HAVING DATEDIFF( d, MAX( s.[SESSIONEND] ), GETDATE()) > 60

Appname turns up blue in query A is this a reserved word?

 

[zhavic]

Yes. 'AppName' is reserved word, use the brackets
A.[AppName]

Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.