Splunk Search

How to add up the number of IPs in a row and output the count into a field within the same table

majek81
New Member

Good morning to all,

I want to add up the IPs in each row under the Affected_IPs field and output the count into the Ip_Count field of the associated row. Essentially, I want a new field (Ip_Count) with the count of the IPs of the Affected_IPs field &/ rows to display with all of the corresponding data within the resulting table.
My Ip_Count field comes up empty.

Base search:

|stats values(ip) as Affected_IPs
values(Ip_Count) as Ip_Count
by xref

I have an Excel image for visual aid.
Thanks in advance!

alt text

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

... | stats values(ip) AS Affected_IPs dc(Ip_Count) AS Ip_Count BY xref

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

... | stats values(ip) AS Affected_IPs dc(Ip_Count) AS Ip_Count BY xref

View solution in original post

0 Karma

majek81
New Member

Thanks again but this didn't work, the Total field coming up empty or output 0. The solution I entered works apart from the double output which I was able to
...|stats values(ip) as Affected_IPs count as Ip_Count values(Affected_IPs)

then:
|fieldformat Ip_Count=Ip_Count/2

0 Karma

woodcock
Esteemed Legend

This is not making sense; we really should keep going and figure out why it is doubling and do it "right". Please post a few raw events to look at.

0 Karma

woodcock
Esteemed Legend

Add this:

... | eventstats dc(Affected_IP) AS Ip_Count BY xref
0 Karma

majek81
New Member

Thank you for this, I have tried this before and it did not work, the Ip_Count output was empty while all other field populate with data. I don't know if the placement of you code as any bearing on the outcome.
However, I have similar code which works:

...|stats values(ip) as Affected_IPs count as Total values(Affected_IPs)

But now the output of the count is doubled in the Ip_Count field. For instance, if Splunk finds one ip, the IP_Count field and rows counts output is two.

My next question is if you or any, know how to produce a true count doesn't double my actual ips count in the Affected_IP field?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You're close. Try

|stats values(ip) as Affected_IPs
count(Ip) as Ip_Count
values(*) as *
by xref
---
If this reply helps you, an upvote would be appreciated.
0 Karma

majek81
New Member

Thank you for this, I have tried this before and it did not work. Maybe I should make myself a little more clear.
My table contains ip and other fields, I want to count the individual ip grouping for each row and put the resulting count back into the table as a Ip_Count. I hope that explanation provide more insight to my query.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try my updated answer.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.