Results returned from subsearch / appendcolos inaccurate

[osh55]

I have the following simplified version of the query where for each caller, I need all_calls (from sourcetype=x) and messagebank_calls (from sourcetype=y). 

index=sample1 sourcetype=x host=host1
| stats values(caller) as caller by callid
| stats count as all_calls by caller
| rename caller as caller_party
| appendcols
    [ search index=sample1 AND sourcetype=y 
    | stats count as messagebank_calls by caller_party]
| search all_calls=*

messagebank_calls value is incorrect and I'm guessing because of the subsearch/appendcols? How do I increase the limit or re-write so I can get the same results caller, all_calls, messagebank_calls?

[gcusello]

Hi @osh55 ,

let me understand: is the issue the number or results of the subsearch that are more than 50,000?

did you tried to put bo the searches in main search?

index=sample1 ((sourcetype=x host=host1) OR sourcetype=y)
| eval caller=coalesce(caller, caller_party)
| stats 
     count(eval(sourcetype=x)) AS all_calls 
     count(eval(sourcetype=y)) AS messagebank_calls
     BY caller
| search all_calls=*

Ciao.

Giuseppe

Ciao.

Giuseppe

[osh55]

Thank you, that's a neat solution. However in my simplified query I have removed some eval conditions and filters. One of them being the caller and caller_party formats are different in the sourcetypes. So below the rename I have `| eval caller_party=substr(caller_party, 2)`. Could you please advise how your solution would change to account for this? Thank you!

[gcusello]

Hi @osh55 ,

please share your search, anyway, you have to adapt the eval commands to the different kinds of logs.

Ciao.

Giuseppe

[osh55]

Thanks Giuseppe, so my search is as follows:

index=sample1 sourcetype=x host=host1 (action=200 OR action=400)
| stats values(caller) as caller by callid
| stats count as all_calls by caller
| rename caller as caller_party
| eval caller_party=substr(caller_party, 2)
| appendcols
    [ search index=sample1 AND sourcetype=y 
    | stats count as messagebank_calls by caller_party]
| search all_calls=*

Note how the base search has a few conditions on it, so in the final result I would only want the callers that satisfy the condition and has a matching record in sourcetype=y.  

[gcusello]

Hi @osh55 ,

ok, please try this:

index=sample1 ((sourcetype=x host=host1) OR sourcetype=y)
| eval caller_party=if(sourcetype=x, substr(caller, 2), caller_party)
| stats 
     count(eval(sourcetype=x)) AS all_calls 
     count(eval(sourcetype=y)) AS messagebank_calls
     BY caller
| search all_calls=*

See my approach and adapt it to your use case.

Ciao.

Giuseppe