Worksheet analysis and data manipulation

[chieftan]

I will try and explain as best I can.

I have been given the task of cleaning up a firewall configuration that contains hundreds and hundreds of policies over a period of time. The first thing I need to do to achieve this is to work out the required services and their zones and to and from points.

I have completed the actual sorting in Excel, and now, if I need to achieve the desired results I will have to manually go through each entry, this could take weeks or months and time is not on our side for this project.

So, here is what I would manually have to do:

1: Get the /32 IP Address (not a problem).
2: Search for that address on the second worksheet with the completed policies (including all addresses and services per policy)
3: Once found, note the policy ID, the services and all addresses.

Now here is the issue

4: I then have to search for the next instance of the IP address and note the same details again.
5: And again as per 3 and 4 above.
6: Once all have been found, I then have to look at all the services and networks and addresses to see what is replicated and remove what is replicated.

What I really need is some help with a user inpput box I can enter the IP required. Then a button of some sort (OK) I guess, and it finds that IP address and the associated services and networks etc etc.....

The columns are listed as follows:

Policy ID (Column D) , From Zone (Column E), To Zone (Column F), Src Address (Column G), Dst Address (Column H) , Services (Column I) , Action (Permit, deny, etc) (Column J).

The reason for this is that I know a lot of /32 masked addresses are going to be included with the same services and maybe even the same to and from zones as an actual network address.... well that's pointless.....

Any help would be greatly appreciated.

Thanks

Clive

 

[SkipVought]

Hi,

Again, very nebulous! If you want help, we will need a LOT more specific information, like sample tables of related data that you have worked through with sample solutions that can then perhaps be generalized to design a more encompassing solution.

Nothing you have supplied so far is close to what is necessary to provide any help. For instance, "3. Once found, note the policy ID, the services and all addresses."

Okay, "noted!" So what does that entail, after it has been noted?

Removing duplicated is pretty simple using a feature in the Data TAB. But then it may not be that simple unless ALL the data in ALL the columns is exactly the same.

It would probably be a good idea to upload your workbook containing all your data tables. then we could talk more intelligently and actually play with the data to help in a solution.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[chieftan]

Hi SkipVought,

Unfortunately, there is no chance of uploading the table as it is the firewall rulebase......

I have managed to get around the issue in a messy way but it seems to do the trick.

The table headings as I mentioned above have the Policy ID..... The main problem was that when entering the desired IP in either the Source address or destination address (through filtering) it would bring up entries where the Policy was not known. And the reason for that was as follows (imagine the three columns as per below:

Policy ID Src Address Dst Address
3 192.168.1.2 10.20.30.40
10.20.30.41
10.20.30.42
5 172.16.20.20 192.168.10.0/24
192.168.50.0/24
6 etc

The issue would be the gaps in the Policy ID (if the address did not reside in the same row as the ID)....... The way I got around it was simply to copy the policy ID to every line. Now I know the policy I can go to that policy and see the Services....... Nothing can be deleted as the next part has to be completed manually. I can see no way of automatically detcting the following:

Is the current /32 IP address in a rule allowing the same services with the same from and to zone and also the same destination addresses within that zone...... this is a manual task and will take a while but I can see no other way of matching all this date through several policies.... the other section is straightforward but will take time, for example where the following services seem to be repeated:

ICMP ANY
ICMP PING
PING
TRACEROUTE

The bottom three of that list are covered by the top one so can be removed, IF, there is no specific reason within the rulebase they are there.......

Thank you for your help and advice though. It is appreciated.

Thanks