Search code examples
splunksplunk-querysplunk-calculation

List unique values from splunk events

index=myIndex container_name="abc-mno-pqr" "status code :: 50*"

For this splunk query I am getting events like below

[123-456-789-098] | 2023-07-26 12:05:31:245 [application-1] INFO com.example.event.SampleClasss - status code :: 500

[321-564-986-197] | 2023-07-26 13:04:38:287 [application-1] INFO com.example.event.SampleClasss - status code :: 503

[655-256-278-865] | 2023-07-26 13:05:42:245 [application-1] INFO com.example.event.SampleClasss - status code :: 503

[457-234-856-528] | 2023-07-26 14:08:23:123[application-1] INFO com.example.event.SampleClasss - status code :: 504

[457-234-856-528] | 2023-07-26 14:08:24:123[application-1] INFO com.example.event.SampleClasss - status code :: 504

In the above events last one is duplicate transactionId but displayed because there is difference in the timestamp i.e 1 second

I need to display unique Ids with corresponding status codes like below.

transactioId Status-Code
123-456-789-098 500
321-564-986-197 503
655-256-278-865 503
457-234-856-528 504
Solution

  • stats will be your friend here:

    index=myIndex container_name="abc-mno-pqr" "status code :: 50*"
| stats latest(status) as Status-Code by transactionId

    If the fields transactionId and status are not yet extracted, you'll need to pull them out

    A way to do this at search time is with rex:

    | rex field=_raw "code\D+(?<status>\d+)"
| rex field=_raw "^\[(?<transactionId>[^\]]+)"

    regex101 verifications: https://regex101.com/r/JDgzya/1 && https://regex101.com/r/O5qTJ9/1

    If you want to see all statuses for each transactionId, do this instead:

    index=myIndex container_name="abc-mno-pqr" "status code :: 50*"
| stats count by transactionId status
| rename status as Status-Code

    and with timestamps:

    index=myIndex container_name="abc-mno-pqr" "status code :: 50*"
| stats count by transactionId status _time
| rename status as Status-Code