Thursday 5 September 2019

Office Reporting API


Problem

Working with a customer moving from one tenant to another. Two distinct groups of users and one of the challanges has been in getting data on OneDrive, OneNote usage and client usage.We have not found decent information on the usage of OneDrive or OneNote, so moving 5K users is challanging. OneDrive has two statistics that MS publish, Total Files and Active Files. We have found that total files is different in OneDrive from the report is different to the count on a local copy. Have no infor on why this is different.
The PowerBI or Office 365 report for OneDrive will give you the number in Sharepoint.
The following query in PowerShell on your local OneDrive location, assuming all files are synched, will tell you how many files you have.
     
    dir *.* -recurse | measure-object


The Data Source

Based on access to MS O365 usage report on PowerBI.
Enable O365 reporting as per the Microsoft document Link

The file on Customize the reports in Microsoft 365 usage analytics Link is faulty. I've reported this, so a copy is available here 

Tenant ID found in Azure AD    _Tenant_ID_
Account for the user that enabled O365 reporting        admin@_Tenant_Name_.onmicrosoft.com


Shared link for the PowerBI portal

Office 365 Reports in the admin center - OneDrive for Business usage
The PowerBI Desktip pbit file is in the files section, password is please

If a PowerBI app fails to load data. Delete it and re-add.
PowerBI Desktop will save the creds you used to connect to the PowerBI portal.

 Fig 1 – PowerBI Desktop



To make use of the PowerBI report, edit the query to get at the data/ API.

Fig 2 – PowerBI Desktop



Fig 3 – PowerBI Desktop

Copy out the code
let
    Source = OData.Feed("https://reports.office.com/pbi/v1.0/" & TenantID,null,[ODataVersion=4]),
    TenantMailboxUsage_table = Source{[Name="TenantMailboxUsage",Signature="table"]}[Data]
in
    TenantMailboxUsage_table
Then to edit we take the api piece and add in the tenant ID and then the content we want to report on
Source = OData.Feed("https://reports.office.com/pbi/v1.0/" & TenantID,null,[ODataVersion=4]),
    TenantMailboxUsage_table = Source{[Name="TenantMailboxUsage",Signature="table"]}[Data]

We will use
https://reports.office.com/pbi/v1.0/ Tenant ID to be added /TenantMailboxUsage

Giving us


Fig 4 – Report 01


Tenant OneDriveUsage is as follows:



Fig 5 – PowerBI Desktop

let
    Source = OData.Feed("https://reports.office.com/pbi/v1.0/" & TenantID,null,[ODataVersion=4]),
    TenantOneDriveUsage_table = Source{[Name="TenantOneDriveUsage",Signature="table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TenantOneDriveUsage_table,{{"DiskQuota", Int64.Type}})
in
    #"Changed Type"

All tenant reports

Other reports available


What Clients are being used, limited data.
Aggregated mailboxes in O365
O365 group activity per month over the last 12 months as a monthly stat
Monthly total office activations
OneDrive activity per month over the last 12 months as a monthly stat
Seems to show the total activity per month over the last 12 months as a monthly stat
Seems to show the total product activity per month over the last 12 months as a stat
SharePoint activity per month over the last 12 months as a monthly stat
User overall activity per month over the last 12 months as a monthly stat. Is this the important one?
User information



Thursday 20 December 2018

Microsoft Graph API - Post 1 of many

Microsoft Graph API

It almost seems like the Tangential Screw is out of Sync.
A phrase I've commonly used and sound technical enough that people leave me alone, assuming that I know what I'm doing.
Microsoft Graph API seems to be the 'fix for everything' that is wrong in O365/ Intune/ Azure.

Anyway, I decided its time to read about it and jot some notes down.

These are random pieces of code that worked for me and failures and error message I figured out. Mostly because as a person who likes to kick tin, coding is not a natural way to access logs.


If you see D.W. it means didn't work, or I haven't figured out what it does yet.
General assumption needs to be be I made a mistake, but with this cloud stuff...who knows. If you have a complaint or comment on that , then revert to above, I like to kick tin, virtual or physical.

I'm currently using the Graph Explorer
https://developer.microsoft.com/en-us/graph/graph-explorer

Will move to Visual Code at some stage with the intention of trying to see if I can get security information.

So here goes:
Demo O365 Tenancy, with a three moth life time.
No vanity domain added yet.

GET https://graph.microsoft.com/beta/security/secureScores?$filter=fqdn eq 'AarifS@M365x106898.onmicrosoft.com'

GET https://graph.microsoft.com/beta/security/hostSecurityProfiles?$filter=fqdn eq 'AarifS@M365x106898.onmicrosoft.com'&$select=riskScore

These work, but give a response with no data.

{
    "@odata.context": "https://graph.microsoft.com/beta/$metadata#Security/hostSecurityProfiles(riskScore)",
    "value": []
}
I assume I need to add data to make get a response.


Send and email
***************
Send an Email
***************

***Command***
POST v1.0 https://graph.microsoft.com/v1.0/me/sendMail

***Content 2***
{
  "message": {
    "subject": "Meet for lunch?",
    "body": {
      "contentType": "Text",
      "content": "You available.Origional - 1 line"
    },
    "toRecipients": [
      {
        "emailAddress": {
          "address": "my.emailaddress@provider.com"
        }
      }
    ]
  }
}

***Content 2***
{
  "message": {
    "subject": "Meet for lunch?",
    "body": {
      "contentType": "Text",
      "content": "You available. I'm free tomorrow and wondering if all is ok
      This works as well
      "
    },
    "toRecipients": [
      {
        "emailAddress": {
          "address": "my.emailaddress@provider.com"
        }
      }
    ]
  }
}

***Content 3***
{
  "message": {
    "subject": "Meet for lunch?",
    "body": {
      "contentType": "Text",
      "content": "You available. I'm free tomorrow and wondering if all is ok
      And how does this continue.
      Does it just go on
      yes
      Joe"
    },
    "toRecipients": [
      {
        "emailAddress": {
          "address": "my.emailaddress@provider.com"
        }
      }
    ]
  }
}


***Command*** Get logged in user’s OneDrive site
POST v1.0 https://graph.microsoft.com/v1.0/me/drive

***Command*** Get users whose email address starts with “Adele”
D.W. https://graph.microsoft.com/v1.0/users?$filter=startswith(mail,’adele’)
https://graph.microsoft.com/v1.0/users?$filter=startswith(mail,’adams’)
https://graph.microsoft.com/v1.0/users?$filter=startswith(mail,'A')
https://graph.microsoft.com/v1.0/users?$filter=startswith(givenName,'A')

***Command*** Get logged in user’s user profile picture
https://graph.microsoft.com/v1.0/me/photo/$value


Query
https://graph.microsoft.com/v1.0/users?$filter=startswith(mail,’adele’)
And the output is
{
    "error": {
        "code": "BadRequest",
        "message": "Invalid filter clause",
        "innerError": {
            "request-id": "c913cb68-3759-476e-89ba-718887cd7653",
            "date": "2018-12-20T23:10:41"
        }
    }
}

This means that the user Adele is not there!
Not that your query is bad.


Thursday 13 December 2018

Windows 10 not displaying login name or missing last login name

Windows 10 when connected to Azure AD stops showing the last login name or any of hte login accounts.

Not sure why, had a look and found different solutions.

Seems we need to merge a few together.


Issue 
Windows 10 does not show last logged in user name.
Potentially does not show any user names.
Doesn't give any clues as to whom is logging in.

Solution
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System]​
"HideFastUserSwitching"=dword:00000001​
"dontdisplaylastusername"=dword:00000000

First option enables fast user account switching & second confirms that the username is displayed.
Either by itself does not work.
You need both.

Save as a reg file and run.