topic Re: Advanced Nested query in Splunk Search

Advanced Nested query

webshan — Thu, 03 Jan 2013 09:57:18 GMT

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

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.

Re: Advanced Nested query

dart — Thu, 03 Jan 2013 10:25:05 GMT

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

Re: Advanced Nested query

jonuwz — Thu, 03 Jan 2013 10:42:47 GMT

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