I get this question a lot. An institution hands me a spreadsheet of 200 MACs and IPs and asks for a quick way to push those as static IP-MAC reservation entries into a Sophos firewall. Manual clicking is boring and error prone. There are three practical routes: convert the sheet to a Sophos-compatible XML and import, use the Sophos API if available on your firmware, or keep DHCP on a dedicated server that supports CSV or PowerShell imports. I will show the safe path I use, give a CSV example, and explain how to delete en masse when the term ends.
Start by exporting or inspecting a current Sophos config so you know the expected XML structure. Sophos supports importing a partial or full configuration as XML from Backup and firmware > Import export in the GUI, so you can add entries offline and re-import the file rather than typing each row in the web UI. If you prefer community-sourced notes and alternatives, see the Sophos docs on import/export and a community thread that raised this exact problem: Sophos import/export docs and a Reddit discussion on bulk static reservations. Those links show the import point in the GUI and community workarounds. Use them to confirm your SFOS version supports partial import the way you expect.
Convert your spreadsheet to a small, predictable format first. I use a CSV with three columns: hostname,mac,ip. A single example row looks like: classroom01,00:11:22:33:44:55,10.10.20.101. Keep MACs in lower-case or a consistent case your scripts expect. Validate every row with a quick grep or Excel filter to catch duplicates and out-of-range IPs before attempting an import. Next, either run a tiny script that wraps each CSV row into the matching XML node the firewall uses for static mappings, or use an existing converter from a reputable source. The script approach gives repeatable imports and a clear rollback path.
If your firewall firmware and licence allow API access use that instead of import/export for safer, repeatable ops. The API accepts authenticated calls to create and delete objects programmatically. I use an API script that reads CSV, checks against the DHCP pool and current leases, then POSTs each mapping. The advantage is immediate feedback and simple error handling. If API access is blocked on policy, fall back to XML import. For both routes keep a backup of the current configuration and run imports against a staging device where possible.
Deleting many reservations follows the same patterns. If you used XML import, prepare an XML that removes or replaces the static mapping nodes and import it. If you used the API, send delete calls for each MAC or IP. If you prefer the GUI, filter the static mappings list and delete by manual multi-select, but expect that to be slow for hundreds of entries. Always verify deletion by checking the DHCP mappings area in the GUI and by confirming leases have returned to the dynamic pool. A quick smoke test is to reboot a sample device and confirm it receives the expected address.
Practical checks to run after any bulk change: confirm the import file matches the firewall’s expected XML schema; confirm no collisions with the DHCP dynamic range; and verify a random sample of devices actually receive the reservation by renewing DHCP on a device. Keep the original CSV and a timestamped copy of any XML you import. That gives an audit trail and lets you roll back by re-importing the previous config if anything goes wrong. If you need a starting script, search for CSV-to-Sophos-XML converters and adapt one to your CSV layout rather than hand-editing XML for hundreds of entries.
Do this once and you will save hours every provisioning cycle. Prepare CSV, validate, convert to the target format, import or API push, then verify with the GUI and a few DHCP renewals. Those steps stop the common mistakes: wrong MAC format, IPs inside the pool, and accidental duplicates.
