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?
Is anybody have an idea in order to solve this strange behaviour?
Is anybody can help please?
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
Hi scelikok
Unfortunately, same problem....
You can see in the screenshot that there is Geolocation_building = SNOW_building
https://www.cjoint.com/c/KCdhxmAx5Ih
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.
Hi
Sorry it doesn't works
Have you other solution please?
Do you have some examples of your data you can share?
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
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+)"
same results...