Monitoring Splunk

Track highest value of distinct count over time.

peiffer
Path Finder

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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.

View solution in original post

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

peiffer
Path Finder

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

Tim

0 Karma

HiroshiSatoh
Champion

↓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
0 Karma

peiffer
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...