topic Append number by value to results in Splunk Search

Append number by value to results

dataisbeautiful — Mon, 10 Mar 2025 09:37:30 GMT

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

Re: Append number by value to results

meetmshah — Mon, 10 Mar 2025 09:13:53 GMT

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?

Re: Append number by value to results

gcusello — Mon, 10 Mar 2025 09:25:08 GMT

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

Re: Append number by value to results

dataisbeautiful — Mon, 10 Mar 2025 09:38:48 GMT

@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.

Re: Append number by value to results

gcusello — Mon, 10 Mar 2025 09:43:16 GMT

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

Re: Append number by value to results

dataisbeautiful — Mon, 10 Mar 2025 09:52:27 GMT

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.

Re: Append number by value to results

gcusello — Mon, 10 Mar 2025 10:14:54 GMT

Hi @dataisbeautiful ,

ok, please try this:

Ciao.

Giuseppe

Re: Append number by value to results

ITWhisperer — Mon, 10 Mar 2025 11:06:05 GMT

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

Re: Append number by value to results

dataisbeautiful — Mon, 10 Mar 2025 14:37:13 GMT

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

Thanks also @gcusello @meetmshah

Re: Append number by value to results

gcusello — Mon, 10 Mar 2025 15:01:49 GMT

Hi @dataisbeautiful ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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