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

[ng87]

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) .

[ITWhisperer · Accepted solution]

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

[gcusello]

Hi @ng87,

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

Ciao.

Giuseppe

[ng87]

Hi @gcusello 

 Yes i tried the below :

| search "logon country" != "Country"

Didn't work.