Getting controle back with Exchange admin audit logs in realtime with Power BI

A lot of times when I visit customers I notice that they struggle with the concept of managing Exchange. Although they have extensive RBAC in place, they are still unable to understand who is doing what. They rely on their change procedures to know who is changing what in their environment. We all know how good we are as administrator in using proper change procedures for production environments (or even better test / pre-production environment) when something breaks or needs to be done on Friday 17:00. Ask yourself the following questions and be honest do you have the proper measures in place for them:

  • Do you know what is being changed in your Exchange environment and by who?
  • Do you know when something was executed?
  • Does anybody of your company ever look proactively in the admin audit log?
  • What if a admin did do something malicious and just recreated the admin audit mailbox?
  • And than there is that during migrations to newer versions it is forgotten to move the admin audit mailbox to the newer version. Etc. etc.

When Search-AdminAuditlog is no longer efficient

Because I have seen way to many of these issues I have started working on a kind of backup for the admin audit log. A new way to make it visible, in real time, possibility to apply filters, a fast responding interface and easy to view the data without any non-Microsoft tools. The only thing you need is a Power BI free license ( or push all the data in a O365 Teams Power BI to have the data shared with the entire group ). One of the cool site effects is that you can also see when Exchange does self healing or runs commands in the background ( when installing other server or recreating health mailboxes for example ).  Here is the sample dashboard from my environment.

Notice there are 3 tabs “Admin vs Target, Servers, Raw data”. You can filter dates or drill down in a rapport.

 

 

  • Admin vs Target ( tab 1 )  – I created this tab to show who run what command at what time against which target
    • This can be very handy to identify:
      • Rogue admins
      • Compromised accounts
      • Changes not logged in your change administration application
      • Misused parameters ( Educate your co-workers in the proper use of parameters)
      • Server configuration drift
  • Servers ( tab 2 ) – I created this tab to see what commands get run from which server at what time and which domain controller
    • This can be very handy to trouble shoot:
      • Performance issues on overloaded domain controllers
      • Performance issues on Exchange servers
    • This can be very handy to isolate:
      • Running scripts causing overload issues
      • Server configuration drift
  • Raw data ( tab 3 )
    • Here you can see what is actually going on

Creating admin audit logs in Power BI

Now, how do we create these nice reports of the admin audit log in Power BI? Good question.

There is a functionality that Microsoft introduced into Exchange 2010 that is not so commonly known. This functionality is called a “Cmdlet extension agent”; you can find more info about it on the Microsoft technet site. Although you might find some blogs on the internet describing some basic functionality most of the capabilities are not well documented.

With this functionality we can extend the built in PowerShell Exchange commands to run additional code before, during or after execution of that command. In the backend the new code instantly customizes the default commands on all servers, for all admins and applications. As crazy as it sounds your now standardized your customization??!!??.By enabling this functionality we can extend all the commands we want to do a direct API call to Power BI or Azure event hubs ( if you want a blob storage backup and sent the data to Power BI). Using this we know that we can manipulate the code running after an Exchange command we can start extracting the data from the command and use that data to generate JSON strings.

Power BI accepts JSON as input for its streaming API and it creates very nice graphical dashboards; something I  had to explore. How do we set this up : First of all you need to have a Power BI account or use Microsoft teams to enable it using a teams Power BI workspace. In this sample I will work with my own Power BI account. ( A power BI pro license is not required as this functionality does not use any of the premium features. You can create a account on www.powerbi.com or use your O365 account) Login to PowerBI and create a new streaming API with historical data enabled:

 

Next we feed the API the parameters that Exchange will start to feed into Power BI. Although you can customize this to your needs if you use the cmdlet xml file provided by me you should create the API exactly as stated in the screenshot below (note the Historic data analysis slider ).

After you created the API you can get the endpoint from the info provided or by reopening the settings. This endpoint contains the API location and key used to secure the stream. ( As with all Azure API keys don’t expose this key any where else) Copy this endpoint URL as we need to update this in the XML file so Exchange knows where to push the data to.

Next open the XML file provided in this post and scroll all the way down. Replace the API endpoint with the one you copied from the previous picture.

Now that we have the API, and the cmdlet file ready we can start distributing the cmdlet extension XML to all Exchange servers you want to monitor. You only need to copy the file to the location “C:\Program Files\Microsoft\Exchange Server\V15\Bin\CmdletExtensionAgents\ScriptingAgentConfig.xml” On all the Exchange servers (update the v15 to the version of Exchange you are using). You can find the XML command let extension files on the Microsoft TechNet gallery or via the download links on this site. Finaly the last step is to enable the scripting agent so it will load the XML. This is a organization wide setting and only needs to be done once. Run the following command in a exchange management shell to enable the agent.

Enable-CmdletExtensionAgent "Scripting Agent"

Your all set up now. If you connect to a Exchange PowerShell endpoint or start a new Exchange management shell on an Exchange server the agent will start forwarding the data to Power BI. Run some commands to feed the Power BI and verify that the script agent is working. It will show you a red error if there is something with the code in the XML file: For example set a custom attribute: set-mailbox -Identity “mailbox1” -CustomAttribute1 “Test2” Now lets start playing with Power BI. Go back to Power BI and create your first report

  1. Select your dataset.
  2. Select a Visualizations
  3. select some fields from you dataset
  4. drag the field to the visualization option you want to display them in.
  5. See your data come to live. keep adding more visualizations and new pages until you have the layout you want

Finally go to File -> “publish to web” if you want to share you Report with your team members.

You can find the XML command let extension files on the Microsoft TechNet gallery or via the download links on this site.  

That’s all have fun getting your control back.

Leave a Reply

Your email address will not be published. Required fields are marked *