JMESPath queries for CLI for Microsoft 365

Within the CLI for Microsoft 365 we use JMESPath write execute expressions against returned data. It is a great way to filter down a dataset. But it also has a somewhat steep learning curve, at least it did for me coming from a PowerShell world. So for the sake of time I captured some of my most used scenario’s. This allows me to quickly find the query and scenario I am looking at.

JMESPath

The CLI documentation explains some of the basics of filtering. So for the common scenario’s like filtering on a Title it still I found the cases pretty straight forward. You can filter on any properties that are returned.

$ m365 spo site list --query "[?Title == 'Retail']"
Title: Retail
Url  : https://contoso.sharepoint.com/sites/Retail

Yet for some other options I found it harder to find samples, especially if the CLI does return nested objects.

CLI for Microsoft 365 quirks

Currently the CLI can have two different types of output. It either is an object with an array of items, or the data is returned as part of the value object. That means that there are two distinct ways to filter or query on the result set. So if you are working with CLI it makes sense to pass the --output json parameter to see how the response is returned before writing your query. Luckily you can test your queries live at JMESPath.org. For both output options I use a small sample to quickly test my query.

Some simplified JSON for listing all sites using the m365 spo site classic list --output json would look as follows.

 [{
    "_ObjectType_": "Microsoft.Online.SharePoint.TenantAdministration.SiteProperties",
    "AllowDownloadingNonWebViewableFiles": true,
    "AllowEditing": false,
    "Title": "Demo 1"
 },
 {
    "_ObjectType_": "Microsoft.Online.SharePoint.TenantAdministration.SiteProperties",
    "AllowDownloadingNonWebViewableFiles": false,
    "AllowEditing": false,
    "Title": "A Demo 2"
 },
 {
    "_ObjectType_": "Microsoft.Online.SharePoint.TenantAdministration.SiteProperties",
    "AllowDownloadingNonWebViewableFiles": true,
    "AllowEditing": false,
    "Title": "Sample 1"
 }]

The most common queries are either to filter based on a specific property, or use a contains, starts_with or ends_with on a specific property. The final scenario is to limit the return values to have a smaller data set.

• [?Title == 'Demo 1'] would return only the first item as it matches on the title Demo 1
• [?contains(Title, 'Demo')] would return the first two items as it matches the Title on the word Demo
• [?contains(*, 'Demo 1')] would return any item in the array where the value of any property would be Demo 1, currently only the first item.
• [?starts_with(Title, 'Demo')] would only return the first item as it filters the title to start with Demo
• [?ends_with(Title, '1')] returns the first and last item, as the title ends with a 1
• [?contains(Title, 'Demo') && AllowDownloadingNonWebViewableFiles] returns only the first item as it combines two filters.
• [*].Title returns only the Titles for all items.
• [*].{Title: Title} returns all items as array with a Title property.

You can also use filters on sizing using comparison operators [?value < 100]. This can come in handy if you want to filter out based on sizes. Since the CLI for Microsoft 365 can also return values as part of a value object. The queries for those responses are slightly different. Lets assume we have a somewhat similar result for the m365 spo user list --webUrl https://contoso.sharepoint.com/ --output json

{
  "value": [
    {
      "Id": 7,
      "LoginName": "i:0#.f|membership|[email protected]",
      "Title": "Garth North",
      "PrincipalType": 1,
      "Email": "[email protected]",
      "IsEmailAuthenticationGuestUser": false,
      "IsShareByEmailGuestUser": false,
      "IsSiteAdmin": true,
      "UserId": {
        "NameId": "xxxx",
        "NameIdIssuer": "urn:federation:microsoftonline"
      },
      "UserPrincipalName": "[email protected]"
    },
    {
      "Id": 2,
      "LoginName": "i:0#.f|membership|[email protected]",
      "Title": "Admin",
      "Email": "[email protected]",
      "Expiration": "",
      "IsEmailAuthenticationGuestUser": false,
      "IsShareByEmailGuestUser": false,
      "IsSiteAdmin": true,
      "UserId": {
        "NameId": "xxxx",
        "NameIdIssuer": "urn:federation:microsoftonline"
      },
      "UserPrincipalName": "[email protected]"
    }
  ]
}

• value[?Title == 'Garth North'] would return only the first item as it matches on the title Garth North
• value[?contains(Email, 'contoso')] would return all items as it matches the E-mail on the word contoso
• value[?contains(*, 'Garth North')] would return any item in the array where the value of any property would be Garth North, currently only the first item.
• value[?starts_with(Title, 'Gart')] would only return the first item as it filters the title to start with Garth
• value[?ends_with(UserPrincipalName, '.nl')] returns all items, as the UserPrinicipalName ends with a .nl
• value[?contains(Title, 'Garth') && IsSiteAdmin] returns only the first item as it combines two filters.
• value[*].Title returns only the Titles for all items.
• value[*].{Title: Title} returns all items as array with a Title property.

A final remark is that all JMESPath queries are case sensitive! I have not found a way to allow for case sensitive options. If you have a solution for that let me know! At the JMESPath site you can find an extensive list of samples. But for most cases in the CLI for Microsoft 365 you should >have enough on the samples above.

As of V4 of CLI for Microsoft 365 we no longer use the value wrapper so you won’t find any commands that use it.

Update Sorting options

A recent scenario required me to apply some more logic with JMESPath. Besides filtering I had to apply sorting as well. Obviously you can sort your data set using PowerShell or Bash. However in this case I wanted to apply the logic with JMESPath so it would work cross platform. Yet it took me quite some time to figure out the correct syntax, so for an update to my cheat sheet was in order! As with the previous sample, some dummy json and a set of sample queries to get you started!

[
  {
    "Report Refresh Date": "2021-06-15",
    "User Principal Name": "[email protected]",
    "Is Deleted": "False",
    "Deleted Date": "",
    "Last Activity Date": "2020-07-07",
    "Viewed Or Edited File Count": "0",
    "Synced File Count": "0",
    "Shared Internally File Count": "0",
    "Shared Externally File Count": "0",
    "Visited Page Count": "0",
    "Assigned Products": "OFFICE 365 E3",
    "Report Period": "7"
  },
  {
    "Report Refresh Date": "2021-06-15",
    "User Principal Name": "[email protected]",
    "Is Deleted": "False",
    "Deleted Date": "",
    "Last Activity Date": "",
    "Viewed Or Edited File Count": "152",
    "Synced File Count": "0",
    "Shared Internally File Count": "0",
    "Shared Externally File Count": "0",
    "Visited Page Count": "0",
    "Assigned Products": "OFFICE 365 E3",
    "Report Period": "7"
  },
  {
    "Report Refresh Date": "2021-06-15",
    "User Principal Name": "[email protected]",
    "Is Deleted": "True",
    "Deleted Date": "2021-05-15",
    "Last Activity Date": "",
    "Viewed Or Edited File Count": "0",
    "Synced File Count": "0",
    "Shared Internally File Count": "0",
    "Shared Externally File Count": "0",
    "Visited Page Count": "0",
    "Assigned Products": "OFFICE 365 E3",
    "Report Period": "7"
  }
]

• sort_by(@, &"Last Activity Date") would return the result set with the oldest Last Activity Date on top. That means empty dates first.
• reverse(sort_by(@, &"Last Activity Date")) would reverse the order, shows the newest last activity date on top.
• reverse(sort_by(@, &"Viewed Or Edited File Count")) would return the user with the most edited items on top.
• reverse(sort_by(@, &"Viewed Or Edited File Count"))[*]."User Principal Name would sort and only return the User Principal Name sorted by the most edited files on top.
• reverse(sort_by(@, &"Viewed Or Edited File Count")) | [0]."User Principal Name" would sort and return the User Principal Name for the user with the most edited files.
• reverse(sort_by(@, &"Viewed Or Edited File Count")) | [?"Is Deleted" == 'False']."User Principal Name" sorts by then Viewed Or Edited File Count, then filters out deleted users and finally returns the User Principal Name

The last two commands show in the previous sample use a | to pipe the command. This allows you to further filter or tweak your set. Besides using | [*] to retrieve all results, or using | [0] to get the first item from the array you can also specify | [0:3] to get the top 3 items. Given the previous data sample you can thus use reverse(sort_by(@, &"Viewed Or Edited File Count")) | [0:3]."User Principal Name" would sort and return the User Principal Name for top three users with the most edited files.

Hope this helps you with querying against the results of the CLI for Microsoft 365 🔍!