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