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