Splunk Search

How to combine my two search queries using join or subsearch?

Gowtham0809
New Member

Hi,

I have 2 different search queries which i need to combine and generate the report as similar to dashboard and output distinctly in a CSV file can somebody help :

  1. index=* sourcetype=DORA_Login | eval User_Name=lower(User_Name) | dedup User_Name | table _time User_Name index | eval age_days=(now()-_time)/(60*60*24) | where age_days > 180 | chart count by index

and

  1. index=* sourcetype=DORA_Login | eval User_Name=lower(User_Name) | dedup User_Name | table _time User_Name index | eval age_days=(now()-_time)/(60*60*24) | where age_days < 180 | chart count by index

Thanks

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

I'm not sure I understand exactly what you are trying to accomplish, but try this output.

index=* sourcetype=DORA_Login 
| eval age_days=(now()-_time)/(60*60*24) 
| eval IsOver180 = if(age_days>180,"Over 180","Under 180") 
| eval User_Name=lower(User_Name) | dedup User_Name 
| chart count OVER index BY IsOver180 

And actually, as a test because it'll be SO much faster - try doing a search over the previous 7 days or week to date and instead of using "180" use, say, 1 or 3. This should do this week, looking for ones older than 3 days:

index=* sourcetype=DORA_Login earliest=-7d@d
| eval age_days=(now()-_time)/(60*60*24) 
| eval IsOver180 = if(age_days>3,"Over 3","Under 3") 
| eval User_Name=lower(User_Name) | dedup User_Name 
| chart count OVER index BY IsOver180 

Give that a go, see what you think.

Happy Splunking!
-Rich

View solution in original post

Richfez
SplunkTrust
SplunkTrust

I'm not sure I understand exactly what you are trying to accomplish, but try this output.

index=* sourcetype=DORA_Login 
| eval age_days=(now()-_time)/(60*60*24) 
| eval IsOver180 = if(age_days>180,"Over 180","Under 180") 
| eval User_Name=lower(User_Name) | dedup User_Name 
| chart count OVER index BY IsOver180 

And actually, as a test because it'll be SO much faster - try doing a search over the previous 7 days or week to date and instead of using "180" use, say, 1 or 3. This should do this week, looking for ones older than 3 days:

index=* sourcetype=DORA_Login earliest=-7d@d
| eval age_days=(now()-_time)/(60*60*24) 
| eval IsOver180 = if(age_days>3,"Over 3","Under 3") 
| eval User_Name=lower(User_Name) | dedup User_Name 
| chart count OVER index BY IsOver180 

Give that a go, see what you think.

Happy Splunking!
-Rich

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