topic Re: How to separate sets of information with same field values without using transaction in Splunk Search

How to separate sets of information with same field values without using transaction

Traer001 — Mon, 21 Jun 2021 21:49:40 GMT

Hello,

I have log entries that look like this:

2021-06-21 16:36:14 Error Fix Success for issue submitted by user:14
2021-06-21 16:35:22 Error Found for Users:12,13,14
2021-06-21 16:21:11 Error Fix Success for issue submitted by user:19
2021-06-21 16:20:43 Error Found for Users:15,19,22,23
2021-06-21 16:07:38 Error Fix Success for issue submitted by user:14
2021-06-21 16:05:51 Error Found for Users:12,13,14

I want to be able to get the details (users, submitted_by user, and times) and calculate the durations of errors from when they are found to when they are fixed. I am trying to do this without using transactions. Currently my search finds the duration from 16:05:51 to 16:36:14 because the two sets of events have the same information. How can I rewrite my query (below) to get two different results for the error affecting users 12, 13 and 14?

My query:

index=INDEX host=HOST sourcetype=SOURCETYPE
| rex field=_raw "Error\sFound\sfor\sUsers:(?<users>.+)"
| rex field=_raw "Error\sFix\sSuccess\sfor\sissue\ssubmitted\sby\suser:(?<submitted_by_user>\d+)"
| where isnotnull(users) or isnotnull(submitted_by_user)
| sort 0 +_time -users
| filldown submitted_by_user users
| sort 0 -_time +users
| stats earliest(_raw) as earliest_raw latest(_raw) as latest_raw earliest(_time) as early_time latest(_time) as late_time by users submitted_by_user
| eval submitted_by_user=if(like(latest_raw, "%Found%"), "---", submitted_by_user)
| eval error_start=strftime(early_time, "%Y-%m-%d %H:%M:%S")
| eval error_end=if(submitted_by_user != "---", strftime(late_time, "%Y-%m-%d %H:%M:%S"), "---")
| eval duration=if(submitted_by_user != "---", tostring(late_time-early_time, "duration"), "---")
| eval users_involved=split(users, ",")
| eventstats count(users_involved) as user_count by earliest_raw
| fields - early_time late_time
| table users_involved, user_count, submitted_by_user, error_start, error_end, duration

Re: How to separate sets of information with same field values without using transaction

ITWhisperer — Mon, 21 Jun 2021 23:18:16 GMT

Will something like this work (it is a little tricky with such a limited data set).

Re: How to separate sets of information with same field values without using transaction

Traer001 — Tue, 22 Jun 2021 17:11:34 GMT

Thank you for the response @ITWhisperer . What you posted works for the limited data I provided. I'm sorry, I should have included more events with some different scenarios because it isn't working for some other situations. How would you rewrite it to work with a data set like this (a bit altered, so now there is an "Automatic" message that lets us know if the fix was successful or not)?

2021-06-21 16:36:14 Error Fix Success for issue submitted by user:14
2021-06-21 16:35:22 Error Found for Users:12,13,14
2021-06-21 16:22:35 Error Fix Automatic Success for issue submitted by user:34
2021-06-21 16:22:35 Error Fix Success for issue submitted by user:34
2021-06-21 16:21:11 Error Fix Automatic Success for issue submitted by user:19
2021-06-21 16:21:11 Error Fix Success for issue submitted by user:19
2021-06-21 16:20:43 Error Found for Users:15,19,22,23
2021-06-21 16:07:38 Error Fix Automatic Success for issue submitted by user:14
2021-06-21 16:07:38 Error Fix Success for issue submitted by user:14
2021-06-21 16:05:51 Error Found for Users:34,1,18
2021-06-21 16:04:38 Error Fix Automatic Failure
2021-06-21 16:04:38 Error Fix Success for issue submitted by user:14
2021-06-21 16:04:11 Error Found for Users:12,13,14

Note:

If the set of data ends in an "Automatic Failure" and the next instance with the same user is not a "Found" message, then the duration should continue. For instance, in the above, the earliest instance for users 12, 13 and 14 starts at 16:04:11 and there is a "Failure" at 16:04:38, but then another "Error Fix Success" message for user 14 occurs but it isn't following a "Found" message, so the duration for these events should be from 16:04:11 to 16:07:38.

Re: How to separate sets of information with same field values without using transaction

ITWhisperer — Tue, 22 Jun 2021 18:49:45 GMT

Assuming that the error isn't actually fixed until the Automatic Success message, try this

| makeresults | eval _raw="2021-06-21 16:36:14 Error Fix Success for issue submitted by user:14 2021-06-21 16:35:22 Error Found for Users:12,13,14 2021-06-21 16:22:35 Error Fix Automatic Success for issue submitted by user:34 2021-06-21 16:22:35 Error Fix Success for issue submitted by user:34 2021-06-21 16:21:11 Error Fix Automatic Success for issue submitted by user:19 2021-06-21 16:21:11 Error Fix Success for issue submitted by user:19 2021-06-21 16:20:43 Error Found for Users:15,19,22,23 2021-06-21 16:07:38 Error Fix Automatic Success for issue submitted by user:14 2021-06-21 16:07:38 Error Fix Success for issue submitted by user:14 2021-06-21 16:05:51 Error Found for Users:34,1,18 2021-06-21 16:04:38 Error Fix Automatic Failure 2021-06-21 16:04:38 Error Fix Success for issue submitted by user:14 2021-06-21 16:04:11 Error Found for Users:12,13,14" | multikv noheader=t | fields _raw | eval _time=strptime(_raw,"%Y-%m-%d %H:%M:%S") | rex field=_raw "Error\sFound\sfor\sUsers:(?<users>.+)" | rex field=_raw "Error\sFix\sAutomatic\sSuccess\sfor\sissue\ssubmitted\sby\suser:(?<submitted_by_user>\d+)" | where isnotnull(users) or isnotnull(submitted_by_user) | eval error_end=if(isnull(submitted_by_user),null(),_time) | eval error_start=if(isnull(users),null(),_time) | sort 0 _time | eval user=split(users,",") | mvexpand user | eval user=coalesce(submitted_by_user,user) | streamstats latest(error_start) as error_start latest(users) as users by user | fieldformat error_end=strftime(error_end,"%Y-%m-%d %H:%M:%S") | fieldformat error_start=strftime(error_start,"%Y-%m-%d %H:%M:%S") | where isnotnull(error_end) | eval duration=error_end-error_start | eval users_involved=split(users, ",") | eval user_count=mvcount(users_involved) | table users_involved, user_count, submitted_by_user, error_start, error_end, duration