Cloud App Discovery: Now with Excel and PowerBI Support

First published on CloudBlogs on Oct, 01 2014
Howdy Folks, I wanted to share another update to Cloud App Discovery. As our customers have started using this set of capabilities, one of their top requests is to be able to perform analytics on the data collected in tools like Excel and PowerBI. So I'm happy to be able let you know that you can take cloud app discovery data offline and explore and analyze the data with tools you already know--Excel and PowerBI. I've asked Girish Chander, the Principal Lead PM for Cloud App Discovery to walk you through this new feature. You'll find his blog post below. As always, we'd love to receive any feedback or suggestions you have. Regards, Alex Simons (Twitter: @Alex_A_Simons ) Director of PM Active Directory Team ----------------------- Hey everyone, 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 You can try this out at the portal at http://appdiscovery.azure.com .

Getting Offline access to the Cloud App Discovery Data

Step 1: Create an Azure Storage account

If you don't already have an Azure storage account or if you want to create a new Azure storage account, please follow the instructions here .

Step 2: Retrieve the account name and the access key from your storage account.

Log into the Azure Management portal, and click STORAGE

Click the storage account you want to use

Click 'Manage Access Keys' in the command bar at the bottom of the screen.

In the 'Manage Access Keys' dialog, copy the 'Storage Account Name' and 'Primary Access Key'

Step 3: Configure Cloud App Discovery to route data to your Azure Blob Store

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

Paste the 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.

Step 7: Explore to derive further value from Cloud App Discovery data!

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!

Go on, give this new feature a try. Go to Cloud App Discovery and sign up….it's free! 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 . Happy exploring! -Girish