Fulfilling Requests Through Power Automate ServiceNow Connector

[CheeryFrog143]

I have been tasked with creating an automation using Power Automate to perform actions when certain requests are made in ServiceNow.  I'm not a ServiceNow admin and, unfortunately, our admin is not much help as the system was set up by consultants who are no longer contracted to us, so I'm kind of on my own.

I am able to successfully set up a connection to our dev instance and I can use the connector to query tables, but I'm having a terrible time getting all the information I need.  Hopefully someone can point me in the right direction.  Here are the steps that I need to perform.  So far, I've only been able to complete the first one.

1. Get a list of open catalog tasks with a specific description, assigned to a specific group but not assigned to anyone.
2. Iterate through the list of tasks.  For each task, Power Automate needs to:
3. Assign the task to itself.
4. Get the details (variables) of the task.  I've looked in the task, RITM and Request tables, but can't find them.
5. Pass those variables to the Power Automate Desktop flow to do the work.
6. Update the task as needed.
7. Close out the task and request.

If someone could please help me understand how the tables are related to each other, as I will need to update them directly.  Again, these are for requests, not incidents (almost every example out there is for incidents).

[Swarup Patra]

Here are the steps you can follow to achieve your requirements:

1. **Get a list of open catalog tasks with a specific description, assigned to a specific group but not assigned to anyone.**
- Use the ServiceNow Table API to query the 'sc_task' table.
- The API endpoint would be something like: https://.service-now.com/api/now/table/sc_task?sysparm_query=assignment_group=^state=1^assigned_toISEMPTY^descriptionLIKE
- This will return a list of open catalog tasks with the specific description, assigned to the specific group but not assigned to anyone.

2. **Iterate through the list of tasks.**
- You can use Power Automate's 'Apply to each' action to iterate through the list of tasks returned by the above API.

3. **Assign the task to itself.**
- You can use the ServiceNow Table API to update the 'assigned_to' field of the 'sc_task' table.
- The API endpoint would be something like: https://.service-now.com/api/now/table/sc_task/
- In the request body, you can set the 'assigned_to' field to the sys_id of the user you want to assign the task to.

4. **Get the details (variables) of the task.**
- The variables of a task are stored in the 'sc_item_option_mtom' table.
- You can use the ServiceNow Table API to query this table with the 'request_item' field set to the sys_id of the task.
- The API endpoint would be something like: https://.service-now.com/api/now/table/sc_item_option_mtom?sysparm_query=request_item=

5. **Pass those variables to the Power Automate Desktop flow to do the work.**
- You can use Power Automate's 'Run a flow' action to run the Power Automate Desktop flow and pass the variables as inputs.

6. **Update the task as needed.**
- You can use the ServiceNow Table API to update the task as needed.

7. **Close out the task and request.**
- You can use the ServiceNow Table API to update the 'state' field of the 'sc_task' and 'sc_request' tables to close out the task and request.

In terms of how the tables are related, the 'sc_task' table is related to the 'sc_request' table through the 'request' field, and to the 'sc_item_option_mtom' table through the 'request_item' field. The 'sc_request' table is related to the 'sc_item_option_mtom' table through the 'request' field.

nowKB.com

[CheeryFrog143]

Thank you!  I have a follow up question.  I have been able to perform the steps you outlined to read service now tables, but I am unable to write anything to them.  My understanding is that is some sort of internal ACL issue.  Might you have any advice or guidance on how to troubleshoot this?  Our SNOW admin is completely unfamiliar with ACL rules and is hesitant to make any changes.

{

    "statusCode": 403,

    "headers"…

    "body": {

        "error": {

            "message": "Operation Failed",

            "detail": "ACL Exception Update Failed due to security constraints"

        },

        "status": "failure"

    }

}

[Abhijit]

Can you check the roles assigned to user? which is used for getting sc_task table API data.

For testing purpose assign catalog_admin / admin role to this user and then check your query.

[CheeryFrog143]

I will check with our admin, but I should mention that the account is able to update and close tasks via the GUI, just not through the API.  Are there ACL rules that only pertain to API access?

Also, note that this is being done in Microsoft Power Automate using their Service Now connector.  Again, we're able to access tables read-only without issue, just not update them.