Advanced Nested query

[webshan]

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.

[jonuwz]

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

[dart · Accepted solution]

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