Hi There,
SQLBits is back in person, and after two years of Covid restrictions, it is great to see in-person events starting up again. I am fortunate to be speaking at SQLBits this year, but I have opted to present remotely as I was not comfortable traveling just yet. Thank you bits for giving a hybrid option to speakers and attendees, and I hope that this is something that stays in future conferences.
At my session this year, I am doing a 20-minute session on building a bot in Microsoft teams using Power Virtual agents. I intend that most of those minutes are spent doing the bot building. I have blogged about bots in teams before, and I really like the idea of this for a few reasons.
1) Cost - While Power Virtual Agents is a paid-for product when you deploy a bot to be used in teams, the cost is covered by most office365 licenses.
2) Bot's in Power Virtual Agents can easily integrate with Power Automate jobs.
3) So, you can quickly build a bot and deploy it in a teams environment so that users can kick off different types of automation from a common interface (the bot) in an environment that for many people is becoming their normal workplace. (Teams)
We all know that automation is good. It cuts down on mistakes and saves time, but IT is often the guardian of the automated task. So, IT may have a task that they can automate, but they still end up running it. Via Bots, we can put that automation directly into the hands of end-users. Now, I am not proposing that all automation be put into the user's hands, but certainly, there are use cases that I can think of
Example 1 - Changing User Access to a system. I have a client that uses Active Directory to control user access to their data reports. There is a continuous movement of people between roles, which ends up as tickets with the service desk to move a user from one AD group to another. End-users clearly shouldn't have access to AD, but what if we gave them a bot that did this in a controlled manner?
Example 2 - Reprocessing a power bi model. Some clients, especially around budget times, can ask for manual refreshes of power bi models when they have a deadline coming up. They email someone or ask IT support to do this. What if we gave them a bot that did it instead?
Example 3 - Updating "Config" data in a table. Another client maintains certain user-defined config data in SQL Tables. From time to time, it has to be updated. As there is no interface, it end's up as an IT support request, and someone then writes the SQL. What if there was a bot that did the update, based on user input?
I am sure you can add plenty of your own examples here. Personally, I use a bot to long my timesheets and to check my timesheets. It can also tweet, refresh power bi models and create Azure AD Users, but logging and checking timesheets is my daily interaction.
Back to my session at Bits. As sqlbits are running an arcade theme this year, I thought for my demo, I would create a Quiz Bot, with the following features
1) Start a Quiz and Capture your Gamer Name
2) Check your previous best streak of correct answers
3) Start the quiz and call an API to get a True/False question
4) Ask the player the question
5) Check the result and Game over, or ask another question if they are correct.
In addition, you can check the leaderboard. For a bit of fun, I made this quiz bot available to play here. Warning, this will be only online during SQL Bits, so get your game in now!
Some details on how this was built.
Power Virtual Agents were used to create the bot with two topics. (A topic is a specific conversation you want to conduct between the bot and a user). There is a topic to play the quiz and another topic to check the leaderboard.
A view of the topics in my quiz bot
Play the Quiz Topic - The bot flow is as follows. The boxes in blue are when the bot asks a question, shows a message, or checks some conditional logic. The boxes in orange are when the bot calls a Power Automate Job. Please note, The actual flow diagram in Power Virtual Agents is longer than this, so I created a simplified way of looking at it.
The basic building blocks in a Bot are, Ask a Question, Add a Condition, Call an Action, Show a Message and Redirect to another topic. You also have variables that hold the response from a question. This allows you to build basic flows that can show information, gather back user input, and based on that input, conditionally continue the flow. The real power is when you call a Power Automate Job via the Agent "Call and Action".
In order to allow an automate job to be called from Power Virtual agents, there is a Power Automate template that allows you to pass input from Virtual agents and return a value back. The following image show what the basic template looks like.
Once you are in your automate job, you of course can do all the sorts of things are that are possible from Power Automate, which at this stage I believe has over 300 connectors to different systems. The following image shows my automate job that generates the questions for my Quiz Bot. In this case, I am using the HTTP connector to call an API to get my quiz questions.
As you can see, once, you start combining automate with virtual agents, the possibilities of what can be achieved really open up. With over 300 connectors, you can get a sense of what sort of automation you can put into the hands of end-users, in a safe controlled manner.
Some other things that I get asked about Power Virtual Agents are;
How do you control access to the bot? - A bot is associated with a Teams "Team". When you create the bot, you select the team that can access it. So, if you wanted to have a general-purpose bot, that anyone in the company can interact with, then you need to deploy it to a team that everyone has access to. Now, this could be a wide audience, so you better be careful of what automation such a bot can do.
Could you further refine the security of a bot? - As such, no, if a person is in the team, they can access the bot. However, a bot is aware (in teams) of who the current user is, so you could create a "Check User Rights" power automate job that you call at the start of a topic flow, and return a Yes/No depending on if that user has the right to call that topic, and then conditionally branch in Power Virtual Agents.
Should I have many bots or many topics? - It depends on what you want to do and that association back to the team that you deploy the bot into. I prefer to have multiple topics within a bot, and then a user only has to interact with one or two bots. However, as you add on topics, you need to have clean lines between your topic trigger phrases, otherwise, a user to start a topic other than the one they want to.
Are there limits? - Yes, of course. When you deploy to teams you have the following key limits. A user can have 10 sessions with a bot in a 24 hour period. A session is an interaction with a bot, up to 100 "turns" (a turn is a single back or forth interaction between user and bot), or 60 minutes. There is also a limit of 2000 power platform requests in 24 hours.
My talk at SQL Bits is on Friday 11th March. I might see you there, or maybe you are reading this afterward. Either way, I hope this post has managed to show you a few things and give you ideas of what you can achieve.
For an additional bit of fun, if you play my quiz and manage to get a top 3 position, you will win a prize. I will ship out a retro PacMac t-shirt to you. Best of luck, the top of the leader board has a streak of 14! Here is the link to the game.
Thanks for reading,
Mark