It’s Girish again. And I’m excited to share the details of this new feature we released today—the ability to get offline access to the Cloud App Discovery data for your organization. This has been a popular customer request, as customers want to be able to use tools like Excel and PowerBI to analyze their data.

Today’s update provides you the ability to export your data to an Azure Blob Store. You can then to import your data into Excel using Power Query. Below is a detailed walkthrough of how you can do this

In the Cloud App Discovery portal, under the Settings gear click on ‘Store data’.

Pastethe name and key of the storage account that you copied above.

And that’s it, you’re all set. Keep in mind that only data that is captured AFTER configuring the offline store, is available in the Azure blob store.

Step 4: Access Azure from Power Query

To connect Excel to the blob store, follow the really simple instructions here

Note: Do not click ‘Apply and Close’ in the Query editor yet. Let’s stay on the query editor for the next few steps.

Step 5: Expand the Blobs to reveal all the data in the Query editor

When you first connect to the Azure blob store, you will see a container in the Navigator that looks like this:

Double-click the container to see the various blobs of data. We create a new blob per day for each machine the agent is running on. The ‘name’ column of the table below captures the machine name and the date for each blob.

To see all the data in the container, double-click the column that says ‘content’. Alternatively, double click ‘Binary’ of blob you are interested in seeing the data for. This will reveal the data for your organization. Below is an example from my own tenant.

Step 6: Analyze the data

The data is organized into the following columns:

Machine Name: The name of the machine from which the data was collected

User Name: The name of the user who accessed the application

App Name: Is the name of the application or site that the user accessed.

Category: This is the category of the application in the AAD Gallery. There are three notable exceptions. For applications or sites that are not in the AAD gallery, we use ‘Other’. For traffic that we have categorized as ads, we use ‘advertising’. Some traffic we categorize as ‘noise’. This is because the traffic is not part of an intentional access to the application, but was generated incidentally as part of using the application.

Is Business App: When True, these are applications that we see as most likely used for business purposes. While some applications like Facebook may be used in a business context we see its most often used in a non-business context. This pivot may be useful for you to focus on those apps that are most likely used for business purposes, by your employees.

Requests: This captures the number of web requests made to the application within the time window.

Bytes sent and Bytes Received: Captures the volume of traffic sent to and received from the app within the time window

Date Begin and Date End: These columns capture the start time for the data aggregation and the end time. Each row aggregates data for a minute against the app in question.

To start focusing on the business applications as a priority, I recommend filtering out the Ad, noise and other categories.

I also recommend filtering out the non-business apps.

We did not do this by default, because we’ve heard from many of you that you want this data to be available to you, in the event you want to do further forensic analysis across these other applications.

Once you’ve made the relevant selections, click ‘Apply and Close’ in the query editor to load your data set into excel.

Now you can pivot all the data in a variety of ways. Here are some interesting pivots that customers have told us they’d be interested in exploring. I’ve used pivot tables to produce the same data for my tenant.

All activity by a particular user within a particular timeframe

Seeing all the applications used by a particular user (or set of users within a department) within a given timeframe, can be useful in situations where you want to do forensic analysis. You can use pivot tables as shown below to get the desired data.

In the picture above, I’ve chosen to explore all the business applications used by a particular user in the month of September. You see the result on the far left.

To do this, in the Pivot Table Fields, I’ve chosen to the ‘App Name’ as the rows to report, while using User Name, Is Business App and Date Windows as my filters.

Applications with the highest downloads in a particular timeframe

Seeing which applications consume the most bandwidth can be useful for planning your network capacity. Historical information may also be useful to detect patterns, plan better and for forensic analysis

In the picture above, I’ve chosen to explore which business applications users downloaded the most from, in the month of August.

I used both a pivot table and a pivot chart to represent the information. As you can see, in my tenant, Sharepoint, Yammer, msdn and Github represent the business apps with the maximum download volume for the month of August.

Collaboration applications with the highest uploads

Collaboration and file sharing applications often pose the highest data leakage concern for administrators. Seeing which collaboration applications have the most uploads may provide one clue to where IT must focus attention first.

In the picture above, I’ve chosen to focus on collaboration apps used by users in my tenant in the month of August, to see which ones are the apps uses are uploading most information to. I can see that I need to focus on securing Box and Dropbox first.

Users with the highest upload volume within a particular timeframe

The example above can also be extended to seeing which users are consuming the most bandwidth

In my demo tenant, I only had 4 users, but in your organization, you may want to add a filter and only focus on the top 5 users or users with volumes greater than a threshold.

There are many more interesting possibilities to pivot the data. Go on, explore! And if you would like us to help you with any of these, let us know.

Tell us what you think!

Over the next few weeks we’ll continue to make improvements to this service and light up more value. Give us your feedback- we’d love to hear from you. Your inputs help us ensure that we deliver a solution that works for you. If you have any suggestions, questions, or comments, please use this forum.

Join the conversation

Recent Posts from EMS Leaders

Howdy folks, Imagine a user visiting your company’s Azure AD app launcher for the first time and finding exactly the apps they need to be productive and effective. No confusion or clutter. A dream scenario, right? You’ve sent us more and more requests for features that allow you to better manage third-party apps as you’re...

Last week at Microsoft Ignite, more than 25,000 IT professionals converged in Orlando Florida to learn about Microsoft’s technology advancements, skill up across new products, and meet with Microsoft experts. For EMS we unveiled a wave of new capabilities, presented more than 45 sessions, and met with thousands of customers. I wanted to take a...

Late last week, I wrote about the remarkable quarter-century milestone reached by ConfigMgr, and today I wanted to dive even deeper into the backstory of this incredible product, share a couple announcements, and debut an awesome new documentary (lookout Sundance!) which offers an in-depth look at the genesis and growth of the product that created...

Organizations are pushing forward in their digital transformations and we continue to see and hear more about what this shift means for IT. The scope of digital transformation goes beyond moving existing work to the cloud and enabling a more mobile workforce. It brings the opportunity to reimagine business from the ground up – from...