List unique values from splunk events

Question

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

warren · Accepted Answer · 2023-07-27T14:09:15.123

1

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

edited Jul 27 '23 at 14:09

answered Jul 27 '23 at 12:29

warren

32,620
21
85
124

Keep in mind that `stats` is an aggregating command. While it's a good solution in this case, it may not be appropriate in all scenarios. OTOH, `dedup` removes duplicates without aggregation or transformation and is not that inefficient. – RichG Jul 27 '23 at 14:33
@RichG - `dedup` is *incredibly* inefficient in the overwhelming majority of cases (ie >96% of the time). Using `dedup` *after* `stats` or some other function *can* make sense ... but it is almost never the "right" answer :) – warren Jul 27 '23 at 15:49
@warren I created query as like this `index=myIndex container_name="abc-mno-pqr" "status code :: 50*" | rex field=_raw "code\D+(?\d+)" | rex field=_raw "^\[(?[^\]]+)" | stats latest(status) as Status-Code by transactionId` but it is giving value of status as `5` only not complete code like `503` or `504` – Sat Aug 07 '23 at 14:05
@Sat - is it the *count* that is `5`, or the *status* that is `5`? – warren Aug 07 '23 at 15:13
@warren It's not `count`, `status` is giving `5` instead of `503` or `504` – Sat Aug 09 '23 at 11:21
@Sat - are you sure your events only look like what you initially shared? – warren Aug 09 '23 at 16:14
Yes @warren events look like what I shared initially in the question. From your answere splunk query I am getting data in table format with transactioIDs and StatusCodes but the problem is StatusCodes are not getting printed completely. I mean for `501` or `503` or `500` statuses getting only `5` – Sat Aug 11 '23 at 10:25
1

@Sat - per the regex, and your initial search of `"status code :: 50*"`, the newly-created `status` field is going have at least two digits. Unless there is *another* sequence of `code` that is being seen prior to the sequence `status code :: 50`. You can try extending the regex from `"code\D+(?\d+)"` to `"status code\D+(?\d+)"` – warren Aug 11 '23 at 12:22

score 0 · Answer 2 · answered Jul 27 '23 at 12:28

0

The assertion "displayed because there is difference in the timestamp i.e 1 second" is incorrect. The events are displayed because they were sent to Splunk and nothing in the query removes them.

To see only unique events, use the dedup command to remove duplicates.

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

This assumes the transactionId field is extracted automatically.

answered Jul 27 '23 at 12:28

RichG

9,063
2
18
29

`dedup` is somewhat unpredictable (and inefficient) - it should *almost* always be avoided (especially early in a search) – warren Jul 27 '23 at 12:31

List unique values from splunk events

2 Answers2