Splunk Search

Eval a distinct count

Blu3fish
Path Finder

I've been trying to determine the # of free dhcp leases.

I can calculate the total current leases with:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i).*? (?P\d+.\d+.\d+.\d+)(?= )" | chart dc(src_ip)

But when I pipe this into an eval statement to subtract the dc of src_ip from the total # of free leases I always wind up with 0. Which means I'm doing something wrong.

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i).*? (?P\d+.\d+.\d+.\d+)(?= )" | chart dc(src_ip) | eval freeleases = 100 - src_ip | stats c(freeleases) as "Free Leases"

Also tried utilizing a subsearch but I receive an error that dc can't be used w/the eval function:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i).? (?P\d+.\d+.\d+.\d+)(?= )" | eval freeleases = 100 - [search src_ip= | chart dc(src_ip)] | stats c(freeleases) as "Free Leases"

Can the wise splunk jedis help this padawan learner?

1 Solution

Blu3fish
Path Finder

eventstats was the right direction. But when we c(freeleases) it was counting every instance of distinctCount as freeleases. To fix this I just changed

stats c(freeleases) as "Free Leases"

to

stats max(freeleases) as "Free Leases"

so the final search, for all interested parties:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i)lease (?P[^ ]+)" | dedup lease_ip | search lease_ip=192.168.201* | eventstats dc(lease_ip) as "distinctCount" | eval freeleases = 100 - distinctCount | stats max(freeleases) as "Free Leases"

(I'm specifically curious about the 192.168.201 subnet above, you can nix that bit and search over all your subnets)

so problem solved! Thank you acdevlin for your help. W/o that mention of eventstats I would've been stuck in the mud.

Cheers

View solution in original post

Blu3fish
Path Finder

eventstats was the right direction. But when we c(freeleases) it was counting every instance of distinctCount as freeleases. To fix this I just changed

stats c(freeleases) as "Free Leases"

to

stats max(freeleases) as "Free Leases"

so the final search, for all interested parties:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i)lease (?P[^ ]+)" | dedup lease_ip | search lease_ip=192.168.201* | eventstats dc(lease_ip) as "distinctCount" | eval freeleases = 100 - distinctCount | stats max(freeleases) as "Free Leases"

(I'm specifically curious about the 192.168.201 subnet above, you can nix that bit and search over all your subnets)

so problem solved! Thank you acdevlin for your help. W/o that mention of eventstats I would've been stuck in the mud.

Cheers

acdevlin
Communicator

I'm not exactly a Splunk guru myself and I don't currently live in a mud hut on Dagobah, but I do have a suggestion. Try using "eventstats" instead and saving the distinct count as its own field

index=os host=dhcp source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i).? (?P<src_ip>d+.d+.d+.d+)(?= )" | eventstats dc(src_ip) as "distinctCount" | eval freeleases = 100 - distinctCount | stats c(freeleases) as "Free Leases"

Also, depending on exactly what you're trying to see from the field "freeleases", you might want values() or sum() instead of count().

acdevlin
Communicator

I'm thinking the "stats" section right at the end is the problem. Try using values() instead of count() to display freeleases and see if anything comes out:

| eval freeleases=100-distinctCount | stats values(freeleases) as "Free Leases"
0 Karma

Blu3fish
Path Finder

Changed my rex up a bit and while:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i)lease (?P[^ ]+)" | dedup lease_ip | search lease_ip=192.168.201* | eventstats dc(lease_ip) as "distinctCount"

does return the correct # of leased IPs

| eval freeleases = 100 - distinctCount | stats c(freeleases) as "Free Leases"

returns the same result of leased IPs

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...