Category: Office 365

SharePoint Rest API Does Not Allow Unindexed Queries

I’ve been developing code templates for CRUD operations (that’s a real acronym — Create, Read, Update, Delete) against SharePoint — we need to use SharePoint lists to replace database tables. Retrieving information worked fine until I tried to filter the data through the REST call. SharePoint throws a generic error about exceeding some admin-set limit. (1) I know the limit, I can see the limit. The limit is 5,000, and I know my filtered result set is 121 records. WAY lower than 5,000. Oh, and (2) I can run the query without the filter — I’m paging it! — and read all 29,887 records so what does the limit have to do with anything? Reasoning with an HTTP response … well, doesn’t work. No matter how unassailable my argument is, the API call still returned:

{"error":
    {"code":"-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
     "message":{"lang":"en-US",
       "value":"The attempted operation is prohibited because it exceeds the list 
                view threshold enforced by the administrator."}}}

It is, it turns out, a poorly worded error. I started thinking about the query limits on my LDAP servers — we have hard limits to operations and also require most people perform queries against indexed attributes. It’s computationally expensive to search through unindexed attributes (and the Right Thing To Do, generally speaking, is add an index for something that is a frequent query target). I wondered if there was an analogous “no unindexed queries” setting in SharePoint. Quick enough to test — add an index on the column(s) you use in the filter. In the site content listing, click the sideways hamburger menu by the list name. Select “Settings”

Scroll down to Index Columns and click the hyperlink.

Click ‘Create a new index’

Wait for the index process to complete, then try the filtered request again … I’ve got data! Evidently SharePoint ODATA filter queries to the REST API need to be performed against indexed columns. I’m sure Microsoft has that documented somewhere but quite a bit of Googling didn’t get me anywhere … so I’m posting this in case anyone else encounters the same error.

Did you know … you can archive a Team in Microsoft Teams?

Sometimes a project is done. You’ve used Teams to plan, coordinate, and implement the projected … and there’s a lot of good information in the Teams space … but there’s no need to continue the discussion. Did you know a Team can be read-only? This is called “Archived” – members can search and read content, but no new files or posts can be created.

To archive a Team, view your Teams. At the bottom of the Teams list, click the little gear.

I recommend changing the Team description to let others know it is archived – this is especially valuable if your team is Public as people may join intending to participate in an active discussion. To modify the Team description, select the sideways hamburger menu next to the Team name and select “Edit team”.

I prefix the description with “ARCHIVED:” … hoping people at least glance at the description. Click ‘Done’ to save your change.

To archive your Team, click the sideways hamburger menu again. Select “Archive team”.

You will be asked if you want to make the SharePoint site for the Teams space read-only as well – the answer is generally yes, but if you’re using the SharePoint site for more than just the Team then you do not want to check this box. Click ‘Archive’ to archive the Team.

Should you need to begin accepting new content in your Teams space, you can find the archived teams by expanding the “Archived” section.

Click the sideways hamburger menu and select ‘Restore Team’. This will move your Team back to the “Active” section and allow members to continue posting content.

 

Did you know … Microsoft Teams will mute your microphone when joining a large meeting?

Several offices ago I sat across from a L.O.U.D talker. A loud talker who was constantly on a conference call. I’d try to remember to mute my line before joining a bridge but didn’t always remember. Random background noise isn’t so bad when you’re about to talk to two or three close colleagues, but background noise becomes inundating when the twenty-something people in my department all join a bridge.

Microsoft Teams helps you avoid a deluge of background noise in your call. When you join a “large” meeting –more than five people already in the meeting – your microphone is muted as you join. A notification is displayed prior to joining the meeting to remind you the microphone will be muted.

You can click the microphone button in the meeting control bar to un-mute your microphone and begin speaking.

 

Did you know … you can insert 3D models in PowerPoint presentations?

Select “Insert” from the ribbon bar, then click the drop-down on “3D Models”. You can browse models from Remix 3D (“From Online Sources”) or models stored on your computer (“From a File”).

You can insert Filmbox (fbx), Object (obj), 3D Manufacturing (3mf), Polygon (ply), StereoLithography (stl), and Binary GL (glb) files.

STL files are commonly used when designing objects for 3D printing – here I’ve included a camera/telescope adapter.

Click on a 3D object and there is a rotation control

By changing an object’s orientation, you can use the “Morph” transition to rotate the object during your presentation. Right-click on the slide that contains your 3D object. Select “Duplicate Slide”.

Rotate the object on the new slide. Select “Transitions” from the ribbon bar, and select “Morph” as the transition.

When you play your presentation, the object will rotate as PowerPoint transitions between the two slides.

Now I know there aren’t a lot of uses for rotating 3D unicorns in business presentations, but manufacturers can embed 3D renderings of their products. Many architectural firms are providing 3D building designs that can now be included in PowerPoint presentations.

Did you know … you are talking on mute?

Well, probably not *right this second*. But we’ve all heard — err, not heard — someone else do it. We’ve all done it too. The Teams desktop client helps you avoid saying “Sorry, I was talking on mute”.

In the Teams desktop client, you will see an alert when your microphone is muted and sound is detected. Maybe you’re talking to someone who stepped into your office — just ignore the warning. If you’re trying to speak to the meeting, this is a great way to avoid having to repeat your entire thought because no one else heard it the first time!

Did you know … your mouse can be a laser pointer in PowerPoint?

This works both in “Reading View” and when you are playing a presentation. To enable “Reading View”, select “View” from the ribbon bar and click “Reading View”.

To play your presentation, select “Slide Show” from the ribbon bar and click “From Beginning” (or “From Current Slide” to begin the presentation from the current slide).

Hold the CTRL key, then left-click and drag your mouse. You’ll see a red circle which can be used to draw attention to elements of your slide.

If you want a different color “laser”, select “Slide Show” from the ribbon bar and click “Set Up Slide Show”.

Click the drop-down next to “Laser pointer color” and select the desired color. Click OK.

Now when you hold the CTRL key, left-click and drag the mouse … now there’s a blue laser pointer.

 

 

Did you know … you can remove duplicates in Excel?

I use Excel’s COUNTIF function a LOT for reporting. When I want to count the number of transactions that occurred per day (or during a date range), it’s easy enough to get the list of IF’s to count. But when I need to find the occurrence of different text strings, I need a unique list of the strings first. “Remove duplicates” quickly exactly what I need.

In this example, I have a list of all employees and contractor’s departments and titles – I want to know how many people are in each department and how many people have each title. Removing duplicates modifies the data, so the first step is to make a copy of the spreadsheet. Highlight the data. Select “Data” on the ribbon bar, then select “Remove Duplicates”

Select the column(s) where you want to remove duplicate data. This could be exact duplicates across multiple columns (e.g. the unique “City, State” combinations), or (in this case) I just want a unique list of departments. Click OK.

A summary will be displayed showing you how many records were removed and how many unique values remain.

Now that I have a complete listing of departments, I can use my COUNTIF function to show how many employees and contractors are in each department.

Remove duplicates only deletes records within the highlighted data. Here, I have a list of all employee titles next to the department and count info we just created. If I highlight just the ‘Title’ data and click “Remove Duplicates”, the department and count information is left unchanged.

Now I have a unique list of titles as well.

 

Did you know … you can be notified when changed are made to a SharePoint Online Wiki?

My group has started using a SharePoint Online Wiki – it’s a quick way to have an aesthetically pleasing knowledge base, but I’d rather not have to check “Updated Pages” periodically to see if there’s new content. Configuring SharePoint to notify me when new pages are added (or existing pages updated) avoids needing to check for new content.

From the Wiki, click “Page”

On the “Page” ribbon bar, select “View All Pages”

Click “Library” in the ribbon bar, then click the “Alert Me” drop-down.

Select “Set an alert on this library”

Configure your alert – you can receive notifications when new pages changed or added or only when new pages are added.

You can filter out changes you make by selecting “Someone else changes a wiki page”, and you can receive digest updates instead of getting a notification for each individual change. Click “OK” to save the notification, and you’ll start getting e-mails when the Wiki is updated.

If you want to adjust or cease receiving the alerts, select “Manage My Alerts” instead of ‘Set alert on this library’

Click on the alert name to edit the alert (or check the box in front of the alert name and select “Delete Selected Alerts” to remove it).

 

Creating An Azure Bot – Internally Hosted

While hosting a bot on the Azure network allows you to use pre-built solutions or develop a bot without purchasing dedicated hardware, the bots we’ve deployed thus far do not have access to internally-housed data. And program execution can be slow (expensive, or a combination of the two) depending on the chosen pricing plan. But you can build an Azure bot that is essentially a proxy to a self-hosted bot.

This means you can host the bot on your private network (it needs to be accessible from the Azure subnets) and access internal resources from your bot code. Obviously, there are security implications to making private data available via an Azure bot – you might want to implement user authentication to verify the bot user’s identity, and I wouldn’t send someone their current credit card information over a bot even with authentication.

How to Communicate with a Self-hosted Bot from Azure:

Register an Azure bot. From https://portal.azure.com, select “Create a resource”. Search for “bot” and select “Bot Channels Registration”.

On the pane which flies out to the right, click “Create” (if you will be deploying multiple self-hosted bots to Azure, click the little heart so you can find this item on “My Saved List” when creating a new resource).

Provide a unique name for your Azure bot. If you have not yet created a resource group, you will need to create one. Make sure the hosting location is reasonable for your user base – East Asia doesn’t make sense for something used on the East coast of the US!

Select the pricing tier you want – I use F0 (free) which allows unlimited messages in standard channels (Teams, Skype, Cortana) and 10,000 messages sent/received in premium channels (direct user interaction … which I specifically don’t want in this case). Then provide the endpoint URL to interact with your locally hosted bot.

Click “Create” and Azure will begin deploying your new resource. You can click the “Notifications” bell icon in the upper right-hand portion of the page to view deployment progress.

When deployment completes, click “Go to resource” to finish configuring your Azure bot.

Select “Settings” from the left-hand navigation menu, then find the application ID. Click “Manage”.

This will open a new portal – you may be asked to sign in again. You are now looking at the application registration in Microsoft’s developer application registration portal. There’s already an application secret created but beyond the first few letters … what is it? No idea! I’m a cautious person, and I don’t know if MS has embedded this secret somewhere within the bot resource. Since an application can have two secrets simultaneously, I do not delete the automatically-created secret and click “Generate New Password”.

A new pane will appear with your new secret – no, the one in the picture isn’t real. Copy that and store it somewhere – you’ll need to add it to your bot code later.

Close the application registration tab and return to the Azure portal tab. Click on “Channels” in your bot and add channels for any interactions you want to support. In this case, I want to publish my bot to Teams. There aren’t really settings* for teams – just click to create the channel.

* You can publish a bot to the Microsoft App Source … but is your bot something that should be available to the Internet at large? It depends! If you’re writing a bot to provide enterprise customers another support avenue, having the bot available through App Source makes sense. If you’re creating a bot to answer employee-specific questions, then you probably want to keep the bot out of App Source

Once the channel has been created, click on the “Get bot embed codes” hyperlink to obtain the bot URL.

Individuals can use the hyperlink provided to add your bot to their Teams chat.

Ok, done! Except for one little thing – you need something to answer on that endpoint we entered earlier. You need a bot! Microsoft publishes an SDK and tools for building your bot in .NET, JavaScript, Python, and Java.

In this example, I am using a sample Python bot. For convenience, I am handling SSL on my reverse proxy instead of using an ssl wrapper in my Python service. Grab the BotBuilder package from git (https://github.com/Microsoft/botbuilder-python.git)

Install the stuff:

pip3 install -e ./libraries/botframework-connector

pip3 install -e ./libraries/botbuilder-schema

pip3 install -e ./libraries/botbuilder-core

pip3 install -r ./libraries/botframework-connector/tests/requirements.txt

In the ./samples/ folder, you’ll find a few beginner bots. Rich-Cards-Bot requires msrest that has some async functionality and the branch in requirements.txt doesn’t exist. Tried a few others and never got anything that worked properly. Same problem with EchoBot-with-State. I am using Echo-Connector-Bot because it doesn’t have this msrest problem, and I can add my own state support later.

Edit main.py and add your Azure bot application id & secret to APP_ID and APP_PASSWORD

APP_ID = ”

APP_PASSWORD = ”

PORT = 9000

SETTINGS = BotFrameworkAdapterSettings(APP_ID, APP_PASSWORD)

ADAPTER = BotFrameworkAdapter(SETTINGS)

I stash my personal information in a config.py file and added an import to main.py:

from config import strDBHostname, strDBUserName, strDBPassword, strDBDatabaseName, strDBTableName, APP_ID, APP_PASSWORD

Tweak the code however you want – add natural language processing, make database connections to internal resources to determine responses, make calls to internal web APIs. I also added console output so I could debug bot operations.

When you’ve completed your changes, launch your bot by running “python main.py”

Now return to the Azure portal and select “Test in Web Chat” – this will allow you to test interactions with your bot. Ask questions – you should see your answers returned.

Once you confirm the bot is functioning properly, use the URL from the Teams channel to interact with your bot within Teams —

URL for my bot in Teams: https://teams.microsoft.com/l/chat/0/0?users=28:9699546d-fc09-41bf-b549-aed33280693a

The answer is served out of our home automation database – data that is only accessible on our private network.

Security – as I said earlier, you’ll probably want to take some measures to ensure access to your locally hosted bot is coming from legit sources. The app ID and secret provide one level of protection. If a connection does not supply the proper app ID & secret (or if you’ve mis-entered those values in your code!), you’ll get a 401 error.

 

But I don’t want the entire Internet DDoS’ing by bot either, and there is no reason for anyone outside of Microsoft Azure subnets should be accessing my locally hosted bot. My bot is hosted in a private container. The reverse proxy allows Internet-sourced traffic in to the private bot resource. Since communication from Azure will be sourced from a known set of networks, you can add a source IP restriction that prevents the general public from accessing your bot directly. See https://azurerange.azurewebsites.net/ for a convenient-to-use list of addresses.

 

Did you know … Microsoft Teams has a GUI text editor?

I like keeping my fingers on the keyboard, so I like using markdown in Teams messages (had to learn it for GitHub anyway!). The fact that hitting enter sends my posts in Teams? Generally awesome. I am not, however, the most succinct person; and a long series of thoughts is difficult to read as one continuous paragraph.

And using a new paragraph can serve to highlight a sentence without resorting to big bold text.

You can use shift-enter to move to a new line. Enter will still send your message.

But Teams has a GUI-driven composition mode — just click “Format” — that allows you to easily compose multi-line messages. In this editor, enter doesn’t send the message. It just moves the cursor to the next line.

There are a lot of formatting options available too. Basic typographical emphasis can be added to your text, and anything you type into the ‘Subject’ section will automatically be large, bold text.

The little highlighter icon will highlight text.

The underlined “A” changes the font color.

The icon with two A’s controls the text size.

So you can add really tiny or larger text.

Allowing you to use smaller or larger text.

You can create a bulleted list by clicking the icon with bulleted lines (or a numbered list by clicking the one with numbered lines). To end the list, either click the icon again or hit enter twice.

The quotation marks highlights text as a quote (two enters returns you to normal paragraph format here too), and hitting the drop-down next to “Paragraph” provides a list of pre-formatted text options.

A really cool feature for programmer-types – click the ‘code snipped’ icon.

A new composition window will be displayed – click the drop-down text to “Text” and select the programming language.

Text formatting will be applied to your code – the code I paste into Teams looks exactly like it does in my IDE.

When you have finished composing your message, you can click the little paper aeroplane to send your message. Or, if you prefer keeping your hands on the keyboard, hit ctrl-enter.