SharePoint Wiki Bot - How to automate the Azure QnAMaker bot knowledgebase
<TL;DR>
This post shows a proof of concept implementation of an Azure QnAMaker bot that gets its knowledgebase updated by changes in a SharePoint wiki. The main idea is, that you use a wiki as some sort of knowledge management system and that changes to the content automatically get pushed to the bot’s knowledge base. By adding a SharePoint Webhook to the wiki pages library you can trigger an Azure web job that manages the bot’s knowledge base. By using the Azure QnAMaker API the web job publishes changes to the knowledge base so that a connected Azure bot can use it as it’s backend data. This way you can add a heading and some text to the SharePoint wiki page and about two minutes later you can ask the bot in Skype or Microsoft Teams about it. Kudos to: Wictor Wilén, Bert Jansen and Paul Schaeflein.
The presented solution is not production ready, not even close. I currently don’t have the time to level up my rusty developer skills to polish the things to a point needed for a real-world use, but I decided to publish it anyway. So feel free to look through the code on github or just read here to get the bigger picture. I hope it brings some value to you and again do not use this in your production environment.
Idea
The basic idea is to create an Azure bot that uses a SharePoint wiki as its data provider. After last week and my post about how to create a bot for Microsoft Teams without using code, I finally decided that it was time to fire up Visual Studio and try something. As already said, my developer skills are pretty rusty. I’m far away from all the “new” features of C#, the last time I did proper coding was around 2011-2012, so this solution is far from beeing perfectly polished or efficient coded. It’s just a way to get the job done and I’m pretty sure most of you out there can get the job done with a cleaner code base. Anyway, to get an idea of the overall picture let’s have a look at the architecture of this little project. 
As outlined above, the system consists of different components that are connected through some Azure services. Let’s go into details in the following chapters.
SharePoint wiki library
For this example, we assume our users have a wiki library as some sort of knowledge management solution. To be able to get the content in a question and answer style we use the built-in formats of headings and paragraphs. All the later applied magic by the Azure Bot Framework and its AI needs the data stored in a decent way. As we use the Azure QnAMaker we need our knowledge in a question and answer format. The easiest way of getting there is to use the headers as our questions and the sibling paragraphs as the answers like: 
Of course you can also base this solution on more sophisticated wiki structures with a table of content like this here by Stefan Bauer: 
Again, the main idea is getting your data in a question and answer sort of way. That’s why the following code is used to parse the PublishingPageContent field of the page:```
String wikicontent = item[“PublishingPageContent”].ToString();
var html = new HtmlDocument();
html.LoadHtml(wikicontent);
string header = “”;
foreach (HtmlNode node in html.DocumentNode.ChildNodes) { if (node.Name.StartsWith(“h”)) { header = node.InnerText; } else if (node.Name.StartsWith(“p”)) { if (!qna.ContainsKey(header)) { qna.Add(header, node.InnerText); } else { var existingValue = qna[header]; qna.Remove(header); qna.Add(header, existingValue + " " + node.InnerText); } } }
#### Azure Function
As the given solution runs in SharePoint Online we use a webhook to trigger a request whenever something happens within our page library. I used this article [https://dev.office.com/sharepoint/docs/apis/webhooks/get-started-webhooks#step-5-add-webhook-subscription-using-postman](https://dev.office.com/sharepoint/docs/apis/webhooks/get-started-webhooks#step-5-add-webhook-subscription-using-postman) and the given tooling to configure the webhook. To get the project started I downloaded the reference implementation of the PnP core team on this topic: [https://github.com/SharePoint/sp-dev-samples/tree/master/Samples/WebHooks.List](https://github.com/SharePoint/sp-dev-samples/tree/master/Samples/WebHooks.List) The reference implementation uses the following code to store the webhook data to the storage queue:```
/// <summary>
/// Add the notification message to an Azure storage queue
/// </summary>
/// <param name="storageConnectionString">Storage account connection string</param>
/// <param name="notification">Notification message to add</param>
public void AddNotificationToQueue(string storageConnectionString, NotificationModel notification)
{
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(storageConnectionString);
// Get queue... create if does not exist.
CloudQueueClient queueClient = storageAccount.CreateCloudQueueClient();
CloudQueue queue = queueClient.GetQueueReference(ChangeManager.StorageQueueName);
queue.CreateIfNotExists();
// add message to the queue
queue.AddMessage(new CloudQueueMessage(JsonConvert.SerializeObject(notification)));
}
```All this runs in an Azure Function you need to define. For details go the webhook details link above, there is everything you need to understand the principles and the given implementation on github is a million ways better than mine.
#### Azure Web job
The web job is the main component of the solution. It takes the information from the storage queue and processes the changes in the SharePoint wiki library and pushes them to the QnAMaker knowledgebase. To get the data from the pages into the right json format I ended up using this representation:```
public class QnaPair
{
\[JsonProperty("answer")\]
public string answer { get; set; }
\[JsonProperty("question")\]
public string question { get; set; }
}
public class AddToKB
{
\[JsonProperty("qnaPairs")\]
public IList<QnaPair> qnaPairs { get; set; }
\[JsonProperty("urls")\]
public IList<string> urls { get; set; }
}
```Because of the json properties applied we create QnaPairs that hold our questions and answers. This way the wiki headlines and paragraphs end up here and can be converted to a json string that is already aware of the needed format of the QnAMaker API. The best way to start understanding this API is at its [documentation page](https://westus.dev.cognitive.microsoft.com/docs/services/58994a073d9e04097c7ba6fe/operations/58994a073d9e041ad42d9baa) where you can play around with your defined knowledge bases.  This is for example the needed format for the json body of an create request:```
{
"name" : "My Knowledgebase",
"qnaPairs": \[
{
"answer": "You can reserve your launch time ticket online, via your mobile device, or onsite at our admissions windows or self-service kiosks. Select from a variety of options to determine the ticket type, day and time that works best for you. Launch times are set for every 30 minutes from open to close.When planning your visit, pre-purchasing tickets online is the best way to go. Save time, save money and reserve the time you want! Tickets purchased onsite at our admissions windows or self-service kiosks may be limited the day-of purchase.",
"question": "How do I buy a timed ticket?"
},
{
"answer": "No. Unfortunately due to the volume of people and limited space on the elevators, strollers must be left in your car or parked in our designated stroller area.",
"question": "Will my stroller be allowed in?"
}
\],
"urls": \[
"http://www.seattle.gov/hala/faq"
\]
}
```Given this target represntation we still need to grab all that from SharePiont first. In order to do is we use the GetChanges() method of our CSOM list object. To get an overview of this technique start by reading this [introduction](https://www.schaeflein.net/reading-the-sharepoint-change-log-from-csom/) to the topic. As we are only intressted in changes that updated a list item I configured the query like this:```
Web web = cc.Site.OpenWebById(new Guid(webID));
cc.Load(web);
List ls = web.Lists.GetById(new Guid(listID));
cc.Load(ls);
ChangeQuery query = new ChangeQuery(false, false);
query.Item = true;
query.Update = true;
ChangeCollection changes = ls.GetChanges(query);
cc.Load(changes);
cc.ExecuteQuery();
```To keep things simple, and manageable in one weekend's timeframe, I just grab the item Ids from the ChangeCollection and parse the content. As I only have three pages in my test environment this doesn't take long and as I also have no need for production ready code base, I skiped the whole part about ChangeTokens. So basically parse the whole wiki all the time. The PnP core team implementation already uses a SQL table to store the last token and reuses it on later calls. So have a look there if you want to bring this idea to production.
#### QnAMaker Knowledgebase
Last in line is the update of the knowledgebase with our wiki data. At this point we already have the data in our data objects and we are aware of the specific json format we need for calling the cognitiv services endpoints.```
static async Task<string> MakePublishRequest(string url, string body, string clientSecrete)
{
var client = new HttpClient();
HttpResponseMessage response;
HttpContent httpContent = new StringContent(body, Encoding.UTF8, "application/json");
httpContent.Headers.Add("Ocp-Apim-Subscription-Key", clientSecrete);
Console.WriteLine("Going to call: " + url);
response = await client.PutAsync(new Uri(url), httpContent);
response.StatusCode.ToString();
Console.WriteLine("QNAMaker MakePublishRequest statuscode: " + response.StatusCode.ToString());
Console.WriteLine("QNAMaker MakePublishRequest response: " + response.ToString());
return response.StatusCode.ToString();
}
```I just created a HttpClient that has a special header with your QnAMaker API code (clientSecrete). The URL contains the ID of your knowledgebase in this way: https://westus.api.cognitive.microsoft.com/qnamaker/v2.0/knowledgebases/{knowledgeBaseID} All the data is stored in the httpContent and that's basically a json represntation of our question and answer objects containing the data from the SharePoint pages.
#### Final result
 This little project was done in two days straight on a weekend by someone who's daily job isn't coding anymore. Please keep this in mind during browsing the solution. You will find bugs, you will find code that is far, far far away from production ready. I think I lost at least four hours yesterday by trying to authenticate to SharePoint with app only permission. I decided to fall back to username password, because chaning this can be done later on easily. The solution is just a proof of concept, any sophisticated wiki content will break it and probably there are countless other things that need to be changed if someone plans to go furhter with this. To be totally honest, I already had this in mind during the session at SPSMUC, but the last weekend and all the evenings this week were way too short. I know that I'm probably the person gaining the most value out of this, because during the last two days I learned so much about Azure, Json, the QnAMaker and even SharePoint. I hope at least some thoughts and concepts are also interessting to a broader range of people.
#### Kudos
The given solution is based on various blog posts and inspiring sessions, but three guys need to be mentioned by name:
* [Wictor Wilén](http://www.wictorwilen.se/yo_teams-tab) for his talk at SharePoint Saturday Munich that introduced the idea.
* [Bert Jansen](https://twitter.com/O365Bert) for his [reference implementation of](https://dev.office.com/sharepoint/docs/apis/webhooks/webhooks-reference-implementation) webhooks
* [Paul Schaeflein](https://www.schaeflein.net/author/pschaeflein/) for his post on [Reading the SharePoint change log from CSOM](https://www.schaeflein.net/reading-the-sharepoint-change-log-from-csom/)
Thank you all for your work without it, this post would'nt be possible! You find all the code [here at Github](https://github.com/thomyg/AutomateWikiBot).
