how to add the results of two searches as two columns

[ranjyotiprakash]

I am running two different searches to get the total number of successful Logins and Unsuccessful Logins. The searches are :

sourcetype="audit" Transaction_Type = LOGIN | rex field=_raw ".*\s(?<unit_name>\S+)\sAUDIT.*$" | stats count BY unit_name |rename count AS "Successful Logins"

result is

Unit_name Successful Logins

unit1 10

unit2 20

sourcetype="audit" Transaction_Type ="UNSUCCESSFUL_LOGIN" | rex field=_raw ".*\s(?<unit_name>\S+)\sAUDIT.*$" | stats count BY unit_name |rename count AS "UnSuccessful Logins"

Unit_name UnSuccessful Logins

unit1 10

unit2 20

But, I want show the both the successful and Unsuccessful Logins in the same chart.Like,

Unit_name Successful Logins Unsuccessful Logins

unit1 10 10

Unit2 20 20

How to do this. I tried using append. Please help !

Thanks !

[lguinn2 · Accepted solution]

Join will probably work better for you than append, but I think you can do it all in one search like this:

sourcetype="audit" (Transaction_Type = LOGIN OR Transaction_Type ="UNSUCCESSFUL_LOGIN") 
| rex field=_raw ".*\s(?<unit_name>\S+)\sAUDIT.*$" 
| stats count BY Transaction_Type unit_name 

But if you really want the format that you showed...

sourcetype="audit" (Transaction_Type = LOGIN OR Transaction_Type ="UNSUCCESSFUL_LOGIN") 
| rex field=_raw ".*\s(?<unit_name>\S+)\sAUDIT.*$" 
| eval success=if (Transaction_Type=="LOGIN",1,0)
| eval fail=if (Transaction_Type=="UNSUCCESSFUL_LOGIN",1,0)
| stats sum(success) as "Successful Logins" sum(fail) as "UnSuccessful Logins" by unit_name

Avoiding join and append will make your search more efficient - and make it possible to work with larger datasets.

[ranjyotiprakash]

Second option worked as I wanted. Thanks for such a quick reply, Iguinn.

[ranjyotiprakash]

Thanks for your reply !! I was thinking too hard for this. That's worked out very well.