January 27, 2010 at 11:54 am
So I want to locate instances of an odd problem we are having that is being logged.
This event occurs in the logs for some users in rapid succession,
LogID, EntryDateTime, UserID, ActionID, LogNotes
1, 2010-01-27 11:46:48.553, 5, 27, 'blah'
2, 2010-01-27 11:45:19.553, 5, 27, 'blah'
3, 2010-01-27 11:44:01.553, 5, 27, 'blah'
9, 2010-01-26 11:05:13.553, 5, 27, 'blah'
So if the entries are within say 5 minutes of each other and occur more than 3 times within 5 minutes than I want to see those entries.
I can't really think of any good way to accomplish this.
I can get the most recent incident by taking the EntryDateTime, selecing all the entries for the last 5 minutes grouped by user WHERE ActionID = 27 and that would be fine.
But I need to scan the log for the last week or so and find any occurances of this.
I could look at every since row with a correlated subquery and say if the record just before the current one is within say x minutes then count it, perhaps return an additional column on the result and flag it there, then process those into my final grouping?
I just can't think of an elegant way to handle it.
January 27, 2010 at 3:14 pm
I may not be quite understanding the question but if you loaded this into a table (leaded into EntryDateTime order) and included an identity column, you could do a select that would accomplish this fairly easily but doing something like
select t1.id, t1.logid, t.EntryDateTime, t1.UserID, t1.ActionID, t1.LogNotes
from yourtable t1 join yourtable t2
on t1.id = t2.id-1 --use -2 for 3 in a row
where abs(datediff(mi, t1.EntryDateTime, t2.EntryDatetime)) <= 5
Somethign like that should get you what you are looking for, I would say. I am not sure if this is as elegant as you are looking for but it should work.
January 28, 2010 at 8:48 am
Mike-336008 (1/27/2010)
I may not be quite understanding the question but if you loaded this into a table (leaded into EntryDateTime order) and included an identity column, you could do a select that would accomplish this fairly easily but doing something like
select t1.id, t1.logid, t.EntryDateTime, t1.UserID, t1.ActionID, t1.LogNotesfrom yourtable t1 join yourtable t2
on t1.id = t2.id-1 --use -2 for 3 in a row
where abs(datediff(mi, t1.EntryDateTime, t2.EntryDatetime)) <= 5
Somethign like that should get you what you are looking for, I would say. I am not sure if this is as elegant as you are looking for but it should work.
So you are saying basically re-order the table by EntryDatetime and then join on it by saying take the EntryDateTime of the previous record and compare it, if the difference is more than X then return that result.
That makes sense, and I suppose that really is the only way to go about that.
Just compare every row to every other row.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply