topic Re: Compare the values from two fields ( one is from a lookup) in Splunk Search

Compare the values from two fields ( one is from a lookup)

ng87 — Tue, 29 Sep 2020 16:45:24 GMT

Hi all

Trying to build a query and struggling in "comparing" two fields.

Essentially this is what i am trying to do

1) I have logs from our online email service which has the usual details ( time , source ip , email address and source logon country etc )

2) I have a lookup in Splunk with the common Active directory details ( name, title , country etc )

What i am trying to do is to get a search to show me the logons where the two Country fields dont match

ex ( UserA logged on from Germany, his AD Details show the user is based in Germany therefore i dont want to know)

This is what i have so far :

index="email"
| lookup adusers Email AS Username OUTPUT DisplayName Title Country
| where "logon country" != "Country"
| table Username "Source IP" "logon country" DisplayName Title Country

The "Where" statement doesn't , any ideas on how to get this working ( if its possible of course) .

Re: Compare the values from two fields ( one is from a lookup)

gcusello — Tue, 29 Sep 2020 16:57:26 GMT

Hi @ng87,

di you tried with "search" instead "where"?

Ciao.

Giuseppe

Re: Compare the values from two fields ( one is from a lookup)

ng87 — Tue, 29 Sep 2020 17:01:40 GMT

Hi @gcusello

Yes i tried the below :

| search "logon country" != "Country"

Didn't work.

Re: Compare the values from two fields ( one is from a lookup)

ITWhisperer — Tue, 29 Sep 2020 17:16:33 GMT

Try logon country in single quotes (as it is a field name) and remove double quotes from Country since it is also a field name (without spaces in)

| where 'logon country' != Country