Combine 2 searches into table..one is a stats

[brywilk_umich]

Hello
I have the following searches below. The simta_smtp_authuser is the same username as the simta_ublauthuser of the second search.

What Im trying to do is include the stat results from search 2 into the table of search 1 and rename the simta_ublauthuser to simta_smtp_authuser so that sorting will group everything together.

index=collaboration sourcetype="mail-2" Auth [search index=collaboration sourcetype="mail-2" | fields simta_ublauthuser | dedup simta_ublauthuser | return 500 $simta_ublauthuser] | fields simta_client_ip simta_smtp_authuser | dedup simta_client_ip | geoip simta_client_ip | sort simta_smtp_authuser simta_client_ip | table simta_smtp_authuser simta_client_ip simta_client_ip_country_name simta_client_ip_city

thanks a bunch everyone. hope that makes sense.

index=collaboration sourcetype="mail-2" UBL | stats count by simta_ublauthuser simta_ublstatus

[lguinn2 · Accepted solution]

Try this

index=collaboration sourcetype="mail-2" UBL 
| stats count by simta_ublauthuser simta_ublstatus
| rename simta_ublauthuser as user
| join type=inner max=0 user [ search index=collaboration sourcetype="mail-2" Auth 
    | dedup simta_client_ip 
    | eval user = simta_smtp_authuser
    | fields user simta_client_ip ]
| geoip simta_client_ip 
| sort user simta_client_ip 
| table user simta_client_ip simta_client_ip_country_name simta_client_ip_city simta_ublstatus count

[lguinn2]

I updated the answer and added max=0 to the join command. This should fix the problem, I hope.

[brywilk_umich]

thanks. Its close its just seems to be missing the different simta_client_ip's. looks like it grabs the first one and seems to stop.

The simta_client_ip will usually be a couple (depending on if the account is used by a spammer from multiple locations). The simta_ublstatus will be the same for the user