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!

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 ...