How to setup microsoft sharepoint spoke

[Ashutosh Munot1]

Microsoft Sharepoint Spoke

Overview :

In this article we will see how to use sharepoint spoke in ServiceNow along with its setup. I will be making three articles on this topic :

• Mainly to show how to setup the spoke and how it behaves OOB.
• Second one to show how can you fetch custom attributes and how can you edit the spoke for that.
• How to make use of CAML query to fetch data from sharepoint using specific query.

Why sharepoint spoke?

This spoke helps in fetching folder information, files information and copy/move the file to specific location using ServiceNow and REST API calls from sharepoint. I can imagine that in organization there can be a process to copy attachments from ServiceNow records into sharepoint folder and vice versa, also I am big fan of control testing in GRC and this spoke can be used to get evidences from sharepoint folders and make decision if the control is compliant or not. Another example I can tell is adding and removing user from group in sharepoint, this helps us to follow approval process in ServiceNow and helps in audits and have control on this use case removing the overhead of emails.

More information about sharepoint API can be found here : LINK

Pre-requisite :

• IntegrationHub Subscription as mentioned here
• Azure AD account because we are going to use Site collection method in this article. More information is here
• Microsoft Sharepoint spoke activated in instance.

Set-up :

Let’s start and see how to set up the spoke and test it with some OOB actions. It is good know that OOB ServiceNow provides more that 60 actions which performs specific function. So you can use them as per your need. If you want to see the list then go here. Please see the video below to understand the detailed setup procedure. High level the steps are:

• Azure AD subscription.
• oAuth Registration in ServiceNow.
• Credentials in credentials table in ServiceNow
• Credential alias and connection in ServiceNow.
• Tenant Setup in ServiceNow.

Next:

Stay tuned for next article on how to get custom attributes for a folder in sharepoint from ServiceNow using sharepoint API.

Please see the videos and let me know the feedback. Thank you for watching.

Thanks and Regards,
Ashutosh Munot

ServiceNow Community MVP and Dev MVP 2019/2020

My Article and Blogs

YouTube Channel

[Robin Bakker]

Hi Ashutosh, 
I went through your video and managed to establish the connection. However the problem is when performing an action like "List files in folder" then I'm getting 0.0 as result. While when I'm performing similar action via Postman it's working perfectly fine. 

It doesn't give a proper error that something went wrong it just says it's completed with 0 result. While I know for sure that the folder at least contains 12 files + 1 folder. 

[Udit Maheshwar1]

hi @Ashutosh Munot1 :

Great Article! I am unable to redirect to the azure AD account link (pre-requisite) as it is no longer supported. Do you have the contents or steps to configure the same on azure AD portal ?

I am trying to configure the same but getting "invalid/empty token error".

Any help is much appreciated.

Thanks,

Udit

[SS6]

Hi ,

I am able to set up the connection and when trying to GetOauth Token from, I am getting "OAuth token flow completed successfully". But when I am running a "Create Site" Action test, it is showing me error "Invalid Authorization Token" and response {"error_description":"Exception of type 'Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException' was thrown."}.

Do I need to import any certificate or token as part of the connections & credentials?

[Ahmet1]

@SS6  Did you figure this out? I have the same error message and I've setup everything correctly with regards to Permissions on Azure.

[Udit Maheshwar1]

Hi @Ahmet1 : no, still figuring out how to resolve the issue. May I ask what all permissions you have given on azure ? For me, my customer allowed only graph api permissions and not at sharepoint level. I am trying to figure out the solution with graph level permissions only.

Thanks,

Udit

[Robin Bakker]

Hi @Udit Maheshwar1 & @SS6 & @Ahmet1 ,

I have raised a ticket in the portal because of the issues I was having and came a far way from that. 
So basically how the ServiceNow docs are showing the installation of the plugin is the right way to do so... 

1. First create the certificate as this is necessary in the AzureAD App. Which is explained here
https://docs.servicenow.com/bundle/rome-servicenow-platform/page/administer/integrationhub-store-spo...

2. The certificate can be created by following using the powershell script provide in this following article 
https://learn.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azuread

3. Then next go to AzureAD and create the App according to this information 
https://docs.servicenow.com/bundle/rome-servicenow-platform/page/administer/integrationhub-store-spo...

3a. Also follow this guide to add the certificate to AzureAD and have a "Client secret" created. 
https://docs.servicenow.com/bundle/rome-servicenow-platform/page/administer/integrationhub-store-spo...
3b. The thumbprint given by your AzureAD admin can be converted to Base64 via online tools (google Hex to Base64)

Our Azure guy has given me exactly the same access as given here both for Graph and Sharepoint. (Currently also investigating if for Sharepoint Sites.ReadWrite.All would be sufficient, will follow up onto that later on).

4a. Convert the certificate (pfx) file to a JKS file so you can add it to your Certificate record 
If you have Java Developer Kit 1.6 or higher installed you can use below script to easily convert it. 

keytool -importkeystore -srckeystore mypfxfile.pfx -srcstoretype pkcs12 
-destkeystore clientcert.jks -deststoretype JKS

4b. Follow the rest of the steps of the docs.. If you already have installed the plugin this should come along with a certificate entry (Microsoft SharePoint Online Certificate), connection & credential alias (MicrosoftSharePointOnline). 
5. https://docs.servicenow.com/bundle/rome-servicenow-platform/page/administer/integrationhub-store-spo...

6. Now the additional stuff we did to make it work. 
7. In ServiceNow go to "Key Management" -> "Module Access Policies" -> "All" 
It can be the case that you need to grant yourself access to this module as it was not by default available for admin, this can be found under "Key Management Administration". 
8. Check if you have an entry for the target script "OAuthUtilSPJWTOnline" (you can only search on sys_id). If it's there make sure it's set to "Result = Track" 

After this you should not have any authentication error or whatsoever. Although we kept having another issue mentioning "File not Found" This had to do because the OOTB actions are all using the "GetFolderByServerRelativePath" this did not work in our situation and have to copy the action and adjust the "Resource Path" and make it as "GetFolderByServerRelativeUrl" (small L). Also the "Decode@P1" was not working for us and we needed to script it upfront. (code is in the picture)

After having all this setup the action is working fine and was able to create files/upload files from an attachment in ServiceNow to Sharepoint and so on. These are the only 2 actions I need for now, so haven't investigated on the others yet, but this maybe help you out as well.! 

[Udit Maheshwar1]

Hi @Robin Bakker :

Thank you for sharing the step by step details.

The issue with my configuration is that Azure team just provided the Graph API level permission and not the SharePoint one. They are saying that everything is covered within Graph API level permission and there is no need to explicitly provide the SharePoint API permission on azure AD portal.

Is it possible to use only graph level permission and achieve the desired result ??

Thanks,

Udit

[Robin Bakker]

Hi @Udit Maheshwar1,

So I had the same discussion however it won't work without. I tested it just to confirm but no luck. 

[Ravi Kand]

Dear All,

Flow Designer Action name: List Files In A Folder

SharePoint URL:

https://myompany.sharepoint.com/sites/EDMServiceNow/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsit...

I have copied 1 file in this folder for testing purpose. When I run, I am getting FileCount as 0.0.

How to split this URL as input parameters to get the correct file count?

Thank you in advance...

[Sam179]

@Ravi Kand Were you able to figure out how to split the URL?

[Ravi Kand]

Yes. Issue resolved. Thanks.

[Sam179]

@Ravi Kand can you please let me know how you resolved it? I'm stuck with the same issue

[Ravi Kand]

SharePoint URL needs to split as below.

Please test and let me know if it works for you.

[Sam179]

I'm getting error code 403.

Method failed: (/sites/Demo/_api/web/GetFolderByServerRelativePath(decodedurl=@p1)/Files) with code: 403 - Forbidden username/password combo

Any idea where it's picking the credentials from? 

[Ravi Kand]

Make sure Microsoft side API permissions as below.

[Sam179]

 Permissions seems to be correct. I have scratching my head on this or a while. Any help appreciated.

[Ravi Kand]

Remove all unwanted permissions in Microsoft APP. Sometimes, extra permissions may cause an issue.

Verify OAuth Entity Scopes. Only scope should be configured.

•  scope -   https://xxxx.sharepoint.com/.default

Verify in HTTP(s) connections page as below:

• Encoded Certificate Thumbprint (Base64).
• Connection URL:  https://xxxx.sharepoint.com
  Host:                      xxxx.sharepoint.com

[Robin Bakker]

For our company I didn't need anything else then this.