cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
jfi
Level 8
Report Inappropriate Content
Message 1 of 3

ESM Reporting Source/Destination IP address pairs

Jump to solution

Hi,
I'm trying to create a report with a Top of Blocked connections. The SIEM receives all FW logs via syslog. (we don't have netflow)

I'm able to generate a report in this style
"report FW Blocked connections"
----------------------
"Source IP" - "Destination IP"
1.1.1.1 - 2.2.2.2
1.1.1.1 - 2.2.2.2
1.1.1.1 - 2.2.2.2
1.1.1.1 - 2.2.2.2
1.1.1.1 - 2.2.2.3
1.1.1.1 - 2.2.2.3
1.1.1.1 - 2.2.2.3

However, we would like to have a report with a count of source/destination IP address pairs (a top 20 for example).

Like this:
"report FW Blocked connections"
----------------------
"Source IP" - "Destination IP" -  "Count"
1.1.1.1 - 2.2.2.2 - 4
1.1.1.1 - 2.2.2.3 - 3

In the Query Wizard I'm using a "Reporting Query" called srcip-dstip, (I couldn't find any usefull "Event Queries" for this ), but there is no option in the Wizard to have the report sort & summarize on the srcip-dstip combination. Only on the separate source IP or destination IP field.

Does anybody know a way to do this?

With Kind regards,
Joeri

1 Solution

Accepted Solutions
Reliable Contributor brenta
Reliable Contributor
Report Inappropriate Content
Message 2 of 3

Re: ESM Reporting Source/Destination IP address pairs

Jump to solution

The query you are looking for does not exist in the default built in set, you will need to create a custom Reporting Query. (This can only be done in a reporting query and cannot be attached to a dash board.)

Be aware, running this over large ranges can have significant performance impacts. Each SELECT statement can only use one index, the query optimizer will likely use last event time, so it cannot use other indexes like, Event Subtype, SrcIP or DstIP to assist in the grouping. In simple terms this means the database will likely scan the entire Alert table for any date range you specify, regardless if the traffic is blocked or not. So, if you have a bunch of non-firewall traffic, like Windows AD file share activtiy, this will still all be evaulated by the query for possible inclusion.

Create a Reporting query that looks like this;

Screen Shot 2019-02-06 at 10.05.23 AM.png

The Fields selected should look like this with grouping on Source and Destination IP;

Screen Shot 2019-02-06 at 10.05.31 AM.png

 

Brent
2 Replies
Reliable Contributor brenta
Reliable Contributor
Report Inappropriate Content
Message 2 of 3

Re: ESM Reporting Source/Destination IP address pairs

Jump to solution

The query you are looking for does not exist in the default built in set, you will need to create a custom Reporting Query. (This can only be done in a reporting query and cannot be attached to a dash board.)

Be aware, running this over large ranges can have significant performance impacts. Each SELECT statement can only use one index, the query optimizer will likely use last event time, so it cannot use other indexes like, Event Subtype, SrcIP or DstIP to assist in the grouping. In simple terms this means the database will likely scan the entire Alert table for any date range you specify, regardless if the traffic is blocked or not. So, if you have a bunch of non-firewall traffic, like Windows AD file share activtiy, this will still all be evaulated by the query for possible inclusion.

Create a Reporting query that looks like this;

Screen Shot 2019-02-06 at 10.05.23 AM.png

The Fields selected should look like this with grouping on Source and Destination IP;

Screen Shot 2019-02-06 at 10.05.31 AM.png

 

Brent
jfi
Level 8
Report Inappropriate Content
Message 3 of 3

Re: ESM Reporting Source/Destination IP address pairs

Jump to solution

Thanks Brent, that was exactly what I was looking for. (I didn't notice any impact, the reports don't take longer to generate.)

Regards,

Joeri

More McAfee Tools to Help You
  • Subscription Service Notification (SNS)
  • How-to: Endpoint Removal Tool
  • Support: Endpoint Security
  • eSupport: Policy Orchestrator
  • Community Help Hub

      New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.

    • Find Forum FAQs
    • Learn How to Earn Badges
    • Ask for Help
    Go to Community Help

    Join the Community

      Thousands of customers use the McAfee Community for peer-to-peer and expert product support. Enjoy these benefits with a free membership:

    • Get helpful solutions from McAfee experts.
    • Stay connected to product conversations that matter to you.
    • Participate in product groups led by McAfee employees.
    Join the Community
    Join the Community