How to combine multiple queries and then use the final result in one final query?

[amaurya1]

DON'T GET INTIMIDATED BY THE LENGTH OF THE QUESTION.
I'm getting account numbers from the first three queries. I want to combine all the account numbers (no duplicates) and once I've the combined list of account numbers, I would like to find all fails from the 4th query only for these accounts. Can someone please help me with this scenario?

Query 1.

index=america sourcetype=abc 
NOT
[search index=russia actorClient=def flag_restricted=1 OR flag_peeked=1 
| stats count by empNo |rename empNo as account_number|table account_number]
| eval is_grace=if(event=="P_GRACE", 1, 0)
| eval is_pass=if(event=="P_PASS", 1, 0)
| eval is_fail=if(event=="P_FAIL", 1, 0)
| stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id 
| table account_number

---

2.

index=russia actorClient=def 
| eval PTO = if('flag_restricted'== 1 , 1, 0)
| eval peeked = if('flag_peeked'== 1, 1, 0)
| stats min(_time) as _time, sum(PTO) as PTO ,sum(peeked) as peeked by empNo | where PTO > 0 OR peeked > 0
| table empNo | rename empNo as account_number

---

3.

index=america sourcetype=abc 
NOT
[search index=asia (TYPE=404 OR TYPE=505 OR TYPE=202 OR TYPE=303 OR TYPE=101) 
| stats count by employee_id |rename employee_id as account_number|table account_number | where account_number!=""]
| eval is_grace=if(event=="P_GRACE", 1, 0)
| eval is_pass=if(event=="P_PASS", 1, 0)
| eval is_fail=if(event=="P_FAIL", 1, 0)
| stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id | where fail > 0 and success = 0
| table account_number

---

Finally once I have all the accounts by combining above three queries, I would like to use those account_numbers in the below query to find total number of fails.
Query 4.

index=america sourcetype=abc 
| eval is_grace=if(event=="P_GRACE", 1, 0)
| eval is_pass=if(event=="P_PASS", 1, 0)
| eval is_fail=if(event=="P_FAIL", 1, 0)
| stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id 
| eval all_fails = if(fail > 0 and success = 0, fail, 0)
| timechart span=1h sum(all_fails) as FINAL_NUMBER

[woodcock]

It is not clear what you need but maybe this:

index=america sourcetype=abc AND
[
(index=america sourcetype=abc 
OR
(index=russia actorClient=def)
| multireport
[search NOT [search index=russia actorClient=def flag_restricted=1 OR flag_peeked=1 
| stats count by empNo | rename empNo as account_number | table account_number]
|stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id 
| table account_number]
[stats min(_time) as _time, sum(PTO) as PTO ,sum(peeked) as peeked by empNo | where PTO > 0 OR peeked > 0
| table empNo
| rename empNo as account_number]
[search NOT [search index=asia (TYPE=404 OR TYPE=505 OR TYPE=202 OR TYPE=303 OR TYPE=101) 
| stats count by employee_id |rename employee_id as account_number | table account_number | where account_number!=""]
| stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id
| where fail > 0 and success = 0 | table account_number]
| stats count BY account_number
| rename account_number AS empNo
| table empNo
]
| eval is_grace=if(event=="P_GRACE", 1, 0)
| eval is_pass=if(event=="P_PASS", 1, 0)
| eval is_fail=if(event=="P_FAIL", 1, 0)
| stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id 
| eval all_fails = if(fail > 0 and success = 0, fail, 0)
| timechart span=1h sum(all_fails) as FINAL_NUMBER

[amaurya1]

Hi @dmarling Thanks for your answer.

I'm using version 7.1.6.
In line number 35, after you close the bracket, should it be "| search" index? because splunk is not accepting this. Also the number of accounts would be quite high so I think I will face the timeout issue. However, let me run it it for 10 mins window just to validate the results

[dmarling]

A "| search" should not be necessary on line 35 as that bracket close is closing off the subsearch before the main search. That subsearch is providing you with the list of account_numbers that you are going to pass into the main search from all of the prior subsearches. I tested the search (and got no results since I don't have your data) and I did not get any syntax errors when testing in 7.3 and 6.6.6.

[amaurya1]

@dmarling the query looks fine and is even giving correct result for a small time window but can't be used if we want to query 1 hour of data or more.
Do you have any suggestions how can we fine tune it to make it handle large amount of data?
Also, could you please let me know what is line 34 doing in this query?

[dmarling]

Line 34 is removing the count field after the "| stats count by account_number" The stats count was done instead of dedup command as I find it to have better performance for deduping. I also do not want the count field to exist when the format command is executed on line 35 as that formats the results of the account_number field into a search friendly format of account_number="account1" OR account_number="account2" I'll take a look at the searches that are populating the list as it looks like there is room to combine some of those into a single search with OR statements between the different groupings. That should lower the search overhead. Ultimately it may make more sense to run those three account_number populating searches and output the results into a lookup file or if this is a dashboard the formatted account number list into a token which is then passed into search 4.

[dmarling]

Are you on a version 6.6.6 or higher? If so you can use union to accomplish this. The one problem you may run into is the subsearch timing out if you have enough data to chew through.

[| union maxtime=300 timeout=300 
        [ search index=america sourcetype=abc 
            NOT 
            [ search index=russia actorClient=def flag_restricted=1 OR flag_peeked=1 
            | stats count by empNo 
            | rename empNo as account_number 
            | table account_number] 
        | eval is_grace=if(event=="P_GRACE", 1, 0) 
        | eval is_pass=if(event=="P_PASS", 1, 0) 
        | eval is_fail=if(event=="P_FAIL", 1, 0) 
        | stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id 
        | table account_number] 
        [ search index=russia actorClient=def 
        | eval PTO = if('flag_restricted'== 1 , 1, 0) 
        | eval peeked = if('flag_peeked'== 1, 1, 0) 
        | stats min(_time) as _time, sum(PTO) as PTO ,sum(peeked) as peeked by empNo 
        | where PTO > 0 OR peeked > 0 
        | table empNo 
        | rename empNo as account_number] 
        [ search index=america sourcetype=abc 
            NOT 
            [ search index=asia (TYPE=404 OR TYPE=505 OR TYPE=202 OR TYPE=303 OR TYPE=101) 
            | stats count by employee_id 
            | rename employee_id as account_number 
            | table account_number 
            | where account_number!=""] 
        | eval is_grace=if(event=="P_GRACE", 1, 0) 
        | eval is_pass=if(event=="P_PASS", 1, 0) 
        | eval is_fail=if(event=="P_FAIL", 1, 0) 
        | stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id 
        | where fail > 0 and success = 0 
        | table account_number] 
    | stats count by account_number 
    | fields - count 
    | format] index=america sourcetype=abc 
| eval is_grace=if(event=="P_GRACE", 1, 0)
| eval is_pass=if(event=="P_PASS", 1, 0)
| eval is_fail=if(event=="P_FAIL", 1, 0)
| stats min(_time) as _time, sum(is_grace) as grace, ,sum(is_pass) as success, sum(is_fail) as fail by account_number, session_id 
| eval all_fails = if(fail > 0 and success = 0, fail, 0)
| timechart span=1h sum(all_fails) as FINAL_NUMBER