Monitoring Splunk

Track highest value of distinct count over time.

peiffer
Explorer

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
Explorer

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
Explorer

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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...