[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

Firewall Log (asa5510) "Hit-Count" Parse

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
irishmic33
Asked:
irishmic33
  • 2
  • 2
1 Solution
 
Danny ChildIT ManagerCommented:
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
 
irishmic33Author Commented:
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
 
irishmic33Author Commented:
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
 
Danny ChildIT ManagerCommented:
yep, you've nailed it.
cheers for the feedback
Danny
0

Featured Post

 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now