CODEWITHSUNDEEP
odataoffice365-restapi
Tony Hinkle
Tony Hinkle

Reputation: 4742

Get latest record for each user with ODATA

Due to the PowerShell methods of getting mailbox statistics from Office365 taking about 2 seconds per mailbox, I am working on getting the data from Office 365 Reporting web service, which takes only a few seconds for each 2000 mailboxes.

The problem I'm running into is that the stats are updated periodically and some historical data is kept, so there are numerous records for each user. I only want to get the latest record for each user, but I haven't been able to find a way to do that. The closest I've come is to use $filter=Date ge DateTime'2016-03-10T00:00:00' where the date is concatenated to a couple of days ago. Theoretically, if I sort by Date desc I should get the latest records first, and if there is a user that has a record for 3/10 and 3/11, the 3/11 record would get pulled first, which would work for me. But regardless of how I do the sort it seems to come back with the older records first.

Ideally, I would like to be able to set criteria so that it only returns the latest record for each mailbox, but I can't seem to figure out or find how to do that. The closest I've been able to come is to just start running queries filtered on specific dates, walking the date back a day on each query.

If I can get the latest records to be returned first, I would be able to work with that because I can just discard a record if I've already received a later one.

https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailboxUsageDetail/
?DelegatedOrg=nnn.onmicrosoft.com&$select=Date,WindowsLiveID,CurrentMailboxSize
&$filter=Date ge DateTime'2016-03-08T00:00:00'&$orderby=Date desc

So the questions are:

  1. Is there a way to specify criteria so that only the latest record for each user is returned?
  2. Is there a way to get it to order by Date descending--what am I doing wrong with the $orderby?

Thanks!

Upvotes: 3

Views: 7880

Answers (1)

Vinit
Vinit

Reputation: 2607

  1. You can use $top=1 to get latest record by applying $orderby on date (desc). $filter and $skip may not require in this case. https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailboxUsageDetail/?DelegatedOrg=nnn.onmicrosoft.com&$select=Date,WindowsLiveID,CurrentMailboxSize&$orderby=Date desc&$top=1

  2. Your query looks fine, here is an another example from Odata sample service to get employee detail with most recent birth date.

http://services.odata.org/V4/Northwind/Northwind.svc/Employees?$select=EmployeeID,FirstName,LastName,BirthDate&$orderby=BirthDate%20desc&$top=1

Upvotes: 4

Related Questions