Configuration Parser Spreadsheet

[jaclaz]

Not an article, but possibly useful.

Very often, when reviewing the export of the full configuration some forum users post for review or check, I find extremely difficult to read it, particularly when it is not posted enclosed in “code” tags.

Even when the proper formatting is used, I have difficulties in parsing multi-line settings.

So I put together (half-@§§edly as usual, but seemingly working ) a spreadsheet that can be used to better parse a config.

The configuration can be pasted (copied from the output of an /export command opened in - say - Notepad) directly into the sheet “Config_Input”.

Features/info:

1. comments and warnings (lines beginning with “#”) become bold red.
2. sections (lines beginning with “/”) become bold.
3. multilines are condensed into longer single lines (getting rid of the "" at the end of continued lines and of the 4 spaces (if any) at the beginning of the next line.
4. the spreadsheet uses no fancy VBA, it is all in formulas (so it should be compatible with any spreadsheet program (not necessarily Excel), but since it is a bit complex it is not given. I use for testing as the “minimum viable solution” the excellent Spread32 https://www.byedesign.co.uk/ , a teeny-tiny (shareware) spreadsheet program that can even run from its .zip file. I tested both the (good) “old” 2.04 version (1424 Kb) and the “fatter and newish” 3.20 version (5100 Kb). The newish version has more features (unneeded for this spreadsheet) but it is a little bit slower. On this particular spreadsheet it chokes, while the older version works, but it cannot save the file and crashes.
5. I checked a few of the posted configurations and the longest I could find is one with 1059 lines. Since essentially what is used are vlookup and other “range based” formulas, I made them “capable” of 2000 lines, but to contain the size of the spreadsheet formulas are only copied down up to line 500 (499 actually parsable lines) which is more than the common length of the posted files (150-300 lines). In any case formulas can be copied down in each spreadsheet if more lines should be needed. Same for the firewall sorting, range is 500, but formulas are copied only up to 200 (196 rules) that should normally be enough.
6. since usually noone does what is recommended (for readability) i.e. to order the /ip firewall filter rules by chain, the spreadsheet has a firewall filter sort sheet where the /ip firewall filters rules are separated to check them more easily grouping them by chain (but keeping the original order) and re-ordered. (experimental, I still have to understand how to manage non-standard chains).
7. the spreadsheet includes a blank_template_check sheet that can be copied and personalized, which allows to filter up to 5 sections of the configuration and extract from them the values of the settings, allowing to visually group items of interest (DO MAKE a copy of it and ONLY fiddle with the copy).
8. on the base of the above template I made (also included) the sheets “dhcp_server_check”, “interface_list_check” and “firewall_filter_check” that exemplifies possible uses.
9. even the two most basics features, automatically creating from the Config_Input both a single lines and a terse version should be useful.

I tested the spreadsheet on more than a few configuration files posted on the forum, I included as Example1 and Example2 two of them, but of course the thingy is experimental and it is likely that “edge cases” exist that can potentially bork the working of any of the sheets.

So consider it “early Alpha” (what Mikrotik calls either Beta or RC ).

Licensed according to my Careware License:
http://jaclaz.altervista.org/Projects/careware.html

Have fun .

If you have ideas, corrections, etc. post them.

[jaclaz]

Updated to version Alpha2.

Alpha1 removed.

What’s new in Alpha 2:
*) Config_Input - added note to input to increase compatibility with pasted data
*) the_core - fixed a formula to increase compatibility with partial configurations
*) *_check - fixed a typo, coumns to columns
*) *_check - fixed a typo, left to right
*) terse - fixed something somewhere affecting lines colouring
*) general - tested with a few more configs
*) general - something else that I won’t detail

EDIT: Attachment removed look below for Alpha4 or later

[sid5632]

You haven’t got any
*) … fixed … (introduced in Alpha 1)
lines.

[jaclaz]

You haven’t got any
*) … fixed … (introduced in Alpha 1)
lines.

Yep , since Alpha1 is the first release it would have been redundant, rest assured that if needed the “introduced in” formula will be used in next release.

[anav]

Will give this a whirl…

[jaclaz]

Version Alpha 3 attached.

Now it is (should be) compatible with all three common forms of exported config:

1. actual .rsc file/text file
2. posted config on board (within CODE tags)
3. NEW! posted config on board (direct on post or within QUOTE tags)[1]

What’s new in Alpha 3:
*) Config_Input - edited note to input to increase compatibility with pasted data (introduced in Alpha2)
*) the_core - fixed a formula to add compatibility with pasted data without the 4 leading spaces on continued lines
*) general - likely something else that I already forgot about
*) added sheet changelog


[1] this needed a slight modification of the “main” parsing formula, as the continued lines lose the 4 leading spaces otherwise present in export, so I am leaving Alpha2 available should Alpha3 have issues (it shouldn’t but you never know)

EDIT: Attachment removed look below for Alpha4 or later

[Josephny]

I love this!

Do I understand correctly:

1. This will take an exported config and provide the Export in both single line (which appears to be multi-line) and “terse” formats;
2. It pulls out (i.e., makes a separate worksheet) for /ip/firewall/filter, other /ip, /interface,

But, I don’t quite understand how to read or utilize some of the worksheets.

For example: What does “single line no.” and “move to line no.” mean? And what do columns C and E mean in firewall_filter_sort show?

Thank you for this!

It seems like this could be a great step forward in evaluating (i.e., finding errors) in configs.

[jaclaz]

1. This will take an exported config and provide the Export in both single line (which appears to be multi-line) and “terse” formats;
2. It pulls out (i.e., makes a separate worksheet) for /ip/firewall/filter, other /ip, /interface,

Yep, that is the idea.

The “single line” is actually (should be) single lines (it may wrap around depending on how wide you make the column, but the contents are in single lines) in the sense that multi-lines (long settings artificially divided by the export command in more than one line, with new line represented by ) are condensed in one single line.
Besides the red colouring of comments and the bolding of the sections that make it much more readable than plain text, it is very useful if you want to compare side by side (on a new worksheet or in a text compare tool) two configurations (like before and after a set of changes).

The “terse” format is (IMHO) much less readable, but it is very convenient for copying/pasting snippets on an actual device. And with the (clever?) colouring of the cells makes it easy to select a whole set of settings.

The firewall_filter_sort sheet (experimental) allows to re-order the /ip firewall filter rules by chain.
The Idea is that you:

1. copy the contents of the single_line (as values) to a new worksheet
2. replace the lines with numbers corresponding to those in column A of firewall_filter_sort with those in column E of firewall_filter_sort (again paste as values)
3. copy the whole thus modified configuration to Config_input (in a new or the same instance of Config_parser_Alpha.xls) You can also paste the snippet directly on the corresponding lines (the whole /ip firewall filter section of the same Config_input)
4. now the firewall filter rules will be ordered and appear grouped by chain in firewall_filter_check

The other sheets that end with _check are “free setting”, there is a blank template (aptly called blank_template_check) that you can duplicate and rename and then customize as you wish.

Then there are a few already pre-configured (but modifiable) example *_check sheets:
dhcp_server_check
interface_list_check
firewall_filter_check
that are nothing but personalized copies of blank_template_check filtering some relevant sections (top left of the sheet) and extracting the main settings (columns F-> …), additionally optionally highlighting them with colours.

As an example the dhcp_server_check puts together the settings of four relevant sections:
/ip dhcp-server
/ip pool
/ip dhcp-server network
/ip address
so that one can at a glance see on which interfaces there is a dhcp server, which dhcp pool is used and which addresses/network are used, normally these settings are scattered in different areas of the configuration and you have to scroll up and down to check them.

This is also convenient (see the screenshot here):
http://forum.mikrotik.com/t/multi-wan-pbr-problem/183942/1
to quickly see if similar lines miss a setting that should be there, spotting the missing distance and target-scope settings in that case would have been more difficult without using the spreadsheet.

[Josephny]

The “single line” is actually (should be) single lines (it may wrap around depending on how wide you make the column, but the contents are in single lines) in the sense that multi-lines (long settings artificially divided by the export command in more than one line, with new line represented by ) are condensed in one single line.

I’m probably being dense again (still?), but why is the following named “single lines:”

Besides the red colouring of comments and the bolding of the sections that make it much more readable than plain text, it is very useful if you want to compare side by side (on a new worksheet or in a text compare tool) two configurations (like before and after a set of changes).

How would one go about (what is the process) for comparing two configs?

The “terse” format is (IMHO) much less readable, but it is very convenient for copying/pasting snippets on an actual device. And with the (clever?) colouring of the cells makes it easy to select a whole set of settings.

I agree – I actually like the terse format, and the super clever coloring (talk American, will ya?) does indeed help.

The firewall_filter_sort sheet (> experimental> ) allows to re-order the /ip firewall filter rules by chain.
The Idea is that you:

1. copy the contents of the single_line (as values) to a new worksheet
2. replace the lines with numbers corresponding to those in column A of firewall_filter_sort with those in column E of firewall_filter_sort (again paste as values)
3. copy the whole thus modified configuration to Config_input (in a new or the same instance of Config_parser_Alpha.xls) You can also paste the snippet directly on the corresponding lines (the whole /ip firewall filter section of the same Config_input)
4. now the firewall filter rules will be ordered and appear grouped by chain in firewall_filter_check

I’m having a hard time understanding this.

The other sheets that end with _check are “free setting”, there is a blank template (aptly called blank_template_check) that you can duplicate and rename and then customize as you wish.

Then there are a few already pre-configured (but modifiable) example *_check sheets:
dhcp_server_check
interface_list_check
firewall_filter_check
that are nothing but personalized copies of blank_template_check filtering some relevant sections (top left of the sheet) and extracting the main settings (columns F-> …), additionally optionally highlighting them with colours.

As an example the dhcp_server_check puts together the settings of four relevant sections:
/ip dhcp-server
/ip pool
/ip dhcp-server network
/ip address
so that one can at a glance see on which interfaces there is a dhcp server, which dhcp pool is used and which addresses/network are used, normally these settings are scattered in different areas of the configuration and you have to scroll up and down to check them.

This is also convenient (see the screenshot here):
Multi WAN PBR problem
to quickly see if similar lines miss a setting that should be there, spotting the missing distance and target-scope settings in that case would have been more difficult without using the spreadsheet.

I’m just not getting it.

I do very much like the idea of your spreadsheet grouping related sections of config lines – it addresses a real weakness in how an export is inhenerently ordered.

[jaclaz]

Example.
The first entry here is multi-line:

/ip address
add address=192.168.88.1/24 comment=defconf \
    interface=br-main network=192.168.88.0
add address=10.0.254.1 interface=P8-MGMT network=255.255.255.0

Translated to single-lines:

/ip address
add address=192.168.88.1/24 comment=defconf interface=br-main network=192.168.88.0

add address=10.0.254.1 interface=P8-MGMT network=255.255.255.0

How would one go about (what is the process) for comparing two configs?

One opens a new spreadsheet.
Then pastes in column A an export (single lines).
Then paste in column B a modified export (single lines).
then writes in C2 the formula =IF(A2=B2;“”;“DIFF!”) and copy/pastes it down.
Then if needed it inserts blank cells in either column A or column B to synchronize the two columns rows and pastes again the formula as above until only a few DIFF! are showns (that are the changed lines).
Or copy/pastes the configurations (again single lines) to .txt files and then uses a merge/diff/compare tool to check the differences.
Which program to use is up to the user.

I’m having a hard time understanding this.

Example.
Normal firewall rules from export (column C of firewall_filter_sort) in the example there is a single chain=input rule that is out of place between two chain=forward rules, that I am bolding to highlight it:

/ip firewall filter
add action=accept chain=input comment=“defconf: accept established,related,untracked” connection-state=established,related,untracked
add action=drop chain=input comment=“defconf: drop invalid” connection-state=invalid
add action=accept chain=input comment=“defconf: accept ICMP” protocol=icmp
add action=accept chain=input comment=“defconf: accept to local loopback (for CAPsMAN)” dst-address=127.0.0.1
add action=accept chain=input in-interface=P8-MGMT
add action=accept chain=input in-interface-list=MGMT
add action=drop chain=input comment=“defconf: drop all not coming from LAN” in-interface-list=!LAN
add action=accept chain=forward comment=“defconf: accept in ipsec policy” ipsec-policy=in,ipsec
add action=accept chain=forward comment=“defconf: accept out ipsec policy” ipsec-policy=out,ipsec
add action=fasttrack-connection chain=forward comment=“defconf: fasttrack” connection-state=established,related hw-offload=yes
add action=accept chain=forward comment=“defconf: accept established,related, untracked” connection-state=established,related,untracked
add action=drop chain=forward comment=“defconf: drop invalid” connection-state=invalid
add action=drop chain=input in-interface-list=WAN
add action=drop chain=forward comment=“defconf: drop all from WAN not DSTNATed” connection-nat-state=!dstnat connection-state=new in-interface-list=WAN

you replace those in Config_Input with these (column E of firewall_filter_sort):

add action=accept chain=input comment=“defconf: accept established,related,untracked” connection-state=established,related,untracked
add action=drop chain=input comment=“defconf: drop invalid” connection-state=invalid
add action=accept chain=input comment=“defconf: accept ICMP” protocol=icmp
add action=accept chain=input comment=“defconf: accept to local loopback (for CAPsMAN)” dst-address=127.0.0.1
add action=accept chain=input in-interface=P8-MGMT
add action=accept chain=input in-interface-list=MGMT
add action=drop chain=input comment=“defconf: drop all not coming from LAN” in-interface-list=!LAN
add action=drop chain=input in-interface-list=WAN
add action=accept chain=forward comment=“defconf: accept in ipsec policy” ipsec-policy=in,ipsec
add action=accept chain=forward comment=“defconf: accept out ipsec policy” ipsec-policy=out,ipsec
add action=fasttrack-connection chain=forward comment=“defconf: fasttrack” connection-state=established,related hw-offload=yes
add action=accept chain=forward comment=“defconf: accept established,related, untracked” connection-state=established,related,untracked
add action=drop chain=forward comment=“defconf: drop invalid” connection-state=invalid
add action=drop chain=forward comment=“defconf: drop all from WAN not DSTNATed” connection-nat-state=!dstnat connection-state=new in-interface-list=WAN

You now can check all input rules together, and be sure that after the last one there won’t be any other input ones.

I’m just not getting it.

You enter in the top left B2-B6 up to 5 section names. These are the parts that will be “extracted” and will appear below starting from B15-C15.
Then in Column F (and/or in more copies of it to the right) you enter in row 14 the setting “item” that you want to extract the value for.
Then (optionally) you can add for each of the column from F → in row 11/12/13 a “matcher” that will highlight the corresponding values found with the given colours[1].

Attached screenshot of modified interface_list_check, commented.

[1] thank you for the kind proposal but you cannot really teach new tricks to old dogs, and anyway I’ll stand by my British orthography, the late Queen Elizabeth message still sounds good to me. JFYI:
https://www.eetimes.com/a-message-from-the-queen/

[Josephny]

Example.
The first entry here is multi-line:

/ip address
add address=192.168.88.1/24 comment=defconf \
    interface=br-main network=192.168.88.0
add address=10.0.254.1 interface=P8-MGMT network=255.255.255.0

Translated to single-lines:

/ip address
add address=192.168.88.1/24 comment=defconf interface=br-main network=192.168.88.0

add address=10.0.254.1 interface=P8-MGMT network=255.255.255.0

Oh! The single line is exclusive of the top level and command (“/ip address” in this case). Got it – thanks.

How would one go about (what is the process) for comparing two configs?

One opens a new spreadsheet.
Then pastes in column A an export (single lines).
Then paste in column B a modified export (single lines).
then writes in C2 the formula =IF(A2=B2;“”;“DIFF!”) and copy/pastes it down.
Then if needed it inserts blank cells in either column A or column B to synchronize the two columns rows and pastes again the formula as above until only a few DIFF! are showns (that are the changed lines).
Or copy/pastes the configurations (again single lines) to .txt files and then uses a merge/diff/compare tool to check the differences.
Which program to use is up to the user.
[/quote]

Is this process is completely external (separate) from your spreadsheet?

[1] thank you for the kind proposal but you cannot really teach new tricks to old dogs, and anyway I’ll stand by my British orthography, the late Queen Elizabeth message still sounds good to me. JFYI:
https://www.eetimes.com/a-message-from-the-queen/

LOL!

[jaclaz]

Oh! The single line is exclusive of the top level and command (“/ip address” in this case). Got it – thanks.

Not really, any looong configuration line that gets split by export is multi-line.

Another example, here BOTH settings are multi-line:

/interface wifi
set [ find default-name=wifi1 ] channel.band=5ghz-ax .skip-dfs-channels=\
    10min-cac .width=20/40/80mhz configuration.mode=ap .ssid=MikroTik-AEAF0C \
    disabled=no security.authentication-types=wpa2-psk,wpa3-psk .ft=yes \
    .ft-over-ds=yes
set [ find default-name=wifi2 ] channel.band=2ghz-ax .skip-dfs-channels=\
    10min-cac .width=20/40mhz configuration.mode=ap .ssid=MikroTik-AEAF0C \
    disabled=no security.authentication-types=wpa2-psk,wpa3-psk .ft=yes \
    .ft-over-ds=yes

(you can see lines ending with "" and the next line beginning indented by four spaces)
and they become:

/interface wifi
set [ find default-name=wifi1 ] channel.band=5ghz-ax .skip-dfs-channels=10min-cac .width=20/40/80mhz configuration.mode=ap .ssid=MikroTik-AEAF0C disabled=no security.authentication-types=wpa2-psk,wpa3-psk .ft=yes .ft-over-ds=yes
set [ find default-name=wifi2 ] channel.band=2ghz-ax .skip-dfs-channels=10min-cac .width=20/40mhz configuration.mode=ap .ssid=MikroTik-AEAF0C disabled=no security.authentication-types=wpa2-psk,wpa3-psk .ft=yes .ft-over-ds=yes

(these are wrapped around, but they are single-lines)

Is this process is completely external (separate) from your spreadsheet?

If you use an external compare/diff/merge tool of course yes, but nothing prevents you from addng a new sheet to the worksheet, call it (say) work_diff and use that.
It is hard to suggest an external tool because (frankly) most of them suck or (in the case of three-way merge ones) cost $'s (as they are way to sophisticated/complex/do many more things than needed for this simple use) then, if you post something working in Windows, the next post will be someone posting “but I am using only MAC …” or “but that has not dark mode…”

[jaclaz]

Alpha4 attached.

What’s new in Alpha 4:
*) the_core - fixed a formula to add compatibility with pasted data with blank lines
*) *_check -added a conditional WARNING message for non existing sections
*) terse - fixed something else somewhere affecting lines after comments losing prefix/section
Config_Parser_Alpha_4.zip (174 KB)

[Josephny]

Oh! The single line is exclusive of the top level and command (“/ip address” in this case). Got it – thanks.

Not really, any looong configuration line that gets split by export is multi-line.

Another example, here BOTH settings are multi-line:

/interface wifi
set [ find default-name=wifi1 ] channel.band=5ghz-ax .skip-dfs-channels=\
    10min-cac .width=20/40/80mhz configuration.mode=ap .ssid=MikroTik-AEAF0C \
    disabled=no security.authentication-types=wpa2-psk,wpa3-psk .ft=yes \
    .ft-over-ds=yes
set [ find default-name=wifi2 ] channel.band=2ghz-ax .skip-dfs-channels=\
    10min-cac .width=20/40mhz configuration.mode=ap .ssid=MikroTik-AEAF0C \
    disabled=no security.authentication-types=wpa2-psk,wpa3-psk .ft=yes \
    .ft-over-ds=yes

(you can see lines ending with "" and the next line beginning indented by four spaces)
and they become:

/interface wifi
set [ find default-name=wifi1 ] channel.band=5ghz-ax .skip-dfs-channels=10min-cac .width=20/40/80mhz configuration.mode=ap .ssid=MikroTik-AEAF0C disabled=no security.authentication-types=wpa2-psk,wpa3-psk .ft=yes .ft-over-ds=yes
set [ find default-name=wifi2 ] channel.band=2ghz-ax .skip-dfs-channels=10min-cac .width=20/40mhz configuration.mode=ap .ssid=MikroTik-AEAF0C disabled=no security.authentication-types=wpa2-psk,wpa3-psk .ft=yes .ft-over-ds=yes

(these are wrapped around, but they are single-lines)

Is this process is completely external (separate) from your spreadsheet?

If you use an external compare/diff/merge tool of course yes, but nothing prevents you from addng a new sheet to the worksheet, call it (say) work_diff and use that.
It is hard to suggest an external tool because (frankly) most of them suck or (in the case of three-way merge ones) cost $'s (as they are way to sophisticated/complex/do many more things than needed for this simple use) then, if you post something working in Windows, the next post will be someone posting “but I am using only MAC …” or “but that has not dark mode…”

That was exactly my understanding: That all "" line breaks were removed but the top level command and sub remain on a separate line. Makes sense to me now to call this “single line” because of the removal of the "" line breaks.

[jaclaz]

Yep, but any line beginning with / is not a command, it is a section (or path), normally you issue that line and “change directory” to it and then issue one or more commands all belonging to that same section (the prompt changes telling you “where” you are).

In terse this / line is prepended to each and every command, so it is “more” single-line, if you prefer.

[jaclaz]

Ok, with a bit of fiddling, it is actually possible to have a simple compare sheet that needs not to be re-synchronized when inserting or removing cells.

Stand-alone, so it can be used by itself or added to the Config_Parser workbook.

Attached Config_Compare. (Alpha1, but hopefully there can’t be much that needs to be checked/validated)

Even if it will definitely be more usable using “single-lines” configurations, it should work also on “plain” not pre-processed ones as coming out from export or copied from forum posts.
Config_compare_Alpha1.zip (10.2 KB)