topic Is this search achievable? in Splunk Search

Is this search achievable?

calvintkng — Fri, 01 Aug 2014 14:45:02 GMT

My date is like this,

The first and last event occur at random time every day
The number of events are also random
Each event consist of 2 values, say A and B

Is it possible for splunk to return all date that meet the following, how?

Every day, for the first 5 events, note the highest of A (say high-A), note the lowest of B (say low-B). For the 6th event onwards till the last event, once A is higher than high-A, B is not lower that low-B.

Re: Is this search achievable?

somesoni2 — Fri, 01 Aug 2014 16:20:58 GMT

So basically for every day, you need events from 6th to last entry of the day where A is higher than MaxA of first 5 entries OR (or AND confirm) where B is lower than MinB of first 5 entries?

Re: Is this search achievable?

ppablo — Fri, 01 Aug 2014 17:16:18 GMT

Hi @somesoni2

The last part is "B is NOT lower than low-B" in case you're currently working on the search.

Re: Is this search achievable?

somesoni2 — Fri, 01 Aug 2014 17:36:01 GMT

Try something like this

your base search giving _time fieldA fieldB | bucket span=1d _time | streamstats count as sno by _time | eval sno=if(sno<6,_time,null()) | eventstats max(fieldA) as MaxfieldA min(fieldB) as MinfieldB by sno| fillnull value=0 MaxfieldA MinfieldB | eventstats max(MaxfieldA) as MaxfieldA max(MinfieldB) as MinfieldB by _time | where isnull(sno) AND fieldA>MaxfieldA AND fieldB>=MinfieldB

Update 2

Assuming that you have indexed your data in Splunk and fields Date, High and Low are available (say by executing 'index=yourIndexName sourcetype=yourSourceTypeName', then the query will be like this.

index=yourIndexName sourcetype=yourSourceTypeName | streamstats count as sno by Date | eval sno=if(sno<6,Date,null())| eventstats max(High) as MaxHigh min(Low) as MinLow by sno| fillnull value=0 MaxHigh MinLow | eventstats max(MaxHigh) as MaxHigh max(MinLow) as MinLow by Date | where isnull(sno) AND High>MaxHigh AND Low>=MinLow

Re: Is this search achievable?

somesoni2 — Fri, 01 Aug 2014 17:37:48 GMT

Thanks Patric for pointing that out. I had missed it 🙂

Re: Is this search achievable?

calvintkng — Sun, 03 Aug 2014 15:55:40 GMT

Thanks somesoni2 for the quick response. However as I found the timestamp of my data messed up, I need to re-organized it first before I can know this work or not. Just give me a few days for this. Thanks.

Re: Is this search achievable?

calvintkng — Mon, 28 Sep 2020 17:15:46 GMT

I use this search string,
your base search giving _time High Low | bucket span=1d _time | streamstats count as sno by _time | eval sno=if(sno<6,_time,null()) | eventstats max(High) as MaxHigh min(Low) as MinLow by sno| fillnull value=0 MaxHigh MinLow | eventstats max(MaxHigh) as MaxHigh max(MinLow) as MinLow by _time | where isnull(sno) AND High>MaxHigh AND Low>=MinLow

but nothing return 😞

BTW, would you mind explain or point me to somewhere that I can know what this search string mean?

Re: Is this search achievable?

calvintkng — Tue, 05 Aug 2014 06:08:27 GMT

This is my data,

Date/Time; High; Low
20140701 09:00; 100; 99
20140701 09:05; 99; 98
20140701 09:15; 96; 95
20140701 09:30; 95; 94
20140701 09:44; 95; 94
20140701 10:05; 94; 93
20140701 11:11; 95; 94
20140701 12:30; 96; 95
20140701 13:33; 96; 95
20140701 14:23; 95; 94
20140701 15:12; 96; 95
20140701 16:20; 96; 95
20140701 17:30; 97; 96
20140701 18:45; 96; 95
20140701 19:22; 95; 94
20140701 20:48; 96; 94
20140701 21:38; 97; 96
20140701 22:58; 96; 95
20140701 23:58; 95; 94

Re: Is this search achievable?

calvintkng — Tue, 05 Aug 2014 06:08:51 GMT

20140702 01:11; 94; 93
20140702 01:18; 93; 92
20140702 02:48; 92; 91
20140702 03:34; 91; 90
20140702 04:03; 90; 88
20140702 04:53; 86; 82
20140702 05:33; 82; 81
20140702 06:23; 81; 80
20140702 07:43; 80; 80
20140702 08:33; 81; 80
20140702 09:11; 82; 81
20140702 10:00; 83; 82
20140702 10:25; 84; 83
20140702 11:19; 85; 84
20140702 12:22; 84; 83
20140702 13:12; 83; 82
20140702 13:17; 82; 81
20140702 14:47; 81; 79
20140702 16:33; 78; 77
20140702 17:43; 77; 76
20140702 18:58; 79; 77
20140702 20:14; 80; 78
20140702 21:34; 81; 81
20140702 22:47; 82; 81
20140702 23:42; 83; 82
20140702 23:59; 84; 83

Re: Is this search achievable?

somesoni2 — Tue, 05 Aug 2014 12:45:46 GMT

The first portion of the search "your base search giving _time High Low" is a placeholder for your actual search string which will give result with fields _time, fieldA which is High and fieldB which is Low. See the updated answer.

Re: Is this search achievable?

calvintkng — Wed, 06 Aug 2014 10:08:17 GMT

I think it is better to use my data to explain what I need to achieve. e.g. for the first 5 events of 20140701, the highest is 100, the lowest is 94, and for the rest of 20140701, there is no higher than 100, so the search should return nothing for 20140701.

Say, e.g. if an event is higher than 100 for the rest of 20140701, and then no longer below 100, the search should return 20140701.

Can this be done?

Re: Is this search achievable?

somesoni2 — Wed, 06 Aug 2014 13:11:37 GMT

Give the updated answer a try.

Re: Is this search achievable?

calvintkng — Thu, 07 Aug 2014 03:06:28 GMT

Based on my data above, the search string returns all the data above back 😞

Re: Is this search achievable?

somesoni2 — Thu, 07 Aug 2014 03:58:30 GMT

Try now. I had to covert string time to no.

Re: Is this search achievable?

calvintkng — Thu, 07 Aug 2014 06:51:17 GMT

I added the following data
20140704 09:00; 100; 99
20140704 09:05; 99; 98
20140704 09:15; 96; 95
20140704 09:30; 95; 94
20140704 09:44; 95; 94
20140704 10:05; 194; 193
20140704 11:11; 195; 194
20140704 11:30; 196; 195
20140704 13:33; 196; 195
20140704 14:23; 195; 194
20140704 15:12; 196; 195
20140704 16:20; 196; 195
20140704 17:30; 197; 196
20140704 18:45; 196; 195
20140704 19:22; 195; 194
20140704 20:48; 196; 194
20140704 21:38; 197; 196
20140704 22:58; 196; 195
20140703 23:58; 195; 194

but this time the search string return nothing

Re: Is this search achievable?

somesoni2 — Thu, 07 Aug 2014 13:03:43 GMT

Lets try to breakdown the query and see what the problem is.
1) execute this and let me know if you get a table with 3 columns- Date High Low with data in it.

index=yourIndexName sourcetype=yourSourceTypeName | table Date High Low
2)If above works, then execute this and let me know if you get table with 6 column-Date High Low sno MaxHigh MinLow, where last 3 fields have value for 1st 5 rows for a day.

index=yourIndexName sourcetype=yourSourceTypeName | streamstats count as sno by Date | eval sno=if(sno<6,Date,null())| eventstats max(High) as MaxHigh min(Low) as MinLow by sno

Re: Is this search achievable?

calvintkng — Fri, 08 Aug 2014 03:13:40 GMT

1) work and I've reform 2) to index=yourIndexName sourcetype=yourSourceTypeName | streamstats count as sno by Date | eval sno=if(sno<6,Date,null())| eventstats max(High) as MaxHigh min(Low) as MinLow by sno | table Date High Low sno MaxHigh MinLow

For the last 3 fields, the search return the same row 5 times for each day as follow,
sno=20140704 MaxHigh=197 MinLow=194
sno=20140703 MaxHigh=197 MinLow=94
sno=20140702 MaxHigh=84 MinLow=78
sno=20140701 MaxHigh=97 MinLow=94

I'll try to make up some data so can easily be seen how these value are from.

Re: Is this search achievable?

calvintkng — Fri, 08 Aug 2014 09:32:35 GMT

I noticed this is actually the MaxHigh and MinLow of the last 5 events of each day! How to make it first 5 instead of last 5?

Re: Is this search achievable?

somesoni2 — Fri, 08 Aug 2014 13:42:13 GMT

You can add a command "| reverse" before the streamstats to reverse the event list and it will pickup first 5. You can add another "| reverse" in the end if you want to final result to be shown in chronological (order before the first "|reverse" command).

Re: Is this search achievable?

calvintkng — Mon, 11 Aug 2014 02:35:25 GMT

Thanks somesoni2! Using the search string below, I can get a list of events that is higher than the max high but not lower the the min low of the first 5 events every day.