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
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
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
I updated the answer and added max=0
to the join
command. This should fix the problem, I hope.
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