I wanted share what I learned from a POC I did on search integration between SharePoint online and ServiceNow. My goal was that the end user can search knowledge/documents stored in SharePoint document libraries from ServiceNow portal as shown below. I thought it should be a very common use case, but to my surprise, there is no existing documentation available to cover the end to end process. In this POC, I am only going to get documents from one SharePoint document library, so I can figure out overall process, feasibility and user experience.
Files in my testing SharePoint document library:
Search Page from my ServiceNow testing Tenant:
Set up an Oauth2 profiler using Microsoft Online as a OAuth provider
The key part of the whole process is to able to call Microsoft Graph API to fetch all the files via ServiceNow scripting. To call graph API, we will need to set up an OAuth provider and generate an OAuth token. The best resource I found on this topic is this Youtube video Integrating with O365 Mail and Calendar using Graph API.
The main steps from the video are listed here:
- Create unique provider profile name in ServiceNow: Application registry -> Connect to a third-party OAuth provider
- Generate Client ID along with Client Secret on Microsoft app registration portal
- Set provider fields as below:
- Grant type as Authorization Code
- Authorization URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
- Token URL: https://login.microsoftonline.com/common/oauth2/v2.0/token
- Redirect URL: https://testinstance.service-now.com/oauth_redirect.do
- Add OAuth Entity Scopes:
Note: Ensure to add offline_access, as it will generate the refresh token
- Add the scopes to the profile
Use Microsoft Graph explorer to get O365 resources
Next, we are going to test the query that will be used to get all the files in document library(drive) in Microsoft Graph explorer.
- To get all the files in a SharePoint document library, we need to get the ‘drive-id’. You can use following query to list all the document libraries (drives) on the site, including all the drive-ids
https://graph.microsoft.com/v1.0/sites/{tenant}.sharepoint.com:{path-to-site(ie: /sites/HR)}:/drives
In my test case, the URL is: https://graph.microsoft.com/v1.0/sites/shawna.sharepoint.com:/sites/propertyinspection:/drives
- “shawna.sharepoint.com” is my SharePoint testing tenant name
- “propertyinspection” is the site name
- Once you got the drive-id, you can get all files from the document library or a folder
by https://graph.microsoft.com/v1.0/drives/{drive-id}/root:/{folder-name}:/children
In my case, the URL is
- “b!TZwYpvPbeEOVN_n_Wv-Bz-MXiYP8I1lMkxkEz9NSIViv1FcE52qwRZP4gHzMLa_Z” is the target document library/drive id and I am only interested to get few attributes back
Set up Outbound REST Message to call Graph API
Now we have the OAuth profile and the Graph end-point to get all the files. Next, we need to create an Outbound REST Message and a get method as below:
I’m using the Get OAuth token link to generate the tokens as below, as I couldn’t find any references on how to generate the initial token via scripting with Grant-Type “Authorization Code”. The only options I saw getting OAuth token via API call are password and refresh token. I didn’t feel to use my user name and password, so I ended up using refresh token. The refresh token has 3 months expire timespan. So, I think how to get the initial token and keep the refresh token up to date will be the part missing, if we decide to take this into production.
Use scripted Search Source in SeviceNow to consume Graph API
Now, we have the REST API ready, we need to create a ServiceNow search source for portal. Go to Service Portal -> Search Source and set the Search Source as below:
- Data Source: Check Is Scripted Source
(function(query) { var results = []; var tokenRequest = new sn_auth.GlideOAuthClientRequest(); tokenRequest.setGrantType("refresh_token"); tokenRequest.setRefreshToken("** refresh token here"); tokenRequest.setRequestor("request email address here"); var oAuthClient = new sn_auth.GlideOAuthClient(); var tokenResponse = oAuthClient.requestTokenByRequest("Microsoft Graph", tokenRequest); gs.info("Error:" + tokenResponse.getErrorMessage()); var token = tokenResponse.getToken(); var r = new sn_ws.RESTMessageV2('Microsoft Graph API', 'GetDocumentLibraries'); //override authentication profile r.setAuthenticationProfile('oauth2', '3f1b80abdb0353005e30dd30cf9619b4'); var response = r.execute(); var responseBody = response.getBody(); var httpStatus = response.getStatusCode(); gs.log(responseBody); if (responseBody) { responseBody = new JSON.parse(responseBody); gs.log(responseBody + responseBody.value); results = responseBody.value; results.forEach(function(result) { result.url = result.webUrl; gs.info(result.webUrl); result.target = "_blank"; result.primary = result.name; }); } return results; })(query); - Search page template
-
<div> <a href="{{item.url}}" class="h4 text-primary m-b-sm block"> <span ng-bind-html="highlight(item.primary, data.q)"></span> </a> <span class="text-muted" ng-repeat="f in item.fields | limitTo: 4"> <span class="m-l-xs m-r-xs" ng-if="!$first"> · </span> {{f.label}}: <span ng-bind-html="highlight(f.display_value, data.q)"></span> </span> </div>
Done. If you go to Portal Search page, you will find all the documents from the SharePoint online document library.
The next steps for me are
- Figuring out how to create the initial token and get the right refresh token in ServiceNow
- Research on Microsoft Graph search API so we can leverage the Microsoft search to retrieve the search result.
Feel free to comment or share your experience with the integration with Search between SharePoint and ServiceNow.
