How do I join a search on a field that is not unique to compare time values?

[Traer001]

Hello all,

I am struggling to find a solution for this. I have two different searches.

One shows log entries where system errors have occurred:
User Id:              Error code:               Error Time:
121                     E3002189                2021-08-27 12:01:34
249                     E1000874                2021-08-27 12:05:21
121                     E2000178                2021-08-27 12:27:09

The other search shows where the users were/are located throughout the day:
User Id:            Location:             Login Time:                                 Logout Time:
121                   P155                     2021-08-27 11:54:56           2021-08-27 12:14:19
121                   U432                     2021-08-27 12:22:16           2021-08-27 12:34:52
249                   M127                    2021-08-27 12:01:32           2021-08-27 12:35:45
249                  J362                      2021-08-27 12:38:25           2021-08-27 12:50:11

I am trying to join the two searches and then compare the times to find the location of a user at the time of an error. So I tried joining on the user id and the basically comparing the unix time of the times above to find Error Time >= Login Time and Error Time <= Logout Time, but that didn't work. How can I set up the search to accomplish this?

Thanks in advance!

[richgalloway]

Please share the query that didn't work.  Please elaborate on "that didn't work"?  What results did you get and what results did you expect?

[Traer001]

Hi @richgalloway 

This is the search using the example I provided.

| makeresults
| eval _raw="2021-08-27 12:01:34 UserId:121 ErrorCode:E3002189
2021-08-27 12:05:21 UserId:249 ErrorCode:E1000874
2021-08-27 12:27:09 UserId:121 ErrorCode:E2000178"
| multikv noheader=t
| fields _raw
| eval _time=strptime(_raw,"%Y-%m-%d %H:%M:%S")
| eval selected_at=strftime(_time, "%Y-%m-%d %H:%M:%S")
| rex field=_raw "UserId:(?<user_id>\d+)\sErrorCode:(?<error_code>\w\d+)"
| eval compare_time=_time
| join user_id [| makeresults
| eval _raw="2021-08-27 11:54:56 UserId:121 Login Loc:P155
2021-08-27 12:01:32 UserId:249 Login Loc:M127
2021-08-27 12:14:19 UserId:121 Logout Loc:P155
2021-08-27 12:22:16 UserId:121 Login Loc:U432
2021-08-27 12:34:52 UserId:121 Logout Loc:U432
2021-08-27 12:35:45 UserId:249 Logout Loc:M127
2021-08-27 12:38:25 UserId:249 Login Loc:J362
2021-08-27 12:50:11 UserId:249 Logout Loc:J362"
| multikv noheader=t
| fields _raw
| eval _time=strptime(_raw,"%Y-%m-%d %H:%M:%S")
| eval selected_at=strftime(_time, "%Y-%m-%d %H:%M:%S")
| rex field=_raw "UserId:(?<user_id>\d+)\s(?<status>\w+)\sLoc:(?<location>\w\d+)"
| where isnotnull(user_id)
| sort 0 -_time
| transaction user_id startswith="Login" endswith="Logout"
| eval login=strftime(_time, "%Y-%m-%d %H:%M:%S")
| eval logout=strftime(_time+duration, "%Y-%m-%d %H:%M:%S")
| eval start=_time
| eval end=_time+duration
| eval duration=tostring(duration, "duration")
| table user_id, location, status, start, end, duration]
| where compare_time>=start and compare_time<=end
| table user_id, error_code, location, compare_time, start, end

What I expect to get: Three rows of results, one for each error code but with the appropriate location included based on user id and the time of the error falling between the login and logout times.

What I get: One result, which although accurate, is not supposed to be the only result that shows up. There should be a result for each error code message.

[richgalloway]

Works for me

I just changed the where command

| where (compare_time>=start AND compare_time<=end)

[Traer001]

Yes @richgalloway , I got that result as well. But the thing is, with the data I have in the example there should be 3 results, not just one. I'm not sure how to get all of the results with the appropriate info.

[PickleRick]

If in doubt, append and count 😁

I have a monstrosity for you:

| makeresults
| eval _raw="User_Id Error_code Error_Time
121 E3002189 2021-08-27 12:01:34
249 E1000874 2021-08-27 12:05:21
121 E2000178 2021-08-27 12:27:09"
| multikv noheader=f | append 
[ | makeresults
| eval _raw="User_Id Location Login_Time Logout_Time
121 P155 2021-08-27 11:54:56 2021-08-27 12:14:19
121 U432 2021-08-27 12:22:16 2021-08-27 12:34:52
249 M127 2021-08-27 12:01:32 2021-08-27 12:35:45
249 J362 2021-08-27 12:38:25 2021-08-27 12:50:11"
| multikv noheader=f ] 
| fields - _raw _time linecount| eval login_period_loc=Location."%".Login_Time."%".Logout_Time| stats values(Error_Time) as errortimes values(login_period_loc) as loginperiodlocs by User_Id
| mvexpand loginperiodlocs 
| mvexpand errortimes 
| rename errortimes as Error_Time
| eval spl=split(loginperiodlocs,"%") 
| eval Loc=mvindex(spl,0) 
| eval Start=mvindex(spl,1) 
| eval End=mvindex(spl,2) 
| fields - loginperiodlocs spl
| where Error_Time>=Start AND Error_Time <= End

[Traer001]

I'm sorry, the code you provided did not end up working for me 😕

[PickleRick]

There is something wrong with data alignment in the data prepare section (before first fields command) - got mixed up in copy-pasting.
You could try splitting them manually with a regex after multikv instead of relying on splunk's tabulation.

| makeresults
| eval _raw="
121 E3002189 2021-08-27 12:01:34
249 E1000874 2021-08-27 12:05:21
121 E2000178 2021-08-27 12:27:09"
| multikv noheader=t | rex "^(?<User_Id>\S*)\s(?<Error_Code>\S*)\s(?<Error_Time>.*)"
| append 
[ | makeresults
| eval _raw="User_Id Location Login_Time Logout_Time
121 P155 2021-08-27 11:54:56 2021-08-27 12:14:19
121 U432 2021-08-27 12:22:16 2021-08-27 12:34:52
249 M127 2021-08-27 12:01:32 2021-08-27 12:35:45
249 J362 2021-08-27 12:38:25 2021-08-27 12:50:11"
| multikv noheader=f 
| rex "^(?<User_Id>.*)\s(?<Location>.*)\s(?<Login_Time>\S+\s\S+)\s(?<Logout_Time>\S+\s\S+)" ]| fields - _raw _time linecount| eval login_period_loc=Location."%".Login_Time."%".Logout_Time| stats values(Error_Time) as errortimes values(login_period_loc) as loginperiodlocs by User_Id
| mvexpand loginperiodlocs 
| mvexpand errortimes 
| rename errortimes as Error_Time
| eval spl=split(loginperiodlocs,"%") 
| eval Loc=mvindex(spl,0) 
| eval Start=mvindex(spl,1) 
| eval End=mvindex(spl,2) 
| fields - loginperiodlocs spl
| where Error_Time>=Start AND Error_Time <= End