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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...