This solution has been released as a Beta. Please share feedback if you find it useful (or not)
This guide will take you through the process of using the iAuditor connector available in Microsoft Flow to automatically create SQL Server rows for each Action created in iAuditor. It assumes you have a working knowledge of SQL Server and an existing table structure you wish to populate.
The article shows how to export data to an Azure SQL Server database. Connecting to on-premises SQL Server is possible but a bit more complex, using a gateway. Check with Microsoft if your version of SQL Server is supported.
Begin by adding the Schedule – Recurrence trigger in a new blank flow. We’re going to configure the flow to check iAuditor for new Actions to process every hour.
Go ahead and set up the trigger so that the Interval is 1 and the Frequency is Hour.
The Get past time action is also needed for this. Add it to your flow and configure it to match the settings for the trigger.
The next step is to add the Search actions operation listed under iAuditor and set the Created After Date field to use Past time from the available dynamic content.
Add an Apply to each loop, selecting Actions, from the available Dynamic content:
Now the SQL Server – Insert Row operation can be added inside the loop and the different Action properties mapped to your columns. You’ll fine a couple of examples highlighted below:
The assignee information isn’t directly selectable from the Dynamic content selector. An Expression has to be used for each.
- The expression for the assignee name is: item()?[‘assignees’]??[‘name’]
- The expression for the assignee’s user ID is: item()?[‘assignees’]??[‘id’]
This flow will populate your table with the Actions as they’re created. To enable two-way sync, you’ll also need to create two other supporting flows which:
- Update SQL rows when an Action is modified in iAuditor
- Update Actions in iAuditor when the relevant row in SQL is modified
This guide walks you through configuring a flow that will insert a single SQL row per audit. Heavy usage where many thousands of inspections are created daily can result in performance bottlenecks and delayed row insertion.
The number of rows created per second will be largely dependant on a number of different factors. These include but are not limited to the number of loop iterations and HTTP requests the Microsoft Flow platform handles.
Microsoft Flow provides some information on the platform’s limits here.