topic Re: How to show Value and Count in a subsearch? in Splunk Search

How to show Value and Count in a subsearch?

deathbyearthwor — Tue, 11 Oct 2016 05:12:10 GMT

My goal is to look at firewall data and pull the top 10 Blocked IPs along with the incoming ports they were hitting. So I want the IP, then all ports on that IP, and the count of the ports. Here is what I have so far

host=10.2.2.1 filterlog [search host=10.2.2.1 filterlog | top Source_IP | table Source_IP] | stats values(Source_IP) as IP, values(Destination_Port) as Ports by Source_IP | table IP, Ports

It works for IP with a list of ports but I cannot get a count of the ports. Here is what it spits out

IP                Port

10.2.2.183        443
                    80
                    993
10.2.2.22       443
                    80
112.138.14.244    51413
119.247.54.238    51413
120.75.230.2        51413
178.93.32.248      51413
180.127.81.72       51413
36.228.5.180        51413

And I want something more like this

IP                  Port        Count

10.2.2.183          443         22
                      80        25
                      993       148
10.2.2.22            443        7486
                      80        454545
112.138.14.244      51413       14
119.247.54.238      51413      54
120.75.230.2          51413     11
178.93.32.248        51413      1
180.127.81.72        51413      45  
36.228.5.180          51413     454

Re: How to show Value and Count in a subsearch?

sundareshr — Tue, 11 Oct 2016 15:53:57 GMT

Try this

host=10.2.2.1 filterlog [search host=10.2.2.1 filterlog | top Source_IP | table Source_IP] | stats count by Source_IP Destination_Ports | stats list(Destination_Ports) as Ports list(count) as Count by Source_IP | rename Source_IP AS IP

Re: How to show Value and Count in a subsearch?

deathbyearthwor — Tue, 11 Oct 2016 16:35:56 GMT

That worked perfectly! Now it doesn't show the data on the column chart like I'd want to. If I tell it to stack with that data it stacks the port number itself and then the count. I want the port numbers to be in the legend of the graph but not treat it as a number to add to the graph.

Re: How to show Value and Count in a subsearch?

sundareshr — Tue, 11 Oct 2016 16:41:48 GMT

If you want to show it in a chart, try this

host=10.2.2.1 filterlog [search host=10.2.2.1 filterlog | top Source_IP | table Source_IP] | stats count by Source_IP Destination_Ports | eval Ports=Source_IP."::".Destination_Ports | table Ports count

Re: How to show Value and Count in a subsearch?

deathbyearthwor — Tue, 11 Oct 2016 17:06:14 GMT

That looks like it combined the IP and the port into one string and each column is a single IP:Port pair. I'd like each column to be a single ip but a stacked graph showing a breakdown of each port by the IP address. Thanks for your help.

Re: How to show Value and Count in a subsearch?

sundareshr — Tue, 11 Oct 2016 17:18:04 GMT

Sorry mis-understood. Try this

host=10.2.2.1 filterlog [search host=10.2.2.1 filterlog | top Source_IP | table Source_IP] | chart count over Source_IP by Destination_Ports

Re: How to show Value and Count in a subsearch?

deathbyearthwor — Tue, 11 Oct 2016 17:22:55 GMT

That did it! Thank you very much. I must say I really like the community here.