Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

count of values per event

suryaavinash
Explorer
‎03-12-2018 11:17 PM

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........

0 Karma
Reply

vinod94
Contributor
‎03-13-2018 04:59 AM

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
Reply

suryaavinash
Explorer
‎03-13-2018 03:34 PM

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.

0 Karma
Reply

niketn
Legend
‎03-13-2018 02:47 AM

@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?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Reply

suryaavinash
Explorer
‎03-13-2018 03:19 AM

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

0 Karma
Reply

bangalorep
Communicator
‎03-13-2018 02:40 AM

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

| stats count by dst_port
0 Karma
Reply

suryaavinash
Explorer
‎03-13-2018 03:23 AM

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.

0 Karma
Reply

p_gurav
Champion
‎03-13-2018 02:33 AM

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
0 Karma
Reply

suryaavinash
Explorer
‎03-13-2018 03:21 AM

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.

0 Karma
Reply

p_gurav
Champion
‎03-13-2018 03:38 AM

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
0 Karma
Reply

suryaavinash
Explorer
‎03-13-2018 04:16 AM

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

Thanks

0 Karma
Reply

p_gurav
Champion
‎03-13-2018 04:54 AM

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?

0 Karma
Reply

suryaavinash
Explorer
‎03-13-2018 03:33 PM

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.

0 Karma
Reply
Get Updates on the Splunk Community!

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...