help on where not condition which works randomly

[jip31]

hi

As you can see at the end of my search, I use a where condition

But sometimes, even if the condition is true ('Geolocation building' = 'SNOW building'), the events is displayed

what is wrong please?

 `wire` 
| fields AP_NAME USERNAME LAST_SEEN 
| eval USERNAME=upper(USERNAME) 
| eval LAST_SEEN=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N") 
| lookup ap.csv NAME as AP_NAME OUTPUT Building Country Site 
| lookup fo_all HOSTNAME as USERNAME output SITE ROOM COUNTRY BUILDING_CODE 
| eval Building=upper(Building) 
| eval Site=upper(Site) 
| eval SITE=upper(SITE) 
| eval Building=upper(Building) 
| eval LAST_SEEN = strftime(LAST_SEEN, "%Y-%m-%d %H:%M") 
| stats last(LAST_SEEN) as "Last check date", last(AP_NAME) as "Access point", last(Site) as "Geolocation site", last(Building) as "Geolocation building", last(SITE) as "SNOW site", last(BUILDING_CODE) as "S building" by USERNAME 
| where NOT ('Geolocation building' = 'S building')

I have tested with :

| search NOT ("Geolocation building" = "S building") 

but same thing

[scelikok]

Hi @jip31,

Maybe there are whitespaces in your csv data, please try below;

 `wire` 
| fields AP_NAME USERNAME LAST_SEEN 
| eval USERNAME=upper(USERNAME) 
| eval LAST_SEEN=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N") 
| lookup ap.csv NAME as AP_NAME OUTPUT Building Country Site 
| lookup fo_all HOSTNAME as USERNAME output SITE ROOM COUNTRY BUILDING_CODE 
| eval Site=upper(Site) 
| eval SITE=upper(SITE) 
| eval Building=upper(trim(Building))
| eval BUILDING_CODE =upper(trim(BUILDING_CODE))
| eval LAST_SEEN = strftime(LAST_SEEN, "%Y-%m-%d %H:%M") 
| stats last(LAST_SEEN) as "Last check date", last(AP_NAME) as "Access point", last(Site) as "Geolocation site", last(Building) as "Geolocation building", last(SITE) as "SNOW site", last(BUILDING_CODE) as "S building" by USERNAME 
| where 'Geolocation building' != 'S building'

[jip31]

Is anybody can't help please?

[jip31]

Considering that the datas come from 2 différent CSV files, is it possible that the issue comes from the data format?

[jip31]

Is anybody can't help?

[jip31]

Is anybody can't help?

[jip31]

Another remark

When I am doing a value coloring on the 2 fields with a rule color, it's impossible to have the same color for the 2 fields even if Geolocation building = SNOW building

So it shows that there is something wick make that one of the values  is not interpreted like it should

[jip31]

Is anybody can't help please??

[richgalloway]

In Splunk, double and single quotation marks are not always interchangeable.  This is especially true in the where command.  Double quotes surround literal strings and single quotes surround field names.  This means "where not ("Geolocation building" = "S building")" will always fail because the two strings are not the same.

Have you tried using either of the comparison functions: like and match?

| where NOT like('Geolocation building','S building')

[jip31]

Hi Rich

You are right for double quotes, it's just a copy past issue

Unfortunately I tried with

| where NOT match ('Geolocation building','S building') 

And with

| where NOT like ('Geolocation building','S building') 

I also note that when an event with 'Geolocation building' field = 'S building' field is displayed in spite of this where condition, the "Geolocation site' field is always different than the 'SNOW site' field

Is it possible that the issue comes from this?

[isoutamo]

I propose that use fields without spaces on your event processing phase and when everything is ready for presenting data to users then rename those. That will save you from lot of additional headaches;-)

[jip31]

you mean something like this? 

| where NOT like ('Geolocation','S')
| rename "Geolocation" as toto, "S" as tutu?

[isoutamo]

I mean that in stats you should do “stats last(building) as geo_building” ... then when geo_building.... and in last phase rename geo_building as “Geolocation building”

[jip31]

I give you the entire search, pearhaps there is something wrong?

 `wire` 
| fields AP_NAME USERNAME LAST_SEEN 
| eval USERNAME=upper(USERNAME) 
| eval LAST_SEEN=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N") 
| lookup ap.csv NAME as AP_NAME OUTPUT Building Country Site 
| lookup fo_all HOSTNAME as USERNAME output SITE ROOM COUNTRY BUILDING_CODE 
| eval Building=upper(Building) 
| eval Site=upper(Site) 
| eval SITE=upper(SITE) 
| eval Building=upper(Building) 
| eval LAST_SEEN = strftime(LAST_SEEN, "%Y-%m-%d %H:%M") 
| stats last(LAST_SEEN) as "Last check date", last(AP_NAME) as "Access point", last(Site) as "Geolocation site", last(Building) as "Geobuild", last(SITE) as "SNOW site", last(BUILDING_CODE) as "SNOWbuild" by USERNAME 
| rename Geobuild as "Geolocation building", SNOWbuild as "SNOW building"
| where NOT like ('Geolocation building','SNOW building') 
| rename USERNAME as Hostname 
| sort -"Last check date"

[isoutamo]

`wire` 
| fields AP_NAME USERNAME LAST_SEEN 
| eval USERNAME=upper(USERNAME) 
| eval LAST_SEEN=strptime(LAST_SEEN, "%Y-%m-%d %H:%M:%S.%1N") 
| lookup ap.csv NAME as AP_NAME OUTPUT Building Country Site 
| lookup fo_all HOSTNAME as USERNAME output SITE ROOM COUNTRY BUILDING_CODE 
| eval Building=upper(Building) 
| eval Site=upper(Site) 
| eval SITE=upper(SITE) 
| eval Building=upper(Building) 
| eval LAST_SEEN = strftime(LAST_SEEN, "%Y-%m-%d %H:%M") 
| stats last(LAST_SEEN) as Last_check_date, last(AP_NAME) as Access_point, last(Site) as Geolocation_site, last(Building) as Geobuild, last(SITE) as SNOW_site, last(BUILDING_CODE) as SNOWbuild by USERNAME 
| where NOT like (Geolocation, SNOWbuild) 
| rename USERNAME as Hostname 
| sort -Last_check_date
| rename <what ever field you want>

Does this works?

r. Ismo

[jip31]

no same problem....

as you can see in the attachement, the results display hostname with Geobuild ) SNOWbuild....

https://www.cjoint.com/c/JKAmqiSYOQg 

[jip31]

here is what I done

| stats last(LAST_SEEN) as "Last check date", last(AP_NAME) as "Access point", last(Site) as "Geolocation site", last(Building) as "Geobuild", last(SITE) as "SNOW site", last(BUILDING_CODE) as "SNOWbuild" by USERNAME 
| rename Geobuild as "Geolocation building", SNOWbuild as "SNOW building"
| where NOT like ('Geolocation building','SNOW building') 

and unfortunately, it doesn't works....