Tuesday, October 30, 2018

Reporting on Skype for Business Messaging Activity using the Conversation History Folder in Exchange

Skype for Business (and formally Lync before that) uses the Conversation History folder in an Exchange mailbox to store the history of your IM conversations. One message in your mailbox may represent many messages in an IM conversation where the conversation is broken out itself into an XML string in the Message in the "ConversationXml.{CA2F170A-A22B-4f0a-B899-93439DEC3FBC}" extended property which looks something a little bit like this if you dumped its values

Which comes from the below underlying MAPI property

While Instant Messaging hasn't been around as long as Email the statistics and Reporting on its usage even from third party vendors is pretty underwhelming as the data made available for reporting is limited. If however we take advantage of a Mailbox API like EWS or the Graph API and delve into the above information you can start to produce some more useful statistical reporting about your IM traffic. This could be especially useful at the moment if you want to start measuring more accurately the engagement across Skype vs Teams (in regards to IM anyway). I've created an EWS script for doing this that firstly enumerates all the conversation history items for a particular time frame, then gets the above conversation XML for each conversation thread (this requires a second request to Exchange because of the size of the property). Then it parses the XML into a logentry that can be used either itself or summarized for further reporting. I have 3 sample summary reports that does this in the script eg

Report by date

Report by IM's received From

 Report by IM's Sent To

Or you just have the log stream which you can crunch further in something like excel

I've put the code for this script up on GitHub here https://github.com/gscales/Powershell-Scripts/blob/master/SkypeConversationStats.ps1

The script will work in the cloud or OnPrem, if you are using it onprem make sure you using the -basicAuth switch in the cmdlet as the authentication defaults to using oAuth in Azure. A few examples of running the script

If the sip address your reporting is different from the mailbox SMTP (other then the sip prefix) you need to use the -sipaddress parameter to specify this address. This is used in the Sender calculations just pass this in minus the sip: prefix.  

To Get the last 30 days of conversation logs from a Mailbox in the cloud use

Get-ConversationLog -MailboxName gscales@datarumble.com -Days 30
for OnPrem use

Get-ConversationLog -MailboxName gscales@datarumble.com -Days 30 -basicAuth
To use the Reporting options for report by date (see screenshot 1)

Get-ConversationLog -MailboxName gscales@datarumble.com -Days 30 -reportbyDate 
-htmlReport | Out-file c:\temp\30dayreport.html
if you want the same report in csv use

Get-ConversationLog -MailboxName gscales@datarumble.com -Days 30
 -reportbyDate | ConvertTo-Csv -NoTypeInformation -path c:\temp\30dayreport.csv 

Get-ConversationLog -MailboxName gscales@datarumble.com -Days 30
To report the top senders that sent this Mailbox an IM

Get-ConversationLog -MailboxName gscales@datarumble.com -Days 30
To report the top recipients of IM's

Get-ConversationLog -MailboxName gscales@datarumble.com -Days 30
Hire me - If you would like to do something similar to this or anything else you see on my blog I'm currently available to help with any Office365,Microsoft Teams, Exchange or Active Directory related development work or scripting, please contact me at gscales@msgdevelop.com(nothing too big or small).

Friday, October 19, 2018

Using the LAPFID (Last Active Parent FolderId) in EWS and the Graph API in PowerShell when reporting on Deleted Items

A little background

The LAPFID property is an extended property that gets set on an Exchange Store Item when its deleted (any type of delete soft or hard) that is the enabler for the original folder item recovery feature that rolled out to both Exchange Online and Exchange OnPrem (2016) last year. If you where to look at this property in a MAPI editor it would look like the following

To understand what that property Id value represents you need to first understand a little bit more about the Folder EntryId format (PR_EntryId) that exchange uses which is documented in this Exchange Protocol Document https://msdn.microsoft.com/en-us/library/ee217297(v=exchg.80).aspx . A different visual representation of this with the individual components highlighted would look something like this

Hopefully from this you can see that the LAPFID is comprised of the DatabaseGUID and GlobalCounter constituents of the FolderEntryId. To make sense of the LAPFID you need to resolve it back to the actual Mailbox Folder that it represents. A couple of ways you could go about its is to reconstruct the PR_EntryId using the values from the LAPFID and the header values from another folder in the Mailbox which should be the same and then convert that to a EWSId using ConvertId and then Bind to the Id that is returned. The other way which is the method I've used is to enumerate all the Folders in the Mailbox (and Archive store if it exists) and then get the DatabaseGuid + GlobalCounter combinations from the PR_EntryID of those folders, stick this into a Hashtable and then you can look up that for each item to do the resolution process. One issue is that the folder in question in the LAPFID may have been deleted itself which would make both those processes fail.

Getting the LastActiveParentEntryId property in EWS and the Graph API

To get this property value in EWS you need to request the extended property like the following in the EWS Managed API

$LastActiveParentEntryId = New-Object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(0x348a, "Binary")

or if your using the Graph API your request should look something like the following

$expand=SingleValueExtendedProperties($filter=Id eq 'Binary 0x348A'))

Putting them to work in a Script

Apart from the obvious thing of being able to restore items to their original location the LAPFID property can have a variety of reporting uses. I've put together a small module to allow easy access to this property along with some expansion to strongly typed properties so it will show the original folderpath and also the original EWS Folder (if you want to do the restore). I've also done the same for the Graph API in my Exch-Rest module where I've added some cmdlets to view all the deleted and recoverableItems folders and to retrieved this property and do the expansion of the Folder.

Some modifications to the base EWS Script

Because its now more important then ever to stop using Basic authentication for anything see the escalation https://blogs.technet.microsoft.com/exchange/2018/10/17/disabling-basic-authentication-in-exchange-online-public-preview-now-available/  I've made changes to the base script I use for EWS and I've now defaulted the Authentication to use oAuth. While if you are using onPrem you may still need to use basic Auth I have provided provision for doing this as well but will require that you use the -basicAuth switch. I also have some different options for oAuth eg if you want to use the ADAL.dll to generate the access token the front-end cmdlet will take the AccessToken via the -AccessToken parameter if nothing is passed in it will use some generic PowerShell methods to generate an Access Token.

Some Examples in Action

The EWS Module has a cmdlet called Get-LAPFIDItemsFromFolder which has three switch's that can be used to access either the DeletedItemsFolder, RecoverableItemsDeletions and  RecoverableItemsPurges. Or you can also use the -FolderPath parameter and pass in a FolderPath if you really like.

eg to get the items form the dumpster's deletions folder use something like

By default only the Primary Mailbox of the user you enter is checked but if you want to also check the Archive you can use the Archive switch eg

Get-LAPFIDItemsFromFolder -MailboxName jcool@datarumble.com -recoverableItemsDeletions -Archive | select LastActiveParentfolderPath,Subject,Size

Or maybe you want to see how many items from each LAPFID folder in the DeletedItems folder

Get-LAPFIDItemsFromFolder -MailboxName gscales@datarumble.com -deletedItems | Group-Object LastActiveParentfolderPath

Or by RetentionDate

Get-LAPFIDItemsFromFolder -MailboxName gscales@datarumble.com -deletedItems | Group-Object RetentionDate | Sort-Object Count -Descending

Show items from a particular LAPFID

Get-LAPFIDItemsFromFolder -MailboxName gscales@datarumble.com -deletedItems | Where-Object {$_.LastActiveParentFolderPath -eq "\Junk E-mail"} | select DateTimeReceived,Subject,Size

To Restore an item to its original folder you just use the normal EWS Move operation, because I'm using the EWS Managed API there is a Move method on Each object that can be called so you just need to pass in the folderId of the folder you want to move it to. In the case the original folder you can just pass in the expanded value. eg if I get a collection of items like

$items =  Get-LAPFIDItemsFromFolder -MailboxName gscales@datarumble.com -deletedItems

and you want to restore the first item (which would the also the last item deleted all you need is)


this would move the items back to its LastActiveParentFolder value.

Exch-REST and the Graph API

The Graph API has a few restrictions around Deleted Items when compared against EWS eg because the Deleted Items access method uses the Mail Endpoint you will only be able to get Items with a ItemClass (or Subclass) of IPM.Note. So while other things like contacts and appointments maybe in these folder these items won't show in the API. Simular with Folders if the folder doesn't have a Class or Subclass of IPF.Note it won't show. And lastly you can't currently access the Archive using the Graph API. (but apart from that)

In the Exch-Rest module there a 3 cmdlets that can be used to return the information

Because each of the cmdlets in this module build on each other you can do some interesting things like process the Antispam header to get the DMARC and DKIM information and show that along with the LAPFID eg

 Get-EXRDeletedItems -ReturnLastActiveParentFolderPath  -ReturnInternetMessageHeaders -ProcessAntiSPAMHeaders | select LastActiveParentFolderPath,DMARC,DKIM

Or the Sentiment property to see if people are deleting negative email eg

Get-EXRDeletedItems -ReturnLastActiveParentFolderPath  -ReturnSentiment | select LastActiveParentFolderPath,Sentiment,Subject | where-object {$_.Sentiment -ne $null}

The EWS PowerShell Module for this can be found on GitHub Here https://github.com/gscales/Powershell-Scripts/blob/master/LAPFIDModule.ps1 , the Exch-REST Module is available from the PowerShell Gallery https://www.powershellgallery.com/packages/Exch-Rest and GitHub https://github.com/gscales/Exch-Rest

Hire me - If you would like to do something similar to this or anything else you see on my blog I'm currently available to help with any Office365,Microsoft Teams, Exchange or Active Directory related development work or scripting, please contact me at gscales@msgdevelop.com(nothing too big or small).

Wednesday, October 10, 2018

Accessing Public Folder data from a Microsoft Teams Tab application using EWS

For a long time now Public Folders have been a good way to share information and have threaded conversations across organizations. However time and technology have surpassed them somewhat of late, if your using Office365 there was first the introduction of Unified Groups and now Microsoft Teams has come to maturity both of which offer a better user and technical solution to needs Public Folders fulfil. The benefits of these newer technologies is they allow you to do more in the same context while not giving up on the existing features that Public Folders may have been giving you (outside of access in Outlook). But where the rubber meets the road at the coal face its not always as easy to migrate from one solution to another, so in this post I'm going to look at how you can access data (email etc) in an Exchange Public folder from within a Team's Tab application. This type of approach may give you some flexibility and options while your dealing with a Migration between the two or just during that tricky cutover period where you don't have everyone onboard at once.

What it looks like

the Text box is a simple search bar that takes a KQL query so by default it will just return everything but if you entering a query and hit the refresh button you will get filtered results eg

Technical Challenges  

Over the past couple of months I've posted a few Teams tab applications that expose Exchange data using the Graph API as the interface into the Exchange Store. Public Folders aren't yet accessible via the Graph API so to access these you need to use Exchange Web Services. While this isn't too difficult in itself because EWS doesn't support CORS it means you can't write JavaScript code that will run in the client browser to access EWS directly.

Cross-Origin Resource Sharing (CORS) is a mechanism that uses additional HTTPheaders to tell a browser to let a web application running at one origin (domain) have permission to access selected resources from a server at a different origin. In the context of a Teams tab application that wants to access Public folders via EWS the source domain will be where the teams tab application pages are being hosted and the target would be EWS outlook.office365.com, because the server where EWS is hosted doesn't support CORS the browser will block this request because the correct headers aren't returned when the request is pre-flighted (http Options request)
To mitigate against CORS you can use a Proxy like this node.js server https://www.npmjs.com/package/cors-anywhere then if your writing your client side code in something like Angular you can proxy your requests via this. Another way which is the one I've chosen is to write all the EWS logic and EWS calls in node.js and then create a simple Rest API for the Node server that can be called from the client side code that runs in Teams. This has a few benefits as there are multiple requests to get the public Folder and correct routing header initially. With this method you can run all these from a cloud hosted server that should have lower latency then the client which should mean better performance and also the service itself can be reused from other applications.

Node.js app

The node.js server is a relatively straight forward app that uses Express and has one route that become the REST endpoint that the Teams Tab application will call. I've used a JavaScript port somebody did of the EWS Managed API https://github.com/gautamsi/ews-javascript-api  which I found was reasonably easy to use and made dealing with the EWS side of the code a lot faster because I could port over existing C# code. One of the least fun things to do when dealing with Public Folder and EWS is to workout the correct routing headers https://docs.microsoft.com/en-us/exchange/client-developer/exchange-web-services/public-folder-access-with-ews-in-exchange but I have some code that will handle this and also finding the Public folder from a path using multiple shallow traversals. To allow paging past 1000 object (or I made the default page size about 100 to maintain reasonable performance) the rest endpoint support calling it again with an EWS folderId as to avoid the discover process a second time. Other then that the EWS code is pretty run of the mill findItems, folderfinds and bind.

Teams Tab application

Like the other Teams tab applications I wrote this use the Teams Tab Silent authentication method but instead of getting a token for the Graph Endpoint its gets it for the EWS endpoint outlook.office65.com. For displaying the email data I've used the tabulator JavaScript library which has a few nice feature I like.  Each of the email is clickable and will open any of the items you click up in a new tab in OWA eg

Using this app

Unlike the previous tab apps I've published which could just be used straight from GitHub this one requires some technical understanding. Firstly you will need to host the node.js server somewhere eg Azure is best place as that gets you as close to the Exchange servers that will be serving the content. Once you have this hosted all the configuration for the app is held in the appconfig.js file https://github.com/gscales/gscales.github.io/blob/master/TeamsPublicFolder/app/Config/appconfig.js

const getConfig = () => {
   var config = {
        clientId : "2417973f-0680-4ea0-a844-e6d414cee4d4",
        redirectUri : "/TeamsPublicFolder/app/silent-end.html",
        authwindow :  "/TeamsPublicFolder/app/auth.html",
 hostRoot: "https://gscales.github.io",
 folderpath: "\\Childfolder\\childfolder",
 ewsproxy: "https://459e25ee.ngrok.io/FolderItems",
   return config;

clientId is for the application registration you should create for this app, to access EWS you only need to the following 1 permission

folderpath is the path to the public folder (because this is a js file there is one more \ the normal to escape the path \.

ewsproxy is the REST endpoint of node.js server in this example I was hosted in locally on my pc and then exposting that endpoint using ngrok which is a great tool for prototyping.

You also need to host the Tab application pages somewhere and modify the manifest to indicate where the location is.

other this this the normal

Side Loading - To use custom tab applications you first need to enable side loading of Apps in the Office365 Admin portal ref .The important part is  "Sideloading is how you add an app to Teams by uploading a zip file directly to a team. Sideloading lets you test an app as it's being developed. It also lets you build an app for internal use only and share it with your team without submitting it to the Teams app catalog in the Office Store. "

As this is a custom application you need to use the "upload a custom app" link which is available when you click ManageTeam-Apps tab see

(Note if you don't see  the "upload a custom app" check that you have side loading of apps enabled in your tenant config)

What you upload here is a Zip file containing your manifest file and two images that you manifest refers to for eg
   "icons": {
    "outline": "Outline32.png",
    "color": "Colour192.png"

For this sample this is located in https://github.com/gscales/gscales.github.io/tree/master/TeamsPublicFolder/TabPackage

These icons are what is used in the UI to represent your application.

 The files for the tab application can be found on GitHub https://github.com/gscales/gscales.github.io/tree/master/TeamsPublicFolder

The Node.Js solution for the EWS proxy can be found in this repo https://github.com/gscales/TeamsPublicFolder-NodeEWSProxy

There are some todo's that still need to be done eg the pagination is a little clunkily as there is a differences between paging between what is returned (eg in EWS you page back item in lot of 1 to 1000) and paging between all the items in a Public Folder so there is separate footer button to get the next 100 items (from the server) and separate buttons to page the results. Most of these are relativity easy to solve if your interested in using this considering hiring me to complete it and share it with others :).

Hire me - If you would like to do something similar to this or anything else you see on my blog I'm currently available to help with any Office365,Microsoft Teams, Exchange or Active Directory related development work or scripting, please contact me at gscales@msgdevelop.com(nothing too big or small).