Filtering log data - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Filtering log data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-21 : 08:08:54

Kashi writes "I'm a log guy. If the status of something is constantly changing, I'd rather use a log to show each time the status changed so that I get the item's change history. But I always face a major dilemma: sorting through that information.

Suppose I log each time a person logs into their PC. The log might look something like this:

FIELDS:
strUsername
datLoginTimeStamp
strComputer
strActivity

SAMPLE DATA:
jsmith -- 05/18/2001 4:10 PM -- CPU0027 -- LOGGED IN
jdoe -- 05/18/2001 4:12 PM -- CPU0084 -- LOGGED OUT
jsmith -- 05/18/2001 5:15 PM -- CPU0027 -- LOGGED OUT
krucker -- 05/18/2001 6:27 PM -- CPU0015 -- LOGGED IN

Now, suppose I want to see what everyone's latest status is in a list, for example:

jsmith -- 05/18/2001 5:15 PM - CPU0027 -- LOGGED OUT
jdoe -- 05/18/2001 4:12 PM -- CPU0084 -- LOGGED OUT
krucker -- 05/18/2001 6:27 PM -- CPU0015 -- LOGGED IN

In other words, from a log file with tons of entries for tons of people, I want to see a list that shows each person, the last activity they recorded (logged in, or logged out) and the time of that last activity.

I have yet to find this elusive query.
This is running in an IIS 5.0 web server through a DSN."

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-21 : 08:41:28

select * from mytable a
inner join (select strUsername, max(datlogin) from mytable) b
on a.strusername = b.strusername and a.datalogin = b.datlogin

ie find the max(last) login for each person....and then find the full record that matches that sub-selection.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources