SQL query on Postgress

Screaming Monkey · 15-01-2007 4:50pm #1

I have Postgres 8.1.4 on Ubuntu linux, with one table called attacks

Database has the following fields and data
id source dport
1 10.1.1.1 443
2 10.1.1.1 443
3 10.1.1.1 22
4 10.1.1.1 99
5 10.1.1.2 443
6 192.168.1.1 25
7 192.168.1.1 25

Was looking for a SQL query that could output the following.
source dport scans
10.1.1.1 443 2
10.1.1.1 22 1
10.1.1.1 99 1
192.168.1.1 25 2
10.1.1.2 443 1

It counts the number of occurances of a particular port for each source ip, but also sorting by the ip that has the largest number of total scans, so in the case of 10.1.1.1 its has a total of 4 scans and it appears first.

Can't figure out how to sort by the total scans for each ip, the query

"SELECT attacks.source, attacks.dport, count(*) AS scans FROM attacks GROUP BY attacks.source, attacks.dport order by source;

will show the correct data, but not sort by total dport per ip.

PhantomBeaker · 15-01-2007 5:54pm

Ok, I'm no sql expert, but I think you're on the right track, but might be overcomplicating things a bit (there again this might be too simple - I'm not in front of a db right now to check this):

SELECT attacks.source, attacks.dport, count(attacks.dport) AS scans
FROM attacks
GROUP BY attacks.source
ORDER BY attacks.source;

This any help at all?
Aoife

Screaming Monkey · 15-01-2007 6:08pm

Thanks Aoife, but postgres is complaining...

ERROR: column "attacks.dport" must appear in the GROUP BY clause or be used in an aggregate function

PhantomBeaker · 16-01-2007 10:46am

Hmmm...

in which case change the GROUP BY clause to "GROUP BY attacks.source, attacks.dport", but you've probably already tried that.

amen · 16-01-2007 3:15pm

what is the datatype of the source column?
I tried the SQL below and it works in MS SQL 2000

try order by 1

bonkey · 16-01-2007 3:38pm

If I understand the problem correctly, its quite complex.

You want to calculate a total per IP, and a total per IP, per dport.
You then want to generate your output with the highest perIP totals first, and within an IP, you want to present the records per dport.....correct?

So if you have the following :

10 hits on 192.168.0.9 dport 1,
9 hits on 192.168.0.2, dport 1
2 hits on 192.168.0.9 dport 2

then you want yoru output presented as :

192.168.0.9 1 10
192.168.0.9 2 02
192.168.0.2 1 09

If this is correct, then the solutions being suggested thus far won't do it. I'm unfortunately not au fait with Postgres to be able to tell you what will do it. Without having something like the Analytic Functions which Oracle has, I think it will involve using either sub-selects and/or joins, and a bit more to boot.

I'd be happy to be wrong though

amen · 16-01-2007 9:34pm

have you tried order by count(source) ?

bonkey · 17-01-2007 10:52am

Won't work.

If you group over source & dport, then *all* grouping functions will group over the two values.

If you group over source only, then you can't (easily) calculate the totals for source/dport.

Basically, the problem as described requires two layers of aggregation.

SQL query on Postgress

Comments