Attn All GURUs: Finding user IDLE or LOGIN time

[ghwin]

Was wondering if anyone knew of a way to find out the datetime a user has logged in OR been sitting idle. The environment is ASE 12.0 on AIX. The clients could be web-based or application based.

(I know that the right way to do this is using the auto time-out feature of the client applications. But for the sake of this argument, let's assume there are no such features.)

I know that in ASE 12.5, there is a loggedindatetime column from table sysprocesses. But earlier versions don't have this nice feature.

The ultimate goal here is to write a script to auto log users out after a certain amount of time. Apparently, Sybase doesn't have a nice "Idle/Timed-Out" setting (at least that I know of).

Right now, I'm leaning toward using Audit Tables but was hoping that there was a simpler way that I might have missed.

Thanks in advance.

- ghwin

 

[sybaseguru]

As you have correctly pointed out you can look at the column loggedindatetime of sysprocesses table and find out the hours/minutes gone.

select suid,spid, datediff(minute,loggedindatetime,getdate()) from sysprocesses

However, this does not mean than the user is idle or he/she is not doing anything.
A better indicator would be the field (physical_io). The increase in this field means that the user is active. Let's look at it

1> select suid,spid, datediff(minute,loggedindatetime,getdate()) AS "Minutes",physical_io from sysprocesses
2> go
suid spid Minutes physical_io
----------- ------ ----------- -----------
0 2 39 0
0 3 39 0
0 4 39 0
0 5 39 0
0 6 39 26
0 7 39 25
0 8 39 0
0 9 39 0
0 11 39 2
0 12 39 1
1 14 25 0

(11 rows affected)


For example in the above case I am logged on as suid = 1 (sa) and spid = 14. You should ignore anything with suid = 0, these are Sybase internal processes. So despite the fact that I am logged in for 25 minutes, I am not doing anything (i.e. physical_io = 0). So what I suggest is that you measure the variation in physical_io for over a time interval that you have in mind and if physical_io has not changed then you know that the process is idle so you can kill it. When you are timing make sure that the suid and spid of the process tally. So in summary record these three parameters, suid, spid and physical_io and if for a given suid AND spid physical_id2 - physical_id1 = 0 then you can kill the process.

If you want to know who is doing a lot of physical_io in the system (i.e say those with physical_io > 10), you can use the following script (create it in sybsystemprocs and run it without any parameter)

use sybsystemprocs
go
if exists(select 1 from sysobjects where type ='P' and name = 'sp__io')
begin
drop proc sp__io
end
go
create procedure sp__io @loginame varchar(30) = NULL,
@physical_io int = NULL
as
declare @low int
,@high int
,@spidlow int
,@spidhigh int
select @low = 0
,@high = 32767
,@spidlow = 0
,@spidhigh = 32767
if @physical_io is NULL
begin
select @physical_io = 0
end
if @loginame is not NULL
begin
select @low = suser_id(@loginame)
,@high = suser_id(@loginame)
if @low is NULL
begin
if @loginame like "[0-9]%"
begin
select @spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
,@low = 0
,@high = 32767
end
else
begin
print "Login %1! does not exist.", @loginame
return (1)
end
end
end
select spid
,status
,substring(suser_name(suid),1,12) loginame
,hostname
,convert(char(3), blocked) blk
,convert(char(8), isnull(time_blocked, 0)) blk_sec
,convert(char(16), program_name) program
,convert(char(8), hostprocess) host_prc
,convert(char(15), db_name(dbid)) dbname
,convert(char(16), cmd) cmd
,convert(char(8), cpu) cpu
,convert(char(8), physical_io) io
,convert(char(16), isnull(tran_name, "&quot) tran_name
from master..sysprocesses
where suid >= @low
and suid <= @high
and spid>= @spidlow
and spid <= @spidhigh
and substring(suser_name(suid),1,12) !=&quot;NULL&quot;
and physical_io > 10
order by physical_io desc
return (0)
go
grant exec on sp__io to public
go
sp_help sp__io
go


Good luck and hope this helps

 

[JeanNiBee]

I smell an FAQ.





-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[ghwin]

Hi sybaseguru,

Thanks for the tip with the first query (loggedindatetime), but that only works in ASE 12.5. I'm stuck on version 12 and older? How would I do this in the older versions???

As for using physical_io, I've noticed that this may not always work if the resulting queries are already in cache, and thus never go to disk. This might be the case with web-based apps where users often pull the same query. I guess there's always the CPU usage. Any thoughts on this?

Also, I've not used the audit table features. Any tips here?

Thanks for the detailed response.

ghwin

 

[sybaseguru]

Well, killing a user process is always a bit of a gamble. If you do not have a history table in your database where application uses for notification or application based auditing (i.e. write who changed what on which table at what time), then you need to rely on certain parameters. physical_io is a very good one and there should always be some form of user activity which impacts physical_io. However, you can also consider the column &quot;cpu&quot; as well. These two columns have been around for sometime. So my suggestion is that you just build a profile of &quot;physical_io&quot; and &quot;cpu&quot; usage over a period of time. Here is another routine to spot top cpu usages

create procedure sp__cpu @loginame varchar(30) = NULL
as
declare @low int
,@high int
,@spidlow int
,@spidhigh int
select @low = 0
,@high = 32767
,@spidlow = 0
,@spidhigh = 32767
if @loginame is not NULL
begin
select @low = suser_id(@loginame)
,@high = suser_id(@loginame)
if @low is NULL
begin
if @loginame like &quot;[0-9]%&quot;
begin
select @spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
,@low = 0
,@high = 32767
end
else
begin
print &quot;Login %1! does not exist.&quot;, @loginame
return (1)
end
end
end
select spid
,status
,substring(suser_name(suid),1,12) loginame
,hostname
,convert(char(3), blocked) blk
,convert(char(8), isnull(time_blocked, 0)) blk_sec
,convert(char(16), program_name) program
,convert(char(8), hostprocess) host_prc
,convert(char(15), db_name(dbid)) dbname
,convert(char(16), cmd) cmd
,convert(char(8), cpu) cpu
,convert(char(8), physical_io) io
,convert(char(16), isnull(tran_name, &quot;&quot) tran_name
from master..sysprocesses
where suid >= @low
and suid <= @high
and spid>= @spidlow
and spid <= @spidhigh
and substring(suser_name(suid),1,12) !=&quot;NULL&quot;
and cpu > 50
order by cpu asc
return (0)
go
grant exec on sp__cpu to public
go
sp_help sp__cpu
go

My suggestion is that you change physical_io > and cpu > values (sayset both to 0 )in these two routines, cron them and run them every 5 minutes with output to a log file, and try to work out a pattern from both. In that way you will get hang of who is doing anything or not. Once you understand your user profile, you will be able to identify the idle processes.

With regard to Sybase auditing, I would not recommend as it has certain impact on performance.

Anyway try the above you should be able to sort it out

good luck

 

[markmar1]

Physical IO may be okay, but it just does not continuously grow while the end user is logged into the system.

I can have a process that loops. Wihtin the loop, I can perform the update. The physical IO will proceed to increase as update is performed, but once the transaction has been completed, it will go back to 0, and then start to increase during the next loop thru.

You could probably come up with a scheme using auditing, but auditing can really burden the system if you have fine granularity. You can monitor when they login, and then try to determine what option would best show their activity.

But again, it probably be hit and miss...

Hope this helps...

 

[ghwin]

I forgot to thank everyone for replying to this post. It was very helpful.

As it stands, there are no perfect solutions to this problem. The Physical IO field from the system table does not work all the time. I will have to write some sort of monitor tool that checks every few minutes to see who's active and who's not.

Thanks again.

- Greg