(imported topic written by Bill.Ehardt)
Before I start trying to come up with something like this, has anyone done anything like this before? I want to check how many days in a row, I see a particular event.
I don’t mind if an event happens 20 times on a day, just that it happened at least once for a few days in a row.
Anyone have sometime similar, or an easy way to do kick this off?
(imported comment written by Bill.Ehardt)
So far using common event ID I’ve gotten unique dates…
unique values of (date (local time zone) of (time generated of it)) of records whose (event id of it mod 65536 = 7036) of system event log
It would be easy to tell if it happened for X days as well…
number of (unique values of (date (local time zone) of (time generated of it)) of records whose (event id of it mod 65536 = 7036 AND time generated of it > (now-6*Day)) of system event log) = 7
But not sure on how to get the consecutive days
(imported comment written by NoahSalzman)
Oooo… that sounds hairy. The abstract case is “find me consecutive integers in this series”, right?
2 3 4 7 10 11 12 13 14 15 20 50 51 52 53 54
And what do you want as an answer? The longest series? All the series of consecutive integers?
And, because we are dealing with dates you have to create a “0” date and count up.
Q: (date (local time zone) of now - date “01 Jan 1970”) - (date “13 Feb 2011” - date “01 Jan 1970”)
A: 1 day
Unless I’m missing something, this is probably pretty cumbersome to do in Relevance.
(imported comment written by Bill.Ehardt)
Not so much find out the longest consecutive, just find out how many days in a row it happened up until yesterday.
So if it happened on Weds, Thurs, Fri, Sat, and Sun… it would return 5. If it didn’t happen yesterday, it would return 0 (even if it happened the 3 days before that).
I guess I could always make a big ugly if then statement going back to 7 days. like if that above relevance from my previous post = true, then 7, else try 6, else try 5… etc.
Seems like the if/then/else would be the way to go, putting a max of 7 days on it.
(imported comment written by Bill.Ehardt)
Managed to come up with this… takes about 5 seconds to run. Decided to look if It didn’t happen yesterday first, so servers don’t have to go through the whole thing. Maybe it could be made a little quicker, but it works, and shows how many days back starting from yesterday it happens in a row.
Warning… its ugly haha.
if not exists records whose ((event id of it mod 65536 = 5051 OR event id of it mod 65536 = 1008) AND date (local time zone) of (time generated of it) = current date -1day) of application event log then 0 else if number of (unique values of (date (local time zone) of (time generated of it)) of records whose ((event id of it mod 65536 = 5051 OR event id of it mod 65536 = 1008)AND (date (local time zone) of (time generated of it) > current date-8Day AND date (local time zone) of (time generated of it) < current date)) of application event log) = 7 then 7 else if number of (unique values of (date (local time zone) of (time generated of it)) of records whose ((event id of it mod 65536 = 5051 OR event id of it mod 65536 = 1008)AND (date (local time zone) of (time generated of it) > current date-7Day AND date (local time zone) of (time generated of it) < current date)) of application event log) = 6 then 6 else if number of (unique values of (date (local time zone) of (time generated of it)) of records whose ((event id of it mod 65536 = 5051 OR event id of it mod 65536 = 1008)AND (date (local time zone) of (time generated of it) > current date-6Day AND date (local time zone) of (time generated of it) < current date)) of application event log) =5 then 5 else if number of (unique values of (date (local time zone) of (time generated of it)) of records whose ((event id of it mod 65536 = 5051 OR event id of it mod 65536 = 1008)AND (date (local time zone) of (time generated of it) > current date-5Day AND date (local time zone) of (time generated of it) < current date)) of application event log) =4 then 4 else if number of (unique values of (date (local time zone) of (time generated of it)) of records whose ((event id of it mod 65536 = 5051 OR event id of it mod 65536 = 1008)AND (date (local time zone) of (time generated of it) > current date-4Day AND date (local time zone) of (time generated of it) < current date)) of application event log) =3 then 3 else if number of (unique values of (date (local time zone) of (time generated of it)) of records whose ((event id of it mod 65536 = 5051 OR event id of it mod 65536 = 1008)AND (date (local time zone) of (time generated of it) > current date-3*Day AND date (local time zone) of (time generated of it) < current date)) of application event log) =2 then 2 else 1
(imported comment written by NoahSalzman)
This might work… you’ll need to add the “not exists” check at the start. I may be “off by one” somewhere but in general the idea should work. The real benefit is that it only queries the event log once.
(if it = set of (1;2;3;4;5;6;7) then “7” else if it = set of (1;2;3;4;5;6) then “6” else if it = set of (1;2;3;4;5) then “5” else if it = set of (1;2;3;4) then “4” else if it = set of (1;2;3) then “3” else if it = set of (1;2) then “2” else “1”) of ((set of (preceding texts of firsts " " of (((date (local time zone) of now) - it) as string) as integer) of (unique values of (date (local time zone) of (time generated of it)) of records whose (event id of it mod 65536 = 7036 AND time generated of it > (now-7Day) and time generated of it < (now - 1day)) of system event log)))
And, if anyone knows how to coerce a date interval into an integer then please let us know.
(imported comment written by SystemAdmin)
Couldn’t that be further reduced down to…
size of set of (preceding texts of firsts " " of (((date (local time zone) of now) - it) as string) as integer) of (unique values of (date (local time zone) of (time generated of it)) of records whose (event id of it mod 65536 = 7036 AND time generated of it > (now-7Day) and time generated of it < (now - 1day)) of system event log)
-Paul
(imported comment written by Bill.Ehardt)
Noah: that rocks… I was getting there but didn’t think of subtracting dates to see how many days ago it was. This will be a huge help.
Paul: I’m looking for consecutive days backwards from yesterday, so Monday, Sunday, Saturday isn’t the same as Tuesday, Friday, Sunday.
(imported comment written by MattBoyd)
Wow. Just… wow.
(imported comment written by SystemAdmin)
Yeah, but isn’t Noah’s code of
(if it = set of (1;2;3;4;5;6;7) then “7” else if it = set of (1;2;3;4;5;6) then “6” else if it = set of (1;2;3;4;5) then “5” else if it = set of (1;2;3;4) then “4” else if it = set of (1;2;3) then “3” else if it = set of (1;2) then “2” else “1”)
simply the same as just checking the size of the “set of” being returned? That’s why I just made it “size of set of”
Paul
(imported comment written by NoahSalzman)
You are accounting for the first test below, but not the second test:
Q: size of (set of (1;2;3)), size of (set of (2;3;4))
A: 3, 3
Q: set of (1;2;3) = set of (2;3;4)
A: False
The numbers have meaning. (1;2;3) means (one day ago, two days ago, three days ago).
(imported comment written by Bill.Ehardt)
Ahh I see what Paul is saying. I think we need change from:
if it = set of (1;2;3;4;5;6;7) then “7”
to
if it contains set of (1;2;3;4;5;6;7) then “7”
because if we get back set of (1;2;4;5;6), none of those will match
(imported comment written by NoahSalzman)
Hmm… right… we need (1;2;4;5;6) to match the “two consecutive days” case. Ugh. This is where we really could use a “for” loop.
/me drops smoke bomb and runs away from the thread
(imported comment written by Bill.Ehardt)
But…
q: set of (1;2;4;5;6;7) contains set of (1;2;3)
A: False
q: set of (1;2;4;5;6;7) contains set of (1;2)
A: True
So I think this is it… maybe…
brain hurts from this thread and question
/me looks for delete thread button to forget it ever happened
(imported comment written by NoahSalzman)
OK… it wasn’t actually that bad. Justin on the dev team pointed me in the right direction:
(if it contains 1 then if it contains 2 then if it contains 3 then if it contains 4 then if it contains 5 then if it contains 6 then if it contains 7 then 7 else 6 else 5 else 4 else 3 else 2 else 1 else nothings) of (set of (preceding texts of firsts " " of (((date (local time zone) of now) - it) as string) as integer) of (unique values of (date (local time zone) of (time generated of it)) of records whose (event id of it mod 65536 = 7036 AND time generated of it > (now-7Day) and time generated of it < (now - 1day)) of system event log))
I was making it more complicated than it needed to be by comparing sets.
(imported comment written by Bill.Ehardt)
Thanks Noah! That works! It seems like there are a couple different ways to tackle the problem.