topic Re: Do I need to use the join command to combine three searches (including one search with the transaction command)? in Splunk Search

Do I need to use the join command to combine three searches (including one search with the transaction command)?

aba83 — Fri, 19 May 2017 22:39:07 GMT

Hi, I'm trying to combine my three searches so I can see which users are logging in from multiple locations at one time. At the moment, I have these three searches.

index=mensa_exchange-prod sourcetype=iis cs_uri_stem="/owa/auth.owa" NOT LogoffReason=* OriginalIP=* | iplocation OriginalIP | search Country=* NOT Country="United States"| rex field=user "\w{3}\\\(?\S+)" | eval User=lower(user) |table User Country | stats values(Country) as country dc(Country) as Count by User | sort User

index=mensa_radius-prod acct_status_type=1 acct_delay_time=0 vendor=Reserved NOT Wireless | iplocation tunnel_client_endpoint | search Country=* NOT Country="United States" | rex field=user "\w{3}\\\(?\S+)" | eval User=lower(user) | table User Country | stats values(Country) as Country dc(Country) as Count by User | sort User 

index=mensa_radius-prod vendor=Microsoft NOT Wireless | transaction user, Client_Friendly_Name maxspan=1 startswith=acct_session_id=* endswith=action=success  | iplocation tunnel_client_endpoint | search Country=* NOT Country="United States" | rex field=user "\w{3}\\\(?\S+)" | eval User=lower(user) |table User Country | stats values(Country) as country dc(Country) as Count by User | sort User

I was thinking the way to do this is to use a join; however, I don't know how that works if I have a transaction command. Is there another way to use this or do I have to use a JOIN? If I do use a join, how would I go about it? Thanks!

Re: Do I need to use the join command to combine three searches (including one search with the transaction command)?

aba83 — Fri, 19 May 2017 23:58:19 GMT

At the moment I have this search that combines the first two searches.

(index=mensa_exchange-prod sourcetype=iis cs_uri_stem="/owa/auth.owa" user=* NOT LogoffReason=* OriginalIP=*) OR (index=mensa_radius-prod acct_status_type=1 acct_delay_time=0 vendor=Reserved tunnel_client_endpoint=* user=* NOT Wireless) | iplocation OriginalIP | iplocation tunnel_client_endpoint | search Country!="United States" | rex field=user "\w{3}\\\(?\S+)" | eval User=lower(user) |table User Country | stats values(Country) as country dc(Country) as Count by User | sort User

Now I just have to figure out how to add the last one with the transaction. I'm assuming a join, any help would be greatly appreciated.

Re: Do I need to use the join command to combine three searches (including one search with the transaction command)?

aaraneta_splunk — Sat, 20 May 2017 00:48:45 GMT

Hi @aba83 - For future reference, instead of wrapping your sample searches in HTML <code>, try using the Code Sample (101010) button on the toolbar when you're posting your sample searches, data, and/or code. It's right next to the Blockquote button. Thanks!

Re: Do I need to use the join command to combine three searches (including one search with the transaction command)?

lguinn2 — Mon, 22 May 2017 04:55:51 GMT

Try this:

(index=mensa_exchange-prod sourcetype=iis cs_uri_stem="/owa/auth.owa" NOT LogoffReason=* OriginalIP=*)
OR (index=mensa_radius-prod acct_status_type=1 acct_delay_time=0 vendor=Reserved NOT Wireless)
| append [ search index=mensa_radius-prod vendor=Microsoft NOT Wireless 
          | transaction user, Client_Friendly_Name maxspan=1 startswith=acct_session_id=* endswith=action=success ]
| eval clientIP=if(index="mensa_exchange-prod",OriginalIP,tunnel_client_endpoint)
| iplocation clientIP
| search Country=* NOT Country="United States"
| rex field=user "\w{3}\\\(?<user>\S+)" 
| eval User=lower(user) 
| stats values(Country) as country dc(Country) as Count by User

But you do need to check the rex command - something got munged when you posted it, and I made an assumption. But it still seems weird to me.

Re: Do I need to use the join command to combine three searches (including one search with the transaction command)?

aba83 — Mon, 22 May 2017 20:08:55 GMT

The transaction search works now, but now the records from the

index=mensa_exchange-prod sourcetype=iis cs_uri_stem="/owa/auth.owa" NOT LogoffReason=* OriginalIP=*

isn't coming through.

Re: Do I need to use the join command to combine three searches (including one search with the transaction command)?

aba83 — Mon, 22 May 2017 20:17:00 GMT

Fixed it, it was the if statement. Thank you for your help.

Re: Do I need to use the join command to combine three searches (including one search with the transaction command)?

lguinn2 — Mon, 22 May 2017 22:00:57 GMT

Yes, I forgot to put quotation marks around the value in the if statement. I fixed it - thank you!