Splunk Search

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

Oracle
Explorer

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
0 Karma
1 Solution

renjith_nair
Legend

@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
---
What goes around comes around. If it helps, hit it with Karma :slightly_smiling_face:

View solution in original post

woodcock
Esteemed Legend

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"
0 Karma

renjith_nair
Legend

@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
---
What goes around comes around. If it helps, hit it with Karma :slightly_smiling_face:

woodcock
Esteemed Legend

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

0 Karma

Oracle
Explorer

Hello @renjith.nair

Great, your search provided is working fine! Thank you :slightly_smiling_face:

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...