Splunk Search

how to add the results of two searches as two columns

ranjyotiprakash
Communicator

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 !

1 Solution

lguinn2
Legend

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.

View solution in original post

lguinn2
Legend

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
Communicator

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

0 Karma

ranjyotiprakash
Communicator

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...