Splunk Search

Advanced Nested query

webshan
Engager

Hi all,

My logs have data in following format:

" session:host:loginid some-event-data"
Ex: 123:abcd:test1 Login Attempt
Ex: 123:abcd:test2 Login Success
Ex: 123:abcd:test1 Login Failed
Ex: 123:abcd:test2 Reset the passoword
Ex: 123:abcd:test3 Encountered Error in Profile

I need to generate a report as below:

LoginID Logins Failures Errors
Test1 10 2 30
Test2 2 1 1
Test3 4 5 6

I tried "Query1 | stats count as Logins | appendcols [search Query2 | stats count as Failures] | appendcols [search Query3 | stats count as Errors]" But it gives results for all users not per user stats as Below.

Logins Failures Errors
100 200 150

How to pull the records per users? How do I run 3 nested queries for each user?

Thanks in Advance.

Tags (3)
0 Karma
1 Solution

dart
Splunk Employee
Splunk Employee

You don't need three queries, you can do this with a single stats command, assuming you have extracted loginid as a field:

sourcetype=my_login_sourcetype | stats count(eval(searchmatch("Success"))) as Logins count(eval(searchmatch("Failed"))) as Failures count(eval(searchmatch("Error"))) as Errors by loginid

View solution in original post

jonuwz
Influencer

You don't have to run nested queries, what you've got there is a classic example of using the stats command.

First thing, if the fields are not already available to be manipulated, you need to extract them.

Here's an example to capture the loginid and the message

... | rex "[^:]+:[^:]+:[^:]+:(?<loginid>[^\s]+)\s+(?<message>.*)"

Then we can perform some stats

... | stats count(eval(match(message,"Success"))) as Logins count(eval(match(message,"Failed"))) as Failures count(eval(match(message,"Error"))) as as Errors by loginid

So taking "Logins" as an example, this

count(eval(match(message,"Success"))) 

Is saying, try to match 'Success' in the message

The eval surrounding it either returns 1 for success, or NULL for a failure

Then we count up the number of Successes per loginid

Repeat for Failures and Errors

0 Karma

dart
Splunk Employee
Splunk Employee

You don't need three queries, you can do this with a single stats command, assuming you have extracted loginid as a field:

sourcetype=my_login_sourcetype | stats count(eval(searchmatch("Success"))) as Logins count(eval(searchmatch("Failed"))) as Failures count(eval(searchmatch("Error"))) as Errors by loginid
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...