Splunk Search

help on where not condition which works randomly

jip31
Builder

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

Labels (1)
Tags (1)
0 Karma

scelikok
Champion

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'
If this reply helps you an upvote is appreciated.
0 Karma

jip31
Builder

Is anybody can't help please?

0 Karma

jip31
Builder

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

0 Karma

jip31
Builder

Is anybody can't help?

0 Karma

jip31
Builder

Is anybody can't help?

0 Karma

jip31
Builder

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

 

0 Karma

jip31
Builder

Is anybody can't help please??

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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')
---
If this reply helps you, an upvote would be appreciated.
0 Karma

jip31
Builder

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?

0 Karma

isoutamo
SplunkTrust
SplunkTrust
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;-)
0 Karma

jip31
Builder

you mean something like this? 

 

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

 

0 Karma

isoutamo
SplunkTrust
SplunkTrust
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”
0 Karma

jip31
Builder

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"
0 Karma

isoutamo
SplunkTrust
SplunkTrust
`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

0 Karma

jip31
Builder

no same problem....

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

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

0 Karma

jip31
Builder

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!