Parsing Microsoft XLSX and Docs to get Firewall Indicators

Working with Firewalls and Microsoft Products it is sometimes necessary to allow certain endpoints in your firewall. Unfortunately not all…

Piere Wöhl

— 5 min read
Parsing Microsoft XLSX and Docs to get Firewall Indicators

Working with Firewalls and Microsoft Products it is sometimes necessary to allow certain endpoints in your firewall. Unfortunately not all are available through their provided Azure IP and Office 365 IP lists.

They are only available as either a list in docs.microsoft.com or as an excel file, those are formats I can not use with my firewall.

To resolve this issue I build a AzureApp which polls these sources and create a json file which is consumable by many indicator-aggregators, like minemeld from Palo Alto Networks.

Defender ATP

When using Defender ATP there are certain Domains and IPs to be available to every system, regardless of their internet access policy, microsoft provides a excel file to allow you to view those endpoints: https://download.microsoft.com/download/8/a/5/8a51eee5-cd02-431c-9d78-a58b7f77c070/mde-urls.xlsx

Intune

The same is with Microsoft Intune for Autopilot or management in general. To allow for a stable connection they provide a list in their docs: https://docs.microsoft.com/en-us/mem/intune/fundamentals/intune-endpoints

The Azure App

I wanted to allow everybody to just consume those feeds, without having to setup a parser on their side, if you want to, it is open-source available here: https://github.com/pierew/serviceendpoints.azurewebsites.net

I had to first get off by understanding how to publish my docker container in Azure App Service, you can read that here: INSERT LINK

Then I build the framework for the container, an endpoint file, necessary resources and packages for alpine were easy to find, which wasn’t easy to get the correct converter for my file formats being present.

After some searching I settled on these two python packages:

those commands are just as simple as they sound, you pass in the file you want to convert and they place the conversions in the current working directory or beside the specified file.

After I had the excel file to converted into some csv files I could parse them in bash (yes I know, I could do all of that in python but I wasn’t this dedicated then). I first had to make sure that the service categories where in a machine readable form, therefore I searched and substituted these through a case-switch loop

This was the first version of the service, then available as intelligencefeeds, I changed the name now to serviceendpoints.

After that the need for Intune came up, so I searched for these resources, they are in a docs page, goosh, I thought I had to parse html but no! Microsoft has their docs in github as markdown files. Where Markdown files are there is a possibility to convert them to something else, I was right.

I extracted the table from the Markdown and used named package to convert them into csv files. which I used to create plain text files to serve for now.cat ./intune-endpoints.md | awk '/client accesses/,/## Network requirements/' | grep -v "client accesses" | grep -v "## Network requirements" > ./intune-management-endpoints.md

My next problem was to sanitize the output from comma,spaces,lines and brake lines into new lines, sed come in handy for that:tr " " "\n" | sed '/^$/d'

Now the plain text generator was done, but I wanted also a json for minemeld, so I went back to the code editor and fiddled around. I knew jq was the tool to do json stuff in the command line. I quickly got a jq command working which created me proper json for every item I wanted to have in the json file.

Basically for every indicator aggregator each json object is one indicator, each domain, url, ip is his own indicator.jq -n --arg type "URL" --arg category "management" --arg url "$item" '{type: $type, category: $category , url: $url}'

These json elements still needed to be formatted to fit the json frame around it, so I used sed again:| sed 's/}/},/' | sed 's/^/    /' >>

Not all IPs were listed for their domains, so I had to implement a dns lookup which looks like this:for ip in $(host -t a $item | grep "has address" | cut -d" " -f4)

now the complete logic for the App was done, I published it now under it’s new name at: https://serviceendpoints.azurewebsites.net

Minemeld

Minemeld is a indicator aggregator to prepare indicator feeds for primarily Palo Alto Networks Firewalls, Minemeld is available as a docker container from PANs dockerhub profile “paloaltonetworks/minemeld”

You can run the container with:docker run -dit --name minemeld --tmpfs /run -v minemeld-local:/opt/minemeld/local -v minemeld-logs:/opt/minemeld/log -p 8443:443 -p 8080:80 paloaltonetworks/minemeld

To now have minemeld query those feeds you need to create new prototypes based on existing one’s, you do that by navigating into the config and opening the Prototype Browser which is the blue “Hamburger” Button.

Now you need to have an existing Prototype to create a new one from to modify it, it needs to have the correct parent-class to parse JSON. Fortunately aws.S3 is a json prototype, select it and click NEW.

Now use the miner configs from my GitHub: https://github.com/pierew/minemeld

After creating this new prototype, select it again in the browser and “clone” it, this creates the NODE which will pull the feed and filter it based on the set attributes and filters.

Now go back into the prototypes and clone the feedHCGreen Output, which will be responsible for providing the feed for your Firewall to be read properly.

If you want you can use an aggregator prototype for IPv4 and URL to aggregate those MS products into one feed, so you need only one feed for every indicator type, if you want to.

you can also place those prototypes into the /opt/minemeld/local/prototypes/minemeldlocal.yml file and it will be autodetected.

If you wish to have these configured for you, you can use my docker container here: https://github.com/pierew/minemeld-docker/pkgs/container/minemeld-docker

Read more