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

[majek81]

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!

[woodcock · Accepted solution]

Like this:

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

[majek81]

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

[woodcock]

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.

[woodcock]

Add this:

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

[majek81]

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?

[richgalloway]

You're close. Try

|stats values(ip) as Affected_IPs
count(Ip) as Ip_Count
values(*) as *
by xref

[majek81]

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.

[richgalloway]

Try my updated answer.