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.
My trouble is the inbound IP address isn't a fixed entry. Which is where my limited experience ends.
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]My trouble is the inbound IP address isn't a fixed entry. Which is where my limited experience ends.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("/",A 1)-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,FI
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!
yep, you've nailed it.
cheers for the feedback
Danny
cheers for the feedback
Danny
ASKER
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.