How do I use Splunk to combine two searches with a subquery with no common field?

[splunksplunk232]

HI all,

I have a log file that looks like that:

10-12-2018(8:50) INFO system.logIn - log in: yoni
10-12-2018(8:50) ERROR system.auth - fail in log in process
10-12-2018(8:51) INFO system.login - log in: bob
10-12-2018(8:52) INFO system.login - log in: john
10-12-2018(8:54) ERROR bi.report - error in creating report production
10-12-2018(8:56) INFO bi.validation - user inserted valid combination 
10-12-2018(8:58) ERROR bi.report - error in creating report salary

I would like to see a table that looks like:

At each time, only one user can be connected. And a user can throw zero, one or more than one errors while he logs in.

I tried to write the query:

index="some_index"  log_type="ERROR" | appendcols [search * index="some_index" "log in" | where _time < error_time | sort _time | stats last(_raw)] | rename _time as error_time | table log_user _time error_code error_time

But it gives me the error: "Error in 'appendcols' command: You can only use appendcols after a reporting command (such as stats, chart, or timechart)."

Thank you in advance

[whrg · Accepted solution]

The transaction command could also work here:

index="some_index"
| transaction startswith="log in:"
| search log_type="ERROR"

[whrg]

Hello @splunksplunk2323,

I suppose the error events have no username field.
So the challenge here lies in connecting the error messages with the usernames.

You can use streamstats in your case since at each time only one user can be connected.

Do you already have a field for the username? If so, you can remove the rex line:

| your base search
| rex field=_raw "log in: (?<user>\w+)"
| streamstats current=true last(user) as logged_in_user
| search log_type="ERROR"