Report to show count of unique combinations

Discussion created by RSA Admin

on Sep 5, 2008
Latest reply on Sep 5, 2008 by RSA Admin

Like • Show 0 Likes0
Comment • 5

Hi folks,

I've been having some difficulties trying to setup a report that will only show a count of unique combinations of two fields. Doing a count works for one field, but I can't seem to combine it to show combinations of two fields.

For instance, I would like to create a report that will dump all incoming firewall traffic, but show a distinct count of how many attacks were from the same source on the same port. I can do a count(ForeignAddress) to get a count of how many attacks came from each source, and I can do a count(LocalPort) to see how many attacks were directed at a certain port, but I would like to do a count of both in tandem with each other.

Here is some sample data:

DATE FOREIGNADDRESS PORT

2008/09/04 1.1.1.1 25

2008/09/04 1.1.1.1 443

2008/09/04 2.2.2.2 53

2008/09/04 3.3.3.3 25

I would want the report to show:

FOREIGNADDRESS PORT COUNT

1.1.1.1 25 2

1.1.1.1 443 1

2.2.2.2 53 2

3.3.3.3 25 2

Is this something that is doable?

Thanks in advance!

Bash

Outcomes

RSA Admin

Sep 5, 2008 10:18 AM

bashiri,

I'm assuming you are using the Firewall Accounting table. If so, try using the count(DISTINCTForeignAddress) and count(DISTINCT ForeignPort). Let me know how you make out.

Actions

RSA Admin

@ RSA Admin on Sep 5, 2008 10:33 AM

Hey there,

Thanks for the response. Yes, I am indeed using the Firewall Accounting table. Unfortuantely, when I add the two distinct counts you mentioned, all that it gives me is the following:

DATE FOREIGNADDRESS PORT COUNT(DISTINCT FOREIGN ADDRESS) COUNT(DISTINCT LOCALPORT)

2008/09/04 1.1.1.1 25 1 1

2008/09/04 1.1.1.1 443 1 1

2008/09/04 2.2.2.2 53 1 1

2008/09/04 3.3.3.3 25 1 1

It pretty much just counts every single event individually.

Any ideas?

Thanks again.

Actions

RSA Admin

@ RSA Admin on Sep 5, 2008 10:49 AM

You need to remove the timestamp field.

By including the timestamp, you are forcing every record to display individually, thus the counts will always show up as 1.

You also only want to display one count field.

Actions

RSA Admin

@ RSA Admin on Sep 5, 2008 11:22 AM

Thanks to both of you for the responses.

Removing the timestamp field was the problem. I see now how that makes every record display individually. After removing the timestamp field, BlackMonkey's suggested report idea worked perfectly.

Since this is a weekly report, I think I'll have to at the very least include the date, but not the time. At least that way, it will be lumped by day but shouldn't present the counting problem incurred when including the time right down to the second.

Thakns again to both of you for the prompt help!

Actions

RSA Admin

@ RSA Admin on Sep 5, 2008 10:53 AM

I see. I misread the request. Try this, select the ForeignAddress, ForeignPort, count(ForeignPort). Sort it by ForeignAddress. For each ip that has a different port, it will list at a different line.

It should give you the result you need. Let me know how you make out.

Actions

5 Replies

RSA Admin Sep 5, 2008 10:18 AM
bashiri,
I'm assuming you are using the Firewall Accounting table. If so, try using the count(DISTINCTForeignAddress) and count(DISTINCT ForeignPort). Let me know how you make out.
Like • Show 0 Likes0
Actions
- RSA Admin @ RSA Admin on Sep 5, 2008 10:33 AM
  Hey there,
  
  Thanks for the response. Yes, I am indeed using the Firewall Accounting table. Unfortuantely, when I add the two distinct counts you mentioned, all that it gives me is the following:
  
  DATE    FOREIGNADDRESS    PORT       COUNT(DISTINCT FOREIGN ADDRESS)    COUNT(DISTINCT LOCALPORT)
  2008/09/04 1.1.1.1            25                      1                                                          1
  2008/09/04 1.1.1.1            25                      1                                                          1
  2008/09/04 1.1.1.1            443                    1                                                          1
  2008/09/04 2.2.2.2            53                      1                                                          1
  2008/09/04 2.2.2.2            53                      1                                                          1
  2008/09/04 3.3.3.3            25                      1                                                          1
  2008/09/04 3.3.3.3            25                      1                                                          1
  
  It pretty much just counts every single event individually.
  
  Any ideas?
  
  Thanks again.
  Like • Show 0 Likes0
  Actions
  - RSA Admin @ RSA Admin on Sep 5, 2008 10:49 AM
    You need to remove the timestamp field.
    
    By including the timestamp, you are forcing every record to display individually, thus the counts will always show up as 1.
    
    You also only want to display one count field.
    Like • Show 0 Likes0
    Actions
    - RSA Admin @ RSA Admin on Sep 5, 2008 11:22 AM
      Thanks to both of you for the responses.
      
      Removing the timestamp field was the problem. I see now how that makes every record display individually. After removing the timestamp field, BlackMonkey's suggested report idea worked perfectly.
      
      Since this is a weekly report, I think I'll have to at the very least include the date, but not the time. At least that way, it will be lumped by day but shouldn't present the counting problem incurred when including the time right down to the second.
      
      Thakns again to both of you for the prompt help!
      Like • Show 0 Likes0
      Actions
  - RSA Admin @ RSA Admin on Sep 5, 2008 10:53 AM
    I see. I misread the request. Try this, select the ForeignAddress, ForeignPort, count(ForeignPort). Sort it by ForeignAddress. For each ip that has a different port, it will list at a different line.
    
    It should give you the result you need. Let me know how you make out.
    Like • Show 0 Likes0
    Actions

Report to show count of unique combinations

Outcomes

Related Content

Recommended Content