topic Re: help on where not condition which works randomly in Splunk Search

help on where not condition which works randomly

jip31 — Wed, 04 Nov 2020 11:04:04 GMT

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

Re: help on where not condition which works randomly

richgalloway — Wed, 04 Nov 2020 14:24:39 GMT

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

Re: help on where not condition which works randomly

jip31 — Thu, 05 Nov 2020 06:43:26 GMT

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?

Re: help on where not condition which works randomly

isoutamo — Thu, 05 Nov 2020 15:29:43 GMT

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

Re: help on where not condition which works randomly

jip31 — Sat, 07 Nov 2020 05:01:17 GMT

you mean something like this?

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

Re: help on where not condition which works randomly

isoutamo — Sat, 07 Nov 2020 17:49:06 GMT

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”

Re: help on where not condition which works randomly

jip31 — Sun, 08 Nov 2020 05:14:17 GMT

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

Re: help on where not condition which works randomly

jip31 — Sun, 08 Nov 2020 05:29:36 GMT

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"

Re: help on where not condition which works randomly

jip31 — Mon, 16 Nov 2020 15:28:42 GMT

Is anybody can't help please??

Re: help on where not condition which works randomly

jip31 — Thu, 19 Nov 2020 09:37:58 GMT

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

Re: help on where not condition which works randomly

jip31 — Thu, 26 Nov 2020 10:04:04 GMT

Is anybody can't help?

Re: help on where not condition which works randomly

isoutamo — Thu, 26 Nov 2020 10:26:08 GMT

`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

Re: help on where not condition which works randomly

jip31 — Thu, 26 Nov 2020 12:17:56 GMT

no same problem....

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

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

Re: help on where not condition which works randomly

jip31 — Tue, 01 Dec 2020 12:39:25 GMT

Is anybody can't help?

Re: help on where not condition which works randomly

jip31 — Wed, 02 Dec 2020 05:30:30 GMT

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

Re: help on where not condition which works randomly

jip31 — Wed, 27 Jan 2021 05:47:34 GMT

Is anybody can't help please?

Re: help on where not condition which works randomly

scelikok — Wed, 27 Jan 2021 06:11:29 GMT

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'