topic Re: First event in Splunk Search

First event

tahasefiani — Fri, 06 Mar 2020 08:31:22 GMT

Hello, this is my query

| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-26") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") and STEP=="Click"
| bucket _time span=1d
|table _time,MESSAGE
|where MESSAGE = "337668c2-162c-4f4f-bda9-92f7816f2752" OR MESSAGE = "46095117-4dcb-4ebc-9906-8c23f1a1a26b" OR MESSAGE = "60eb62a4-c54a-4fc0-9aaa-17726ff62929" OR MESSAGE = "8b5e055c-17ab-4135-8b90-1fbc65032792"

And this is the result

What i want is only the lines on yellow:
If I have a message on the 26th, 27th and 28th I must have that of 26

Re: First event

to4kawa — Fri, 06 Mar 2020 10:41:46 GMT

| loadjob savedsearch="myquery"
 | where (strftime(_time, "%Y-%m-%d") >= "2020-02-26") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") and STEP=="Click"
 | bucket _time span=1d
 |stats min(_time) as _time by MESSAGE
 |where MESSAGE = "337668c2-162c-4f4f-bda9-92f7816f2752" OR MESSAGE = "46095117-4dcb-4ebc-9906-8c23f1a1a26b" OR MESSAGE = "60eb62a4-c54a-4fc0-9aaa-17726ff62929" OR MESSAGE = "8b5e055c-17ab-4135-8b90-1fbc65032792"

Re: First event

manjunathmeti — Fri, 06 Mar 2020 10:45:36 GMT

hi @tahasefiani,

Try this:

| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-26") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") and STEP=="Click"
| bucket _time span=1d
| stats earliest(_time) as time by MESSAGE 
| eval time=strftime(time, "%Y-%m-%d") 
| where IN(MESSAGE, "337668c2-162c-4f4f-bda9-92f7816f2752", "46095117-4dcb-4ebc-9906-8c23f1a1a26b", "60eb62a4-c54a-4fc0-9aaa-17726ff62929", "8b5e055c-17ab-4135-8b90-1fbc65032792")

Re: First event

to4kawa — Fri, 06 Mar 2020 11:55:36 GMT

Hi, @manjunathmeti
I like min() to epoch. your IN usage is cool.

Re: First event

tahasefiani — Fri, 06 Mar 2020 13:21:44 GMT

I have an old version,so i can't use IN

Re: First event

tahasefiani — Fri, 06 Mar 2020 13:29:34 GMT

@to4kawa @manjunathmeti for the two solution, i can't use after a timechart?

dc(ID_MESSAGE) by _time

OR

timechart dc(ID_MESSAGE)

Re: First event

wmyersas — Fri, 06 Mar 2020 14:02:27 GMT

How old? IN has worked since at least 6.3

Re: First event

to4kawa — Fri, 06 Mar 2020 14:23:30 GMT

timechart ?

....
| bucket _time span=1d
| table _time,MESSAGE

is same of timechart result.

but where does dc() come from?
your question First event doesn't need dc() and timechart .

Re: First event

tahasefiani — Fri, 06 Mar 2020 14:49:26 GMT

the purpose of the query, at the base is to calculate the messages per day, and count the message only on the first day.This why i did this query.

Now, i have 3 ID_MESSAGE for 27/02 and one for 28/02

This is what i want :

27/02 => 3
28/02 => 1

| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-26") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") and STEP=="Click"
| bucket _time span=1d
| stats earliest(_time) as _time by ID_MESSAGE
| eval _time=strftime(_time, "%Y-%m-%d") 
|timechart count(ID_MESSAGE)

Re: First event

woodcock — Fri, 06 Mar 2020 15:48:57 GMT

Try this:

| loadjob savedsearch="myquery"
| rename COMMENT AS "Use timepicker to filter dates"
| addinfo
| rename COMMENT AS "First problem here: you used 'and' instead of 'AND'"
| where (_time >= info_min_time) AND (_time <= info_max_time)  AND STEP=="Click"
| bucket _time span=1d
| sort 0 - _time
| streamstats count AS _serial BY MESSAGE _time
| where _serial="1"

Or maybe even this:

| loadjob savedsearch="myquery"
| rename COMMENT AS "Use timepicker to filter dates"
| addinfo
| rename COMMENT AS "First problem here: you used 'and' instead of 'AND'"
| where (_time >= info_min_time) AND (_time <= info_max_time)  AND STEP=="Click"
| timechart span=1d first(_time) AS time BY MESSAGE

Re: First event

tahasefiani — Wed, 30 Sep 2020 04:31:46 GMT

this query works for me

 | loadjob savedsearch="myquery"
 | where (strftime(_time, "%Y-%m-%d") >= "2020-02-26") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") and STEP=="Click"
 | bucket _time span=1d
 | stats earliest(_time) as _time by ID_MESSAGE
 | eval _time=strftime(_time, "%Y-%m-%d")

And this is the result :

_time ID_MESSAGE
27/02 YHDD
27/02 MFJIO
27/02 LKCFD
28/02 LMDFF

Now i wanna count ID_MESSAGE by _time to have this :

_time count(ID_MESSAGE)
27/02 3
28/02 1