Eval a distinct count

[Blu3fish]

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?

[Blu3fish · Accepted solution]

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]

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]

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"

[Blu3fish]

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