Generate lab statistics from data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generate lab statistics from data

Author

Topic

jpreston02
Starting Member

3 Posts

Posted - 2007-10-11 : 10:25:59

Hello,

I'm a network admin who understands sql but have no reason to write it everyday so I have no idea how to approach this problem.

I am trying to generate usage statistics of our computer labs. I have written a few scripts that populate a sql 2005 database with the following information:

[datetime] [username] [computer] [event]
2007-10-10 15:25:03 tom earth logon
2007-10-10 15:39:09 john mars logon
2007-10-10 15:41:13 dave pluto logoff
2007-10-10 15:47:29 john mars logoff
2007-10-10 15:59:48 tom earth logoff

Each time a user logs on or logs off one of our lab computers the information is inserted into this database.

It seems that was the easy part. Lacking any local sql experts, I'm pondering where to go from here to generate usage statistics.

What I'm thinking is a SQL query (which I don't know how I'd write) that would calculate the session time and insert it into a new table. A session is defined as the time between a logon and logoff where the username and computer match.

The new table would look something like this:

[user] | [computer] | [sessionTime] | [day]
tom | earth | 00:34:45 | 2007-10-10
john | mars | 00:08:20 | 2007-10-10

The logic of the query finds the first logon event and matches it with the first logoff event in which the username and computer match. It then subtracts the logon datetime from the logoff to calculate session time.

Is this a reasonable approach? Is the query easy to write?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 10:40:36

[code]-- Prepare sample data
DECLARE @Sample TABLE (dt DATETIME, UserName VARCHAR(20), Computer VARCHAR(20), [Event] VARCHAR(20))

INSERT @Sample
SELECT '2007-10-10 15:25:03', 'tom', 'earth', 'logon' UNION ALL
SELECT '2007-10-10 15:39:09', 'john', 'mars', 'logon' UNION ALL
SELECT '2007-10-10 15:41:13', 'dave', 'pluto', 'logoff' UNION ALL
SELECT '2007-10-10 15:47:29', 'john', 'mars', 'logoff' UNION ALL
SELECT '2007-10-10 15:59:48', 'tom', 'earth', 'logoff'

-- Show the expected output
SELECT UserName,
Computer,
CONVERT(CHAR(8), DATEADD(SECOND, Logoff - Logon, 0), 108) AS SessionTime,
DATEADD(DAY, 0, dt) AS [Day]
FROM (
SELECT UserName,
Computer,
SUM(CASE WHEN [Event] = 'logon' THEN DATEDIFF(SECOND, '20070101', dt) ELSE 0 END) AS Logon,
SUM(CASE WHEN [Event] = 'logoff' THEN DATEDIFF(SECOND, '20070101', dt) ELSE 0 END) AS Logoff,
DATEDIFF(DAY, 0, dt) AS dt
FROM @Sample
GROUP BY UserName,
Computer,
DATEDIFF(DAY, 0, dt)
) AS q
WHERE Logon > 0
AND Logoff > 0
ORDER BY DATEADD(DAY, 0, dt),
UserName,
Computer[/code]

E 12°55'05.25"
N 56°04'39.16"

jpreston02
Starting Member

3 Posts

Posted - 2007-10-11 : 13:03:43

Thank you for the response Peso, I really appreciate it. How do you prepare the new table (in your section titled --prepare sample data) if you don't know how many records you need to "UNION ALL"

Below the line "INSERT @Sample" I tried SELECT * UNION ALL, but it didn't like that.

jpreston02
Starting Member

3 Posts

Posted - 2007-10-11 : 16:05:55

Nevermind, I get it. You created a table variable and used the data I provided to return the results. I see that I should have prepared the data in this fashion (according to the new user faq). When I copied your entire snippet of code into the query editor I got exactly what I was looking for. Now it's time for me to brush up on my SQL skills to make this work for the table that is being generated in realtime.

I'll play around some (since obviously I have a lot of work to do to understand it all) and if I have anymore questions, I'll be back. Thanks again Peso for your extremely helpful post.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 01:50:21

You're welcome.
What I did, was some CROSSTAB/PIVOTING operation to get all LOGONS in one column and all LOGOFFS in one column.
The rest is pure math!

SUM(Logoff - Logon) is the same thing as SUM(Logoff) - SUM(Logon).
Try for yourself;

Logoff Logon Diff
     8     6    2
    10     4    6
     2     7   -5
------ ----- ----
    20    17    3

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources