topic Re: count of values per event in Splunk Search

count of values per event

suryaavinash — Tue, 29 Sep 2020 18:25:13 GMT

Hi All ,

i have an event as below

Date first seen Duration Proto Src IP Addr:Port Dst IP Addr:Port Packets Bytes Flows
2018-03-06 12:07:31.427 0.002 TCP 10.96.164.13:55796 -> 10.75.77.56:445 3 132 1
2018-03-06 12:07:31.430 0.001 TCP 10.96.164.13:55805 -> 10.75.77.1:445 3 132 1
2018-03-06 12:07:31.431 0.001 TCP 10.96.164.13:55806 -> 10.75.77.1:445 3 220 1
2018-03-06 12:07:34.437 0.001 TCP 10.96.164.13:56129 -> 10.75.77.1:445 3 269 1
2018-03-06 12:07:34.498 0.002 TCP 10.96.164.13:56134 -> 10.75.77.2:445 3 132 1
2018-03-06 12:07:34.500 0.001 TCP 10.96.164.13:56135 -> 10.75.77.2:445 3 220 1
2018-03-06 12:07:37.510 0.000 TCP 10.96.164.13:56489 -> 10.75.77.2:445 3 269 1
2018-03-06 12:07:37.571 0.001 TCP 10.96.164.13:56490 -> 10.75.77.3:445 3 132 1
2018-03-06 12:07:37.573 0.002 TCP 10.96.164.13:56491 -> 10.75.77.3:445 3 220 1
2018-03-06 12:07:40.581 0.003 TCP 10.96.164.13:56863 -> 10.75.77.3:445 3 269 1
2018-03-06 12:07:40.645 0.002 TCP 10.96.164.13:56872 -> 10.75.77.4:445 3 132 1
2018-03-06 12:07:40.646 0.002 TCP 10.96.164.13:56873 -> 10.75.77.4:445 3 220 1
2018-03-06 12:07:43.655 0.001 TCP 10.96.164.13:57193 -> 10.75.77.4:445 3 269 1
2018-03-06 12:07:43.717 0.002 TCP 10.96.164.13:57195 -> 10.75.77.5:445 3 132 1
2018-03-06 12:07:43.719 0.002 TCP 10.96.164.13:57196 -> 10.75.77.5:445 3 220 1
2018-03-06 12:07:46.728 0.001 TCP 10.96.164.13:57575 -> 10.75.77.5:445 3 269 1
...
2018-03-06 12:16:02.280 0.577 TCP 10.96.164.13:49972 -> 10.75.77.240:445 2 104 1
2018-03-06 12:16:03.356 1.014 TCP 10.96.164.13:50104 -> 10.75.77.241:445 3 152 1
2018-03-06 12:16:04.433 0.562 TCP 10.96.164.13:50234 -> 10.75.77.242:445 2 104 1
2018-03-06 12:16:05.509 0.561 TCP 10.96.164.13:50361 -> 10.75.77.243:445 2 104 1
2018-03-06 12:16:06.586 0.576 TCP 10.96.164.13:50489 -> 10.75.77.244:445 2 104 1
2018-03-06 12:16:07.662 0.607 TCP 10.96.164.13:50616 -> 10.75.77.245:445 2 104 1
2018-03-06 12:16:08.741 0.559 TCP 10.96.164.13:50745 -> 10.75.77.246:445 2 104 1
2018-03-06 12:16:09.815 0.577 TCP 10.96.164.13:50835 -> 10.75.77.247:445 2 104 1
2018-03-06 12:16:10.891 0.609 TCP 10.96.164.13:50966 -> 10.75.77.248:445 2 104 1
2018-03-06 12:16:11.968 0.998 TCP 10.96.164.13:51096 -> 10.75.77.249:445 3 152 1
2018-03-06 12:16:13.044 1.014 TCP 10.96.164.13:51225 -> 10.75.77.250:445 3 152 1
2018-03-06 12:16:14.121 0.578 TCP 10.96.164.13:51356 -> 10.75.77.251:445 2 104 1
2018-03-06 12:16:15.196 0.998 TCP 10.96.164.13:51484 -> 10.75.77.252:445 3 152 1
2018-03-06 12:16:16.273 0.515 TCP 10.96.164.13:51623 -> 10.75.77.253:445 2 104 1
2018-03-06 12:16:17.349 0.546 TCP 10.96.164.13:51751 -> 10.75.77.254:445 2 104 1
2018-03-06 12:16:18.536 0.530 TCP 10.96.164.13:51879 -> 10.75.52.94:445 2 104 1
2018-03-06 12:16:19.658 0.999 TCP 10.96.164.13:52009 -> 10.75.41.195:445 3 152 1
2018-03-06 12:16:20.782 0.576 TCP 10.96.164.13:52142 -> 10.75.33.196:445 2 104 1
2018-03-06 12:16:21.913 0.561 TCP 10.96.164.13:52272 -> 10.75.249.84:445 2 104 1
2018-03-06 12:16:23.029 0.000 TCP 10.96.164.13:52403 -> 10.75.22.193:445 1 52 1
2018-03-06 12:16:24.158 0.000 TCP 10.96.164.13:52531 -> 10.75.137.51:445 1 52 1
2018-03-06 12:16:25.280 0.515 TCP 10.96.164.13:52659 -> 10.75.207.231:445 2 104 1
2018-03-06 12:16:26.408 0.000 TCP 10.96.164.13:52791 -> 10.75.152.227:445 1 52 1

I need the count of each port in the event.

index=* 1520558807000 | rex field=_raw max_match=0 "([[ipv4]])" | rex field=_raw max_match=0 "(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}:\d{1,5})" | rex field=IP_add "(?[^:]+):(?\d+)" |eval eventportcnt=mvcount(dst_port) | where eventportcnt >10 |stats values(dst_port) values(eventportcnt)

The above query gives me the total count of different ports in the event. i am expecting the below output.

Port count
445 40
55796 1

Please help........

Re: count of values per event

p_gurav — Tue, 13 Mar 2018 09:33:31 GMT

HI,

Can you try something like:

    index=* 1520558807000 | rex field=_raw max_match=0 "([[ipv4]])" | rex field=_raw max_match=0 "(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}\:\d{1,5})" | rex field=IP_add "(?[^:]+):(?\d+)" |eval eventportcnt=mvcount(dst_port) | where eventportcnt >10 |stats  values(eventportcnt) by dst_port

Re: count of values per event

bangalorep — Tue, 13 Mar 2018 09:40:01 GMT

Hello!
Try replacing the last - stats part of your query with this

| stats count by dst_port

Re: count of values per event

niketn — Tue, 13 Mar 2018 09:47:35 GMT

@suryaavinash, can you please explain your required output a bit more as to what you want to capture as count?

I see one Destination Port 445 with count 40 and one Source Port with count 1? Is there a correlation between the source and destination that you want to establish?

Re: count of values per event

suryaavinash — Tue, 13 Mar 2018 10:19:11 GMT

hi niket,

When we do a regex , it gets me all the IP's and Port's.
if you see the _raw event above , it has around 40 IP's with port 445 and server IP's(40) with 1 Port.

When i am doing a mvcount(dst_port) , i am getting a total count of 80 . what i am expecting is individual count of port's for a single event like

Port count
445 40
55796 1
52791 1
.......
........

Thanks for the help,
Surya

Re: count of values per event

suryaavinash — Tue, 13 Mar 2018 10:21:41 GMT

I remember doing this , whats happening is
if eventportcnt =80 then that is being mapped for all the Ports

Port count
445 80
55796 80
52791 80

I will try it once again tomorrow and update you in case it works . Thanks for helping.

Re: count of values per event

suryaavinash — Tue, 13 Mar 2018 10:23:04 GMT

Stats count by dst_port gets you the result from all the events and not from the specific event.

In my case i want the count of ports in a single event. Thanks for helping.

Re: count of values per event

p_gurav — Tue, 13 Mar 2018 10:38:33 GMT

This works fine for me:

index="test111"  | rex field=_raw max_match=0 "([[ipv4]])" | rex field=_raw max_match=0 "(?<IP_add>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\:\d{1,5})" | rex field=IP_add "(?<ip_1>[^:]+):(?<dst_port>\d+)" | stats count by dst_port

Re: count of values per event

suryaavinash — Tue, 13 Mar 2018 11:16:18 GMT

this gives the result for the entire index. i want the cunt for single event. Same answer was advised below 😞

Thanks

Re: count of values per event

p_gurav — Tue, 13 Mar 2018 11:54:02 GMT

Try this if you want result in 1 row:

    index="test111"  | rex field=_raw max_match=0 "([[ipv4]])" | rex field=_raw max_match=0 "(?<IP_add>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\:\d{1,5})" | rex field=IP_add "(?<ip_1>[^:]+):(?<dst_port>\d+)" | stats count AS dst_count by dst_port | stats list(dst_count) list(dst_port)

It will combine result and display in 1 row. Let me know if you need anything?

Re: count of values per event

vinod94 — Tue, 13 Mar 2018 11:59:22 GMT

You can try this,

index=* 1520558807000 | rex field=_raw max_match=0 "\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\:(?<port>[^\s]+)" | stats count by port

Re: count of values per event

suryaavinash — Tue, 29 Sep 2020 18:26:22 GMT

Doesnt work 😞

list(dst_count) list(dst_port)
160 445
4 49972
4 50104
4 50234
.....
.......

160 is the count of occurrence of 445 in the index on all the events. The same result as stats count by dst_port.The expectation is

445 40
49972 1
445 40
49972 1

i want the count per event. The issue is:
single host( 10.96.164.13) is trying to ping several hosts on a single port(445) to spread Malware. I am unable to get any specific pattern out of this . so i am going with the count of ports per event and alerting such incidents.

Re: count of values per event

suryaavinash — Tue, 13 Mar 2018 22:34:37 GMT

Stats count by dst_port gets you the result from all the events and not from the specific event.

In my case i want the count of ports in a single event. Thanks for helping.