Counting total and only specific values in one table (802.1x log example)

[Ida_2017]

Hi Everybody:

I need a little help with statistics: I use this search to list all Calling_Station_IDs. In the example table below  I can find two different values for  RadiusFlow_Type, for some one one value...  

mysearch | fields RadiusFlowType, Calling_Station_ID | dedup Calling_Station_ID,RadiusFlowType | table Calling_Station_ID RadiusFlowType

Calling_Station_ID	RadiusFlowType
A1	Wired802
A1	MAB
A2	Wired802
A2	MAB
A3	MAB
A4	MAB
A5	Wired802

So I want to get a distinct number of how many calling_station_ids do Wired802 and get that number as a percentage of ALL Calling Station IDs

If I do like this, I can count the number of Calling_Station IDs which do Wired802, which in this case would be three:

mysearch | fields RadiusFlowType, Calling_Station_ID | dedup Calling_Station_ID,RadiusFlowType | table Calling_Station_ID RadiusFlowType |stats count(eval(RadiusFlowType="Wired802_1x")) AS Wired_Clients

so that is fine:-)

But I want to calculate also the total of all unique calling_station_IDs (in this example it would be 5), so that I can calculate the percentage  of clients who do either one or both flow types .  Somehow I fail to count the total in the same search and output that in a table...

How can I get an output in a table like this ?

Total distinct count Calling_Station_ID	Total Wired Clients	Percentage_wired	MAB Only CLients	Percentage_mab
5	3	3/5*100	2	2/5*100

thanks 🙂

[ITWhisperer · Accepted solution]

| makeresults 
| eval _raw="Calling_Station_ID,RadiusFlowType
A1,Wired802
A1,MAB
A2,Wired802
A2,MAB
A3,MAB
A4,MAB
A5,Wired802"
| multikv forceheader=1
| fields - _* linecount
| eval Wired=if(RadiusFlowType="Wired802",RadiusFlowType,null)
| eval MAB=if(RadiusFlowType="MAB",RadiusFlowType,null)
| fields - RadiusFlowType
| stats values(*) as * by Calling_Station_ID
| eval both=if(isnull(MAB) OR isnull(Wired), null, true())
| stats count as Total count(Wired) as Wired count(MAB) as MAB count(both) as both
| eval MABOnly=MAB-both
| eval Percentage_Wired=100*Wired/Total
| eval Percentage_MABOnly=100*MABOnly/Total
| fields Total Wired Percentage_Wired MABOnly Percentage_MABOnly