Append number by value to results

[dataisbeautiful]

Hi Splunkers

I'm looking for a way to append a column with an ID based on the value of another field.

Base search gives this

index=transactionlog sourcetype=transaction earliest=-1h@h latest=@h
| table _time Item Count

datetime=2025-03-10T08:59:59 Item=Apple Count=8
datetime=2025-03-10T08:59:45 Item=Banana Count=2
datetime=2025-03-10T08:58:39 Item=Apple Count=5
datetime=2025-03-10T08:58:25 Item=Coconut Count=1
datetime=2025-03-10T08:57:36 Item=Banana Count=2

_time	Item	Count
...	Apple	8
...	Banana	2
...	Apple	5
...	Coconut	1
...	Banana	2

I'd like something that gives this

_time	Item	Count	ID
...	Apple	8	1
...	Banana	2	2
...	Apple	5	1
...	Coconut	1	3
...	Banana	2	2

The ID is local, based only on the results. Each unique item is numbered.

I've tried streamstats count but this doesn't give the desired results.

Thanks all

[ITWhisperer · Accepted solution]

| streamstats dc(Item) as ID
| eventstats min(ID) as ID by Item

[dataisbeautiful]

@ITWhisperer That's exactly what I was after, thank you.

Thanks also @gcusello @meetmshah 

[gcusello]

Hi @dataisbeautiful ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[dataisbeautiful]

@gcuselloI've updated my post with the base search and raw data

@meetmshahthe ID is not in the raw data, it is something I am adding only at time of search

I'm not interested in maintaining an ID between times, it's going to be used in a visualisation on a deashboard.

[gcusello]

Hi @dataisbeautiful ,

in other words, you need to add a progressive number to your results, is it correct?

if this is your requirement, please try this:

index=transactionlog sourcetype=transaction earliest=-1h@h latest=@h
| eval counter=1
| accum counter AS ID
| fields - counter
| table _time Item Count ID

Ciao,

Giuseppe

[dataisbeautiful]

Hi @gcusello 

Thanks for your suggestion, I've run this

index=transactionlog sourcetype=transaction earliest=-1h@h latest=@h
| eval counter=1
| accum counter AS ID
| fields - counter
| table _time Item Count ID

It gives the output

_time	Item	Count	ID
...	Apple	8	1
...	Banana	2	2
...	Apple	5	3
...	Coconut	1	4
...	Banana	2	5

This isn't what I'm after as Apple doesn't have a single ID now.

I'd like a single ID per unique value of Item, not a row counter ID. Hope that makes sence.

[gcusello]

Hi @dataisbeautiful ,

ok, please try this:

index=transactionlog sourcetype=transaction earliest=-1h@h latest=@h
| join type=left Item 
     [ search index=transactionlog sourcetype=transaction earliest=-1h@h latest=@h
     | stats count BY Item
     | eval counter=1
     | accum counter AS ID
     | table Item ID ]
| table _time Item Count ID

Ciao.

Giuseppe

[gcusello]

Hi @dataisbeautiful ,

could you share your search and a sample of your data (both using "Add/Edit Code Sample" button not a screenshot)?

Ciao.

Giuseppe

[meetmshah]

Hello @dataisbeautiful, You can just add the other field name afte the by clause?

can you give the current search which you are using and confirm if the ID field that you want to add is in the events itself?