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

[Gowtham0809]

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

[Richfez · Accepted solution]

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