Intro
One of the most important technologies built into InTrust is a special event repository which reduces costs and efforts required to store and manage huge volume of events. InTrust can reduce storage requirements considerably, 20:1 for indexed data and 40:1 for unindexed, cold data. But that’s not only a cold repository, InTrust provides ability to index and search data stored there (it works even on unindexed data).
You can perform fast and responsive full-text searches against data in an indexed repository using InTrust Repository Viewer or IT Security Search, which also nicely correlates this data with infrastructure discoveries from Enterprise Reporter, changes from Change Auditor, backups of Recovery Manager and actions from Active Roles.
Also there is big library of reports for data collected by InTrust, and there is a special workflow in the InTrust server that can automatically import a subset of data from this repository into SQL Server for further analysis. So when you run a report in InTrust, in reality such a workflow in the background imports the data into SQL. There is also a way to gather data directly from endpoints to the SQL Server and repository simultaneously, so that you have both – hot-data in SQL for fast and complicated analysis and colder-data in the repository for compliance and audit purposes with full-text search capabilities.
Setup
In this article I will demonstrate how the very same workflow could be used to analyze or upload data in the Microsoft PowerBI Cloud, so that you can create complicated analysis reports there (strictly speaking you can do this for any SaaS data analysis application similar to PowerBI as long as it can get data from on-premises SQL server)
If your organization has Office 365 subscription, most likely you already have access, you can check there.
First thing you need to do is to create a new database in InTrust Manager or use the built-in default SQL DB
The workflow can be created to import only new data, so that it will run faster on the consequent runs.
As you can see, InTrust server has a quite sophisticated workflow engine. You can create advanced scheduling for your tasks and choose from dozen job types that can import, consolidate, gather, clean-up data and even run some programs and perform notifications.
The scheduling engine provides variety of options for configuring recurrent operations. I was able to specify a schedule that executes an import every 2 hours, but you can try even more often - there will be less data to import and the task should run faster - you need to experiment with your environment to find the sweet spot in terms of data freshness and performance impact.
In the workflow engine, you can then build-up the workflow by adding one job after then other, import data, then run a report and notify the responsible person.
When we first start the import job we need to wait for the workflow to process all the data, it could take up to several hours depending on your events volume.
Connecting PowerBI
Download PowerBI desktop and PowerBI on-premises data gateway.
Install and configure PowerBI on-premises data gateway to be able to connect to on-premises datasource from within PowerBI cloud.
Using PowerBI desktop, create and configure a report based on data from InTrust’s SQL DB. When specifying SQL DB settings, pay attention to the query type. Direct query will retrieve the data any time someone refresh the visual on the PowerBI cloud dashboard, on the other hand almost no storage is being consumed from your PowerBI cloud quota. To use this type of query you need to specify "use current credentials" in the SQL connection settings. Import query type imports all the data into the application and/or cloud for further analysis, this query works much faster in the cloud and allows Q&A PowerBI feature, but will consume some of your cloud quota. We do not recommend to use this type without filtering and/or grouping of data prior to uploading it into PowerBI report.
Then select a table in the imported from InTrust Database. We recommend to use "AdvEvents" view which is useful for basic event information, if necessary advanced details and fields parsed from the event could be joined from other tables.
Make sure you've added the corresponding datasource to your connected gateway in your PowerBI account settings
Create and upload the report into PowerBI cloud workspace.
Results
Here is an example report based on data from AdvEvent SQL Server view. The only transformation that I'm doing in this example is about grouping hourly using the GMT date field, and the rest of the data is raw last 14 days of events added to 5 different visuals
Now you can automatically see the updated data there, share it with your colleagues in the Office 365 organization, or even publish for everyone in the cloud.
If you play with the data, so that Import query type can be used (I grouped by date transforming GMT field into date-only field, by computer, event category and EventTypeName and took only last month of data), you can also run additional analysis and even use Q&A engine. Here is an example
We think that even more interesting integrations are possible with powerful InTrust import/automation workflow engine. For example similar results can be received on-premises with SQL Server 2016 SRS and KPI tiles or Mobile reports.