help on a count for doing a pie chart

[jip31]

hi

From the code below, I need to do a pie chart with 2 labels
I am doing a first count in order to count the events | where NOT (Building_AP = Building_IT)
My question is simple
How to display a second label in the pie chart which count the totality of the events less the events | where NOT (Building_AP = Building_IT) ?
It means that i need a label which count the % of events | where NOT (Building_AP = Building_IT) and another label which count the % of the remaining events

`test` 
    [| inputlookup host.csv 
    | table host 
    | rename host as USERNAME ] 
| lookup YY.csv NAME as AP_NAME OUTPUT Building 
| lookup XX.csv HOSTNAME as USERNAME output BUILDING_CODE 
| eval Building=upper(Building) 
| stats last(Building) as Building_AP, last(BUILDING_CODE) as Building_IT by USERNAME 
| where NOT (Building_AP = Building_IT) AND isnotnull(Building_IT) 
| stats count as APnotITOP

Thanks for your help

[to4kawa · Accepted solution]

 | where NOT (Building_AP = Building_IT) AND isnotnull(Building_IT) 
 | stats count as APnotITOP

⇨

| search Building_IT=*
| fillnull Building_AP,Building_IT
| eval APnotITOP=(Building_IT / (Building_AP +Building_IT) * 100)."%"

try it.

[to4kawa]

sorry , I misunderstood.

First, try this to create a pie chart.

| makeresults count=20
| streamstats count as user_count
| eval username="test".user_count
| eval Building_AP=random()%3+1
| eval Building_IT=random()%3+1
| stats  count(eval(Building_AP==Building_IT)) as APnotITOP count(username) as Total
| eval Total = Total - APnotITOP
| eval tmp=1
| untable tmp category count
| fields - tmp

In this way, I think you should use untable. Therefore, where is not necessary,

| search Building_IT=*
| stats  count(eval(Building_AP==Building_IT)) as APnotITOP count(USERNAME) as Total
| eval Total = Total - APnotITOP
| eval tmp=1
| untable tmp category count
| fields - tmp

thanks.

[jip31]

You mean replace
| where NOT (Building_AP = Building_IT) AND isnotnull(Building_IT)
| stats count as APnotITOP
By your code?
If yes, what do you do about the where condition?
I want to count the events where where NOT (Building_AP = Building_IT) AND isnotnull(Building_IT) and to count also all the events in order to do a pie after deleting events where Building_IT is empty (thats why I use isnotnull(Building_IT) )
So your query is not good

[to4kawa]

sorry,I misunderstood.

First, try this to create a pie chart.

| makeresults count=20
| streamstats count as user_count
| eval username="test".user_count
| eval Building_AP=random()%3+1
| eval Building_IT=random()%3+1
| stats count(eval(Building_AP==Building_IT)) as APnotITOP count(username) as Total
| eval Total = Total - APnotITOP
| eval tmp=1
| untable tmp category count
| fields - tmp