Getting more data from the CA Insights and Reporting Workbook

Log Analytics workbooks are pretty intuitive, but KQL is one of those things that can take a while to learn and use effectively. One of the coolest things about workbooks is that it is actually building a KQL query in the backend based on our filter selections, and then we can usually edit the resulting query in Log Analytics based on the table (or other elements) we are interested in from the workbook and even export to Excel for further filtering in a more familiar tool 🔥

This example shows how we might create a query to look at all failed logins for a specific Conditional Access policy over the last 90 days. As you can see, the original query doesn’t give us a whole lot of details about why the user failed, just some detail about who the user is and what apps they were accessing.

You may notice the original query building the table had “| project field1, field2, etc.” statements which is basically saying only include these fields and none of the other fields. When we remove these project statements, then we retrieve all available fields and data :)

This trick works for all Log Analytics workbooks that expose the Edit in Log Analytics button for us. For those that don’t, we have to edit the workbook to see the underlying queries which I’ll save for a later time.