topic: sample products: - office-365 languages: - powershell extensions: contentType: tools
This project is to help faciliate testing and low-volume activity data acquisition from the Office 365 Management Activity API.
Once you have selected the data store that you want to publish your Activity API data to, simply open the ConfigForO365Investigations.json file and enable and configure the attributes that are relevant to your store. Note you will have to register an application in Azure AD, then populate the config with the AppID (
InvestigationAppID) and AppSecret (
InvestigationAppSecret) to enable data flow for the Activity API.
Follow the instructions in the Management Activity API: Getting Started Guide to create a new AAD application and grant it permissions to the tenant's Management Activity API.
If you don't already have a MySQL database, download the Windows MySQL installer. Make sure to include MySQL server, MySQL Workbench, and the ODBC and .Net connectors. (MySQL docs are here: https://dev.mysql.com/doc/refman/5.7/en/json.html
mysqlcommand-line client, run
CREATE DATABASE O365Investigations;
to create the database.
Populate ConfigForO365Investigations.json with your MySQL admin name and password, as well as the hostname and database name.
Run the O365InvestigationDataAcquisition.ps1 script to enable the subscriptions and pull the data. Re-run regularly to continue to consume new data.
Once you have enough data, open MySQL Workbench, open ActivityAPI-InvestigationQueries.sql and run the approach SQL statements to get answers to your questions.
Determine the desired storage account name and update the config file.
Determine the desired container name and update the config file.
Determine the account name you will use to manage the blob storage and update the config file.
Run the PowerShell command
Read-Host -AsSecureString | ConvertFrom-SecureString
and provide the password for the account you will use to manage the Azure blob storage, then use the output as the value for
AzureAccountSecureStringin the ConfigForO365Investigations.json file.
Login to your Azure subscription at https://portal.azure.com
Ensure you have a storage account set up
Select "+ New" in the upper left, then "Data + Storage", then "SQL Database"
Name your new database "O365Investigations"
Select an existing SQL server (and make note of the hostname), or create a new server (making note of the admin account you used to create the database)
Select the source, pricing tier, resource group, and associated subscription, then click "Create".
Select SQL Servers from the main navigation, select the server you just created, then click "Show Firewall Settings". In the "Firewall Settings" blade, click "Add Client IP" and add the IP address of the host where you will be running the investigations tooling from. Save and wait for confirmation that the firewall rules have been updated.
Use Visual Studio, or download SQL Server Management Studio Express 2014 (for free) and connect to your new database.
Create a new SQL database named "O365Investigations"
Ensure you have a username and password for an account that can connect to the database.