topic Re: How to get the first and last number of consecutive integers by search? in Splunk Search

How to get the first and last number of consecutive integers by search?

Oracle — Mon, 11 Mar 2019 03:38:34 GMT

Hello Splunkers,

Need your help on this.

This is my query for testing:

| fields id 
| sort id 
| delta id AS deltaid 
| eval consecutive=if(deltaid=1,"consecutive","nonconsecutive")

id
1
3
4
5
10
13
14
15

Output in a new field should be like this:
1
3, 5
10
13, 15

Re: How to get the first and last number of consecutive integers by search?

renjith_nair — Mon, 11 Mar 2019 05:38:32 GMT

@Oracle ,

Try this

| fields id
| sort id
| delta id AS deltaid|eval flag=if(deltaid==1,0,1)| accum flag as group
| eventstats min(id) as min , max(id) as max by group
| eval result=if(min==max,min,min.",".max)|fields id,result

If you do not want result in all rows, you can replace eventstats with stats

|delta id AS deltaid|eval flag=if(deltaid==1,0,1)| accum flag as group
|stats  min(id) as min , max(id) as max,values(id) as id by group
|eval result=if(min==max,min,min.",".max)|fields id,result

Re: How to get the first and last number of consecutive integers by search?

Oracle — Mon, 11 Mar 2019 05:47:43 GMT

Hello @renjith.nair

Great, your search provided is working fine! Thank you 🙂

Re: How to get the first and last number of consecutive integers by search?

woodcock — Mon, 11 Mar 2019 23:23:32 GMT

Here is another much more complicated solution:

| makeresults 
| eval raw="id=1 id=3 id=4 id=5 id=10 id=13 id=14 id=15" 
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| eval id=printf("%06d",id)
| stats list(id) AS ids
| nomv ids
| map search="
| makeresults
| eval ids=$ids$
| eval id=$ids$
| makemv id
| chart count BY ids id
| fields - ids
| eval _answer=-1, _prev_val=-999
| foreach * [
   eval _last_answer_digit=replace(_answer, \"^.*?(\d+)$\", \"\1\"),
        <<FIELD>> = \"<<FIELD>>\",
        _answer = case(
           (_answer==-1),                     <<FIELD>>,
           (_prev_val + 1==<<FIELD>>),        _answer,
           (_last_answer_digit == _prev_val), _answer . \",<<FIELD>>\",
           true(),                            _answer . \"-\" . _prev_val . \",<<FIELD>>\"),
       _prev_prev_val = _prev_val,
       _prev_val = \"<<FIELD>>\" ]"
| eval _answer = _answer . if((_prev_val==(_prev_prev_val + 1)), "-", ",") . _prev_val
| table _answer
| rename _answer AS answer
| rex field=answer mode=sed "s/,0+/,/g s/-0+/-/g"

Re: How to get the first and last number of consecutive integers by search?

woodcock — Mon, 11 Mar 2019 23:25:20 GMT

Wow; very, Very, VERY nicely done! Take a look at my brute-force approach!!!