Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi all! We have been experiencing an issue on site where threads have been missing the latest postings. The platform host Vanilla are working on this issue. A workaround that has been used by some is to navigate back from 1 to 10+ pages to re-sync the thread and this will then show the latest posts. Thanks, Mike.
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

SQL query on Postgress

  • 15-01-2007 4:50pm
    #1
    Registered Users, Registered Users 2 Posts: 218 ✭✭


    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.


Comments

  • Registered Users, Registered Users 2 Posts: 304 ✭✭PhantomBeaker


    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


  • Registered Users, Registered Users 2 Posts: 218 ✭✭Screaming Monkey


    Thanks Aoife, but postgres is complaining...

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


  • Registered Users, Registered Users 2 Posts: 304 ✭✭PhantomBeaker


    Hmmm...

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


  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


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

    try order by 1


  • Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭bonkey


    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 :)


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    have you tried order by count(source) ?


  • Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭bonkey


    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.


Advertisement