Splunk Search

Combine 2 searches into table..one is a stats

brywilk_umich
Path Finder

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

Tags (2)
0 Karma
1 Solution

lguinn2
Legend

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

View solution in original post

lguinn2
Legend

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
Legend

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

0 Karma

brywilk_umich
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...