Solved

Firewall Log (asa5510) "Hit-Count" Parse

Posted on 2014-02-12
4
604 Views
Last Modified: 2014-02-13
Afternoon Experts,
I'm trying to figure out how to parse a hit count log from my firewall.  This log is a simple hit count dumped from my ASA, but I'm running into trouble parsing the exact info needed.

I'm looking to simply compile a list of incoming IP addresses and then prioritize somehow  by the number of hits.  I thought using MS Excel would be the easiest to group the IP address hits, but I'll use anything free to get the job done.

This log is just a simple dump of  who has been abusing our external IP, and then they are added to a simple black-list.

2014-02-12 14:58:20	Local4.Info	192.168.0.1	Feb 12 2014 14:03:54: %ASA-6-106100: access-list outside_in permitted tcp outside/##.95.44.157(24802) -> inside/###.###.###.###(3389) hit-cnt 8 300-second interval [0x8fe88aaf, 0x0]

Open in new window



My trouble is the inbound IP address isn't a fixed entry.  Which is where my limited experience ends.
0
Comment
Question by:irishmic33
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
Danny Child earned 500 total points
ID: 39854812
OK, some assumptions:
the IP you're looking for in the example is ##.95.44.157
it's always preceded by the / character, and followed by the ( character.

If so, and the text string is in A1, put this in A2, and it should pull it out for you:

=MID(A1,FIND("/",A1)+1,FIND("(",A1)-FIND("/",A1)-1)
Basically, it uses 2 FIND queries to locate the characters above, and then feeds them into a MID formula to extract the text itself

I'm guessing I'm on the right track, not a Cisco log specialist I'm afraid!
If this is right, I'd copy the results to a separate sheet, use a Filter for Unique records, and then a COUNTIF to find the biggest hitters.
0
 
LVL 2

Author Comment

by:irishmic33
ID: 39854852
Nice work,
I didn't think of Mid...  I was trying to figure out different ways with Left and Right.

I'll give it a ride once I'm back in the office.
0
 
LVL 2

Author Comment

by:irishmic33
ID: 39856702
OK, some assumptions:
the IP you're looking for in the example is ##.95.44.157
it's always preceded by the / character, and followed by the ( character.

If so, and the text string is in A1, put this in A2, and it should pull it out for you:

=MID(A1,FIND("/",A1)+1,FIND("(",A1)-FIND("/",A1)-1)
Basically, it uses 2 FIND queries to locate the characters above, and then feeds them into a MID formula to extract the text itself

I'm guessing I'm on the right track, not a Cisco log specialist I'm afraid!
If this is right, I'd copy the results to a separate sheet, use a Filter for Unique records, and then a COUNTIF to find the biggest hitters.

This is exactly what was needed.

So... just to sum it up into steps for others:

Step 1:  Copy text into Excel
Step 2:  Use the formula "=MID(A1,FIND("/",A1)+1,FIND("(",A1)-FIND("/",A1)-1)"
Step 3:  Copy the column and paste values into new sheet.
Step 4:  Advance filter the new column with "Unique Only" and "Copy To" new cell.
Step 5:  Then use the CountIf function and identify range, referencing the new filtered list.

Bam!  Exactly what I needed.   You've helped me save so much time.

Thanks as always!
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 39857558
yep, you've nailed it.
cheers for the feedback
Danny
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question