Splunk Search

How to add a new column with serial number based on the uniqueness of the existing column?

spkriyaz
Path Finder

I have a column called "message" which has duplicate records in it. I want to create a new column named "serial" beside it to have a serial no. for the message.

For example:

If the first and second-row message columns are the same then the new column "serial" should have 1 and 1 in it. If not identical then it should have the serial number as 1 and 2, and continue the serial no. for other records based on the message column uniqueness.

Example:

alt text

Since my first two records has same value in message as arran I had the value as 1 for it wherever it appears in the table. If you see the message "flex" appears in different row but I consider the serial number as 2 wherever it appears.

Labels (1)
0 Karma

rnowitzki
Builder

Streamstats if your friend. This should do it:

| streamstats count as id
| sort message
| streamstats dc(message) as "serial"
| sort id
| fields - id

You could remove the first streamstats command, sort id and fields - id if the original order does not matter.
Use sort 0 id if you expect to have more than 10.000 rows.

--
Karma and/or Solution tagging appreciated.
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!