Help on "where not" clause wich doesn't works and conditional formating issue

[jip31]

Hello

I use the search 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 aps.csv NAME as AP_NAME OUTPUT Building Country Site 
| lookup fo_all HOSTNAME as USERNAME output SITE BUILDING_CODE 
| eval Building=upper(Building) 
| eval Site=upper(Site) 
| eval SITE=upper(SITE) 
| 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 "SNOW building" by USERNAME 
| where NOT ('Geolocation building' = 'SNOW building') 
| rename USERNAME as Hostname 
| sort -"Last check date"

As you can see in the where clause, I just need to display the Geolocation building fields which are differents than the SNOW building

But it works randomly because I have fields where Geolocation building = SNOW building and where Geolocation building fields are not equal to SNOW building fields

I tried :

| where NOT like ('Geolocation building','SNOW building')
| where NOT match ('Geolocation building','SNOW building')

But it changes anything!

Other problem

I need to do a conditional formating on SNOW building field but no colors are displayed!

<format type="color" field="SNOW building">
<colorPalette type="map">{"ZB12":#4FA484,"G39":#AF575A,"ZD30":#294E70,"A50":#53A051,"E74":#B6C75A,"ZH38":#F8BE34}</colorPalette>
</format>

What is the global problem please?

[jip31]

Is anybody have an idea in order to solve this strange behaviour?

[jip31]

Is anybody can help please?

[scelikok]

Hi @jip31,

Please try below; I changed field name without spaces;

 `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 aps.csv NAME as AP_NAME OUTPUT Building Country Site 
| lookup fo_all HOSTNAME as USERNAME output SITE BUILDING_CODE 
| eval BUILDING_CODE=upper(trim(BUILDING_CODE)) 
| eval Building=upper(trim(Building)) 
| eval Site=upper(Site) 
| eval SITE=upper(SITE) 
| 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 SNOW_building by USERNAME 
| where Geolocation_building != SNOW_building 
| rename USERNAME as Hostname 
| sort -Last_check_date

[jip31]

Hi scelikok

Unfortunately, same problem....

You can see in the screenshot that there is Geolocation_building = SNOW_building 

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

[scelikok]

Hi @jip31,

You can try below, maybe there are some whitespaces on your data;

 `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 aps.csv NAME as AP_NAME OUTPUT Building Country Site 
| lookup fo_all HOSTNAME as USERNAME output SITE BUILDING_CODE 
| eval BUILDING_CODE=upper(trim(BUILDING_CODE)) 
| eval Building=upper(trim(Building)) 
| eval Site=upper(Site) 
| eval SITE=upper(SITE) 
| 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 "SNOW building" by USERNAME 
| where 'Geolocation building' != 'SNOW building' 
| rename USERNAME as Hostname 
| sort -"Last check date"

This may help for your second coloring problem too.

[jip31]

Hi

Sorry it doesn't works

Have you other solution please?

[ITWhisperer]

Do you have some examples of your data you can share?

[jip31]

In the screenshot below :

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

You can see that the results of my search display Geolocation Building equal to ServiceNow Building and Geolocation Building different than ServiceNow Building

I just need Geolocation Building different than ServiceNow Building that is the reason why I use :

| where NOT ('Geolocation building' = 'ServiceNow building') 

But it doesn't works despite I use the "trim" command

[ITWhisperer]

How about trying to extract numbers and letter in case there any "hidden" other characters (not sure why this would work when trim doesn't seem to)

| rex field=BUILDING_CODE "(?<BUILDING_CODE>\w+)" 
| rex field=Building "(?<Building>\w+)" 

[jip31]

same results...