Track highest value of distinct count over time.

[peiffer]

I would like to summarize the count of distinct iMAC addresses seen per hour, chart and also insert a column of the maximum value of the distinct count over a period of time in order to analyze a lease pool. I am using chart to summarize by hour and eventstats to track maximums over a given day.

index=network_access "DHCP*"
| lookup dnslookup clientip as relay OUTPUT clienthost as relay_agent
| chart limit=0 dc(mac) as distinct_mac over relay_agent by date_hour
| eventstats max(distinct_mac) as max_mac by date_mday
| table relay_agent max_mac 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

My new field from max() doesn't show up, and the field max_mac doesn't appear to exist. Is there a better way to do this compounding?

Tim

[woodcock · Accepted solution]

Replace your eventstats line with this:

| eval max_mac=0 | foreach * [eval max_mac=max(max_mac, tonumber($<<FIELD>>$))]

Your eventstats command cannot work because you dropped the date_month field when you ran the chart command.

[peiffer]

Brute force, but simple.. I like the method. It reminds me of what I did in other programming languages.

Tim

[HiroshiSatoh]

↓It is not displayed because date_mday does not exist.
| chart limit=0 dc(mac) as distinct_mac over relay_agent by date_hour

Is the extraction period one day?

If only for one day

 index=network_access "DHCP*"
 | lookup dnslookup clientip as relay OUTPUT clienthost as relay_agent
 | chart limit=0 dc(mac) as distinct_mac over relay_agent by date_hour
 | addtotals
 | eventstats max(Total) as max_mac
 | table relay_agent max_mac 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

[peiffer]

Yes, the extraction period is one day. Chart only allows one criterion or 'by' value.

The requirement is to provide the highest value of distinct_mac for a given relay agent.

I think that the method you offer is the total over all of the relay agents per chart time period ( 1 hour ). Using your method, the max_mac is identical over all of the relay_agents. I think I might be confused