Execute SQL Commands and Create Custom Notifications with SAP Automation Pilot and SAP Alert Notification Service

This step will create a catalog, an input, and a command in the SAP Automation Pilot. The command in subsequent steps will execute SQL against the database to determine if there are any unassigned maintenance items.

1. In the SAP Automation Pilot, create a new catalog.

   

   Specify the values below.

   Label	Value
   Name	CustNotif
   Display name	Custom Notifications
   Description	A user catalog which will hold inputs and commands demonstrating how to perform a custom notification

2. Create an input to store the connection details for an SAP HANA Cloud database.

   

   Specify the values below.

   Label	Value
   Catalog	Custom Notifications
   Name	SAPHANADB
   Description	Contains the details to connect to a specific SAP HANA Cloud database

3. Add the following keys to the input.

   

   Key Name	Type	Sensitive	Description	Value
   host	String	no	Host value for a SAP HANA Cloud database	Copy the SQL Endpoint from SAP HANA Cloud Central and remove :443
   port	Number	no	Port value for a SAP HANA Cloud database	443
   user	String	no	The user ID to connect the database	USER1
   password	String	yes	The password to connect the database	Password1

   Storing values in an input enables the values to be reused across different commands. If a value needs to be updated, it only has to be updated in one place.

4. Create a command.

   

   Specify the values below.

   Label	Value
   Catalog	Custom Notifications
   Name	CheckMaintenanceItems
   Description	Perform a check to see if there are any unassigned maintenance items

5. In the newly created command, select input and add an additional value which is the SAPHANADB input.

   

   Specify the values below.

   Label	Value
   Alias	scriptInput
   Value Type	Input
   Input	SAPHANADB

   An alternative method of passing parameters to the command uses the input keys in the contract section. This is shown in step 4.

6. Add an output key.

   

   Specify the values below.

   Label	Value
   Name	checkResult
   Type	string
   Sensitive	No
   Description	JSON string of the result

7. Under Configuration, add an executor.

   

   Click on Here and specify the values below.

   

   Label	Value
   Alias	QueryDB
   Command	ExecuteScript (Version: 2)
   Automap Parameters	true

   :2 indicates that this is version 2 of the command.

   Automap parameters is not used in this tutorial, so its value can be enabled or disabled.

8. Select QueryDB and then press the edit parameters button.

   

   Paste in the following code for the script parameter.

   Python Copy

   #!/usr/bin/env python3
   
   print("Hello from QueryDB!")

9. Select the output and choose Edit.

   

   Label	Value
   checkResult	$(.QueryDB.output.output[0])

   This will take the result printed in the QueryDB executor and set it to the output of the command.

   For additional details on the use of the $(...) used above, see Dynamic Expression.

10. Trigger the execution.

    

    Additional inputs are not needed.

    

    After a few seconds, the output can be seen.

    

    

    A quick way to return to the command after executing it is to click on the link shown below.

    

At this point, a command has been created and executed. In the next two steps, two different techniques of executing SQL against an SAP HANA Cloud database will be shown.

This step will add an additional executor to the command that can connect to a SAP HANA Cloud database and execute a query.

1. Under Configuration, add an executor.

   

   Click on Here and specify the values below.

   

   Label	Value
   Alias	SQLStatement
   Command	ExecuteHanaCloudSqlStatement
   Automap Parameters	true

2. Select SQLStatement and then press the edit parameters button.

   

   Set the Connection URL to be jdbc:sap://$(.scriptInput.host):$(.scriptInput.port).

3. Under Statement, specify the SQL below.

   SQL Copy

   SELECT H.NAME, M.DESCRIPTION FROM HOTELS.MAINTENANCE M, HOTELS.HOTEL H WHERE M.HNO = H.HNO;

   

4. Under Authentication specify the values below.

   

   Key	Value
   Type	Basic Authentication
   User	$(.scriptInput.user)
   Password	$(.scriptInput.password)

   Notice that the user or password inputs provide autocompletion after $(. is entered.

   

5. Under Advanced specify the values below.

   

   Key	Value
   Result Row Format:	Object
   Result Transformer	toArray[0]

6. Select QueryDB. Then press the edit parameters button.

   

   Paste in the following code for the script parameter replacing the previous content.

   

   Python Copy

   #!/usr/bin/env python3
   
   import sys, json
   
   input = sys.stdin.readline()
   rows = json.loads(input)
   print(rows)

   Under STDIN, specify $(.SQLStatement.output.result) so that the output of the previous step can be accessed in the Python code.

   

7. Trigger the execution. Additional inputs are not needed.

   

   The output can be seen below.

   

The above step demonstrates how the provided SQL Statement command can be used. Its output was passed into a Python command, where the result could be further processed. The next step is an example to execute SQL statements directly in the Python command.

This step will demonstrate how SQL queries can be made directly in Python rather than using a separate executor.

1. Optionally delete the executor SQLStatement.

2. Select QueryDB and then press the edit parameters button.

   

   Paste in the following code for the script parameter replacing the previous content.

   Python Copy

   #!/usr/bin/env python3
   
   import sys, os
   from hdbcli import dbapi
   
   #Note:  We cannot directly access input keys such as $(.execution.input.user) or additional parameter values $(.scriptInput.user) in a Python script
   host = os.environ.get("host")
   port = os.environ.get("port")
   user = os.environ.get("user")
   password = sys.stdin.readline()
   
   conn = dbapi.connect(
       address=host,
       port=port,
       user=user,
       password=password
   )
   
   cursor = conn.cursor()
   cursor.execute("SELECT count(*) AS UNASSIGNED_ITEMS FROM HOTELS.MAINTENANCE WHERE PERFORMED_BY IS NULL;")
   rows = cursor.fetchall()
   numOfUnassignedItems = 0
   severity = "INFO"
   
   for row in rows:
       numOfUnassignedItems = row[0]
       severity = "WARNING"
   
   cursor.close()
   print("There are " + str(numOfUnassignedItems) + " unassigned maintenance items.")
   print(severity)

   Show all 31 lines Show less

   The provided executor ExecuteScript supports various languages and when Python is used, it includes the hdbcli which is the Python driver for SAP HANA which is used to connect to and query an SAP HANA Cloud database.

3. In the next two sub-steps, connection details for the SAP HANA Cloud instance will be passed into the executor. Under environment, specify the values below.

   Key	Value
   host	$(.scriptInput.host)
   port	$(.scriptInput.port)
   user	$(.scriptInput.user)

   

   Additional value keys or input keys cannot be directly referenced in a Python script and hence they need to be passed in through alternative means such as environment variables or stdin. Step 3 makes use of input keys.

4. Under STDIN, replace the previous value with $(.scriptInput.password)

   

   Passing the password through STDIN is more secure.

5. Trigger the execution. Additional inputs are not needed.

   

   The output can be seen below.

   

   A command has now been successfully created that can execute SQL against an SAP HANA Cloud database using the Python SAP HANA Client. With this method, the query and the logic to process the result can be done in one executor.

A second executer will be added in this step. It will take the output returned from QueryDB and forward it to the SAP Alert Notification Service which will then send the details via email.

1. In the SAP Alert Notification Service, create a basic service key named BasicANSServiceKey. The service key will provide the URL and credentials for the SAP Automation Pilot to send notifications to the SAP Alert Notification Service.

   

   JSON Copy

   {
       "type": "BASIC"
   }

2. In the SAP Automation Pilot, create an input named ‘ServiceKeyANS’.

   

   Specify the values below.

   Label	Value
   Catalog	Custom Notifications
   Name	ServiceKeyANS
   Description	Basic credentials (client_id and secret_key) used to access the SAP Automation Pilot

3. Add a key named JSONKey of type object marked as sensitive and paste in the JSON generated in step1.

   

4. In the command, under Executors, select the input and choose Add.

   

   Specify the values below.

   Label	Value
   Alias	ANSKey
   Value Type	Input
   Input	ServiceKeyANS

5. Add a second executor.

   

   Place the executor between QueryDB and output. This executor will send notifications to the SAP Automation Pilot.

   

   Specify the values below.

   Label	Value
   Alias	SendNotification
   Command	SendAnsEvent
   Automap Parameters	true

6. Edit the parameters of the just added executor.

   

   

   Specify the values below.

   Label	Value
   body	Reminder that there are $(.QueryDB.output.output[0]) unassigned maintenance items
   category	NOTIFICATION
   eventType	Unassigned_Maint_EVENT
   resourceName	unknown
   resourceType	unknown
   serviceKey	$(.ANSKey.JSONKey)
   severity	$(.QueryDB.output.output[1])
   subject	$(.QueryDB.output.output[1]) Unassigned_Maint_EVENT

   The SAP Alert Notification Service is expecting the input data to be a JSON document. Additional details can be found at Producing Custom Events and Cloud Foundry Producer API (Model View).

7. Trigger the command.

   

   The next step will create a subscription in the SAP Alert Notification Service that will be triggered by the just sent JSON and can then perform an action such as send an email.

The following instructions use the SAP Alert Notification Service to send an email when there are unassigned maintenance items.

1. In the SAP Alert Notification Service, create a condition.

   

   Specify the values below.

   Label	Value
   Name	Unassigned-Maintenance-Items
   Condition	eventType is Equal To Unassigned_Maint_EVENT
   Description	There are items in the table HOTELS.MAINTENANCE that are not assigned.

   

   A further condition could be added to send an email only when the severity equals WARNING. When the severity is INFO, there were no unassigned maintenance items.

   ---

   An additional approach would be to apply a condition on SendNotification to indicate that it should only execute if the number of unassigned maintenance items does not equal to 0.

   

2. Create an action.

   

3. Specify the type of action to be Email.

   

   See also Managing Actions for details on other available action types.

4. Provide the Name email-manager.

   

   Scroll to the Additional Properties section.

   

   Specify the values below.

   Label	Value
   Email Address	your email address
   Subject Template	{subject}
   Payload Template	{body}

5. A confirmation token will be sent to the email address. Click on the provided link or copy confirmation token and use it to confirm the action as shown in the next sub-step.

   

6. Click on email-manager to open it.

   

   Press the Confirm Action button.

   

   Enter the confirmation token and notice that the action is now enabled.

   

7. Create a subscription.

   

   Provide a value for the name and press the Create button.

   

8. Assign the condition and action to the subscription.

   

9. In the SAP Automation Pilot, trigger the command and notice that an email is now sent.

   

In this step, the command will be scheduled to run once a week.

1. In the SAP Automation Pilot, select Scheduled Executions and press the Schedule button.

   

   Specify the values below.

   Label	Value
   Command	CheckMaintenanceItems
   Schedule	Weekly
   Weekdays	Monday
   Hours	12
   Minutes	0

   

   Additional details can be found at Scheduled Execution.

2. Provided commands such as StopHanaCloudInstance and StartHanaCloudIntance can also be scheduled perhaps to shut down and restart a development SAP HANA Cloud instance on weekends. The StopHanaCloudInstance command is shown below.

   

Congratulations! You have now used the SAP Automation Pilot to schedule a query against an SAP HANA Cloud database and to send a notification that reflects the result of the query.