Splunk Search

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

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=DORALogin | eval UserName=lower(UserName) | dedup UserName | table time UserName index | eval agedays=(now()-time)/(606024) | where age_days > 180 | chart count by index

and

  1. index=* sourcetype=DORALogin | eval UserName=lower(UserName) | dedup UserName | table time UserName index | eval agedays=(now()-time)/(606024) | where age_days < 180 | chart count by index

Thanks

0 Karma
1 Solution

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

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