Visualize Microsoft Forms results in Log Analytics

I was recently assisting a group of students with a project for the Sentinel Hackathon. We came up with the idea to create alerts and dashboards based on Suspicious Activity Reports. The following example will demonstrate how Microsoft Forms responses can be sent to Log Analytics using a Logic App for further analysis. The solution has a wide range of uses where automation and visualization of user responses is desired.

Scenario:

Cyber-attacks are often proceeded by physical intrusions and social engineering that does not leave a digital footprint. Employees occasionally encounter suspicious activity and they may struggle with how or where to report this information. The Suspicious Activity Report gives users the ability to report suspicious activity directly to the security team. The employee has the option to set the priority and may request a call back if needed. The security team needs a mechanism to track and correlate suspicious activity by collecting dates, locations, and other characteristics.

Requirements: Microsoft Azure Account and a Log Analytics Workspace (optionally, activated for Sentinel).

Step 1 – Create a Microsoft Forms survey:

Create a survey using Microsoft Forms. Here is an example Suspicious Activity Report and some tips on form creation:

• Fixed, multiple choice responses are good for dashboards. For example, you can display reports by device type or location quite easily.
• Free text responses can complicate dashboard queries. For example, MS Forms has a standard response for “Other” that allows a free-text response. I recommend fixed responses with multiple choice questions. You can also consolidate the free-text responses in your dashboard query.
• MS Forms has an option to limit responses to your organization. If selected, the user account is provided in the background. This account can be mapped to the Account entity in Sentinel for better correlation in hunter queries and investigations. Displayed in the responder_s column (otherwise listed as Anonymous).
• Forms shared publicly do not collect background user information. You decide how much information to collect and which entries are required.
• Each response is placed in a column with a generic label which we will address later. For example, r33635b738c3c43e5a2bf2df3ca52bf30_s.

Step 2 – Collect the Microsoft Forms Responses using a Logic App:

We created a Logic App in Azure with three simple components displayed below. The trigger monitors the Form ID for new results. The responses are collected dynamically into a variable “List of Responses”. Finally, the contents are written to a custom table in Log Analytics. Each new submission creates a new row in the table.

Step 3 – Rename the columns in Log Analytics:

This is an optional step to make queries and dashboard design easier to perform by renaming the generic column names. Your survey response columns will be named something like r33635b738c3c43e5a2bf2df3ca52bf30_s. Log Analytics does not allow you to rename columns on custom tables. You can use a saved query (also called a KQL Function) that renames your columns. The function alias replaces your table name in queries and dashboards. Here is a more detailed explanation on KQL Functions if needed.

Example:

Step 4 – Create alerts and dashboard (workbooks) using the data collected:

You now have a dataset that can be used to create dashboards and alerts. In our example, we generated alerts when the user reported activity with a critical severity and when the user requested a call back. We also created a daily incident summary alert. See sample queries below. If this data includes standard entity values used by Azure Sentinel like account or IP address, these entities can be mapped in the Analytic or Hunter Query rules for deeper investigations.

Alert Query Examples:

User_report_CL
|where Severity == “Critical”
\\User reported critical severity incident

User_report_CL
| where TimeGenerated > ago(30d)
\\Summary of daily user reported incidents

Dashboard Query Examples:

User_report_CL
| extend Date2 = todatetime(Incident_Date)
| summarize Hits=count() by bin(Date2, 1d)
\\Reformat reported date and summarize reports by daily count

User_report_CL
| summarize Hits=count() by Severity
\\Tile displaying reports by severity

Special thanks to the Camp Pendleton Sentinel Hackathon team for their contributions. You can see their submission to the 2020 Sentinel Hackathon. The team members are US military veterans in the Microsoft Software & Systems Academy (MSSA) program.