Will you help me enumerate some duplicate records returned in a search into another field?

[chris94089]

Greetings!

I have duplicate data. But that's ok. I actually don't want to just remove my dupes, I want to create another field that will enumerate them.

start with something like this:

myfield | _time
+++++++++++++
record1 | _time1
record1 | _time2
record1 | _time3
record2 | _time4

and turn into this (using eval level=?):

myfield | _time | level
+++++++++++++++++++++++++
record1 | _time1 | 1
record1 | _time2 | 2
record1 | _time3 | 3
record2 | _time4 | 1

I feel like Splunk could do something like this with tools like sortby and split by. Any thoughts? Thanks!

[renjith_nair · Accepted solution]

@chris94089 ,

Try

"your search to get myfield and _time"|streamstats count as level reset_on_change=true by myfield 

[chris94089]

Hey, renjith.nair.

Thanks for the reply. Will this work if the data is not in real time? It's structured JSON. Perhaps _time should be just date_time. sorry 😕

[macadminrohit]

Can you provide some sample data ?

[macadminrohit]

You can try something like this

 `| makeresults 
| eval data="Record1,_field1:Record1,_field2:Record3,_field3:Record4,_field4" 
| makemv delim=":" data 
| mvexpand data 
| rename data as _raw 
| kv 
| fields - _time 
| rename _raw as data 
| mvexpand data 
| makemv delim="," data 
| eval Records=mvindex(data,0) 
| eval Fields=mvindex(data,1) 
| fields - data 
| streamstats count by Records reset_on_change=true`

[renjith_nair]

yes it will work if the data is not in real time as well and does not depend on time field. So as long as long you have the field myfield it should work.

Here is a run anywhere example

| makeresults  count=10|eval myfield="abc"| streamstats count|eval myfield=if(count%3==0,"xyz","abc")|fields - count
|streamstats count as level reset_on_change=true  by myfield

In the above case, if you want a continuous count for let's say abc instead of starting from 1 in the next occurance, just remove reset_on_change=true

[chris94089]

This looks to be working for my situation, thank you so much!

Since I have lots of other fields, I needed to add a sort on the relevant fields before Splunk could start numbering things correctly. Very interesting behavior.