Search to display port scan attempts

[bend_bi]

I'm having a hard time developing the query to display this, but in short, I want to write a query that will display all ports that an attacking IP has scanned on a victim IP. In short, my data structure looks like this:

src_ip                          dest_ip         dest_port
1.1.1.1 attempted to connect to 2.2.2.2 on port 3389
1.1.1.1 attempted to connect to 2.2.2.2 on port 3306
1.1.1.1 attempted to connect to 2.2.2.2 on port 22
1.1.1.1 attempted to connect to 3.3.3.3 on port 22
1.1.1.1 attempted to connect to 4.4.4.4 on port 3389
1.2.1.1 attempted to connect to 2.2.2.2 on port 22

Where src_ip, dest_ip, and dest_port are already indexed fields from my firewall logs. I'm wanting to create a query that has output that looks like this:

1.1.1.1       2.2.2.2      22
                        3389
                        3306
1.1.1.1    3.3.3.3      22
1.1.1.1       4.4.4.4      3389
1.2.1.1    2.2.2.2      22

I think a stats values() is involved, but from what I've played around with, I can't get the output to display in the format that I need.

[DalJeanis · Accepted solution]

try this

Your query that pulls the attempts
| stats  list(dest_port) as dest_port by src_ip dest_ip         

or

| stats  values(dest_port) as dest_port by src_ip dest_ip         

values() will sort and dedup, list() will give them in the order they appear in the data (most recent first).

[zeeshantayyab]

Hi,

Why this query is not working in my environment.

[bend_bi]

Works perfectly, thanks!