Splunk Search

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

splunksplunk232
Explorer

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:
alt text

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

0 Karma
1 Solution

whrg
Motivator

The transaction command could also work here:

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

View solution in original post

0 Karma

whrg
Motivator

The transaction command could also work here:

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

whrg
Motivator

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"
0 Karma
Get Updates on the Splunk Community!

Index This | Forward, I’m heavy; backward, I’m not. What am I?

April 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

A Guide To Cloud Migration Success

As enterprises’ rapid expansion to the cloud continues, IT leaders are continuously looking for ways to focus ...

Join Us for Splunk University and Get Your Bootcamp Game On!

If you know, you know! Splunk University is the vibe this summer so register today for bootcamps galore ...