Querying an “Unqueryable” Table
Get creative to get past MCAS table restrictions.
Microsoft Cloud App Security (MCAS) is Microsoft’s Cloud Access Security Broker (CASB) service. MCAS provides centralized management of cloud apps and generates alerts based on cloud app activity, but it has one serious limitation—as of the writing of this blog—in the “Activity Log”.
The Activity Log is a menu where security analysts can go through logs that MCAS generated from monitoring cloud app activity. The flaw with this menu is that it does not allow the user to query it using anything but a handful of pre-built queries. Also, all the columns cannot be sorted aside from the “Date” column. Anyone that has worked as a cybersecurity analyst can tell you this’d be a serious limitation because the ability to query and sort data is vital for performing more complex investigations.
In this blog, we show how an analyst can get around the querying and sorting restrictions in the Activity Logs menu. There are some methods that analysts can use to imitate what they would do while writing a query for any other Microsoft cloud security service.
Preset Queries
The preset queries are provided, but they are specific to a limited set of incident types. Before doing maneuvers to get around the table restrictions, analysts should go through the existing queries to see if they will help with their investigation.
If the analyst is investigating an incident that involves permissions, they can use the prebuilt “Admin Activity” query, or if they are looking for exfiltration activity they can use “Downloads” and “Sharing Activities”. These queries may help with the investigation, but they are not a replacement for doing a full investigation using the logs.
Use “where” and “or” with Default Filters
The default filters at the top of the navigation menu can be used as a logical representation “where” statements like in KQL. If an analyst is looking for more information related to one of their alerts, they can use the filters on top of the menu to filter their results using information found in their alerts as “where” statement restrictions.
The default filters also allow users to select multiple entities at once, which behaves the same as an “or” statement used with a “where” statement. Analysts can look for results across multiple apps by treating the filters as the logical equivalent of “where” and “or” in KQL.
Advanced Filters Unlock Query Options
By going to the top-right corner of the Activity Logs menu and enabling the advanced filters, a new menu will appear near the top of the menu where new filters can be added. This unlocks other logical representations of querying options like “and”, “not”, and “not set”.
This also unlocks roughly 30 tables that can be filtered from based on what is set during the filter creation. This menu can create more precise filters, but it takes noticeably more time to use.
Dynamically Add Filters
Some filters can be added dynamically by scrolling to the right of one of the attributes in an expanded activity menu. A small thumbtack icon will appear next to the attribute that will add the attribute to the filter as an “equal to” function. This is useful for narrowing results while in the middle of filtering through results for an investigation.
Query in Microsoft 365 Security Menu
If an analyst is unable to find the information, they are looking for using filters in the MCAS Activity menu, they will have to move onto the Microsoft 365 Security hunting menu. This menu allows the analyst to write KQL queries on 2 tables that collect data from MCAS, “AppFileEvents” and “CloudAppEvents”.
The reason that analysts do not normally want to come to this menu for MCAS investigations is because the tables cannot neatly display all the information that the MCAS expanded activity log can, and there is no way to associate logs from these menus with alerts in MCAS.
We will continue to share best practices and lessons learned in future posts on investigating incidents in MCAS. Until MCAS is updated with its own advanced hunting menu these are the most effective methods that can be used to find additional investigation information in MCAS.
In closing, consider these three questions when investigating using MCAS in your organization:
- Are we able to respond to MCAS alerts effectively without gathering any additional information?
- Does the security operations team have the skills to get information from MCAS in slightly “unconventional” ways?
- Could we connect MCAS to another service like Microsoft Sentinel to help with analyzing the data that MCAS brings in?