Automation Pilot : Custom Alerts on state of HANA Cloud data

[shraddhashetty]

Goal : With the help of SAP Automation Pilot, lets define a custom alert which runs a custom SQL command. As sample for this blog, we retrieve the list of HDI containers (unused > 90 days ) and send this list to the user via email using SAP Alert Notification Service.

This blog serves two purposes:

1. Writing Custom Alerts.


2. Identifying potential unused HDI containers.

Note: This concept can be applied to any data accessible for the SAP Automation Pilot including customer data.

Pre-requisites :

Before we dive into this, have a look into my previous blogs which will help you setup automation pilot and try the predefined command provided by automation pilot.

Setup configuration of Automation Pilot in BTP Cockpit

Automation Pilot : Start & Stop HANA Instance, Schedule Executions on Automation Pilot

This blog will help you with overview of SAP Alert Notification Service.

SAP Alert Notification Service : Send Notifications to E-mail, Microsoft teams

Overview of the Custom Alerts

This blog aims to give you a detailed overview on how you can make use of the Automation pilot to query SAP HANA Cloud which retrieves the list of HDI containers which has not been used for more than 90 days. This list will be sent to the respective owner/ HDI-Admin who can take desired actions on the same. The List will be sent in the form of E-mail using one of the other BTP services i.e. SAP Alert Notification Service.

Reasoning on this sample: On HDI development machines, often large quantity of HDI tables is created. For Database hygiene, unused HDI containers should be cleaned out. To identify those unused containers we are using HDI metadata tables for finding these which will help the administrator.

Note : Image above has been modified for this topic. Original Source : (here)

How to ?

Once you are on Automation Pilot,

Step 1: Create a catalog which will consist of your Custom command in it.

Step 2: Create a command Custom Alerts.

Step 3: Create an input to store the connection details for an SAP HANA Cloud database.

Step 4: Add the keys and value to it.

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 strip off :443
port	String	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

 

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

Step 5: In the newly created command 'CustomAlertsForHDI' select the input and the add the value SAPHANADB.

Add the following values:

Alias	Input
Value Type	scriptInput
Input	SAPHANADB

Step 6: Add an output key - HDIList, in the contract section.

Step 7: Under configuration, Add an executor 'ScriptData'. Select 'Here' and enter the below details.

Step 8: Select 'ScriptData' and edit the parameters.

Copy the following code into the script parameter.

#!/usr/bin/env python3



import subprocess

import sys, os

from hdbcli import dbapi

from datetime import datetime



ANSData1 = '{"eventType":"CheckHDI_List","resource":{"resourceName":"unknown","resourceType":"unknown"},"severity":"'

ANSData2 = '","category":"NOTIFICATION","subject":"Reminder that there are unused HDI containers from more than 90 days","body":"Perform the necessary actions on the below list of HDI : '

ANSData3 = ' " ,"tags":{"ans:detailsLink":"somedetailslink"}}'



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()

sql_select="""CREATE LOCAL TEMPORARY COLUMN TABLE #CONTAINER_LIST

	AS	( select VC.container_name AS CONT_NAME,

		   VC.container_group_name AS CONTAINER_GROUP_NAME,

		   VC.last_successful_make_timestamp_utc AS TIME_STAMP,

		   MT.table_name	 AS TABLE_NAME,

		   MT.table_size	 AS TABLE_SIZE,

		   DAYS_BETWEEN(last_successful_make_timestamp_utc, current_date  ) AGE

	from 

	      _sys_di.m_visible_containers VC

	left outer join

		  m_tables MT

	on 

		VC.container_name = MT.schema_name

	where 

		DAYS_BETWEEN(last_successful_make_timestamp_utc, current_date ) > 1

	order by VC.container_name) with data;"""

sql_command ='SELECT DISTINCT cont_name, CONTAINER_GROUP_NAME, TIME_STAMP, table_name, table_size, age as RESULT FROM #CONTAINER_LIST'

cursor.execute(sql_select)

cursor.execute(sql_command)

rows = cursor.fetchall()

cursor.close()

severity = "INFO"

res = [list(ele) for ele in rows]

result = [[ele if type(ele)== str else str(ele) if type(ele) == int else '' if ele is None else ele.strftime("%m/%d/%Y %H:%M:%S") for ele in row] for row in res]

headers=[["\\nContainerName", "ContainerGroup", "Timestamp", "TableName", "TableSize", "Age"]]

headers.extend(result)

table = '\\n'.join(' '.join(sub) for sub in headers)

hdilist = table.replace(' ', ', ')

print(ANSData1 + severity  + ANSData2 + hdilist + ANSData3)

Note : Replace the (>1) to with any number (eg: >90) based on your requirements.

Add the environment values, these connection details to HANA cloud instance which will be passed to the executor.

Add the STDIN parameter.

Step 9: Select the output and add the value $(.ScriptData.output.output).

Step 10: In the SAP Alert Notification Service, create a basic service key. The service key will provide the URL and credentials for the SAP Automation Pilot to send notifications to the SAP Alert Notification Service.( Go to SAP BTP cockpit > Instances > Select your alert notification instance )

Step 11: Go to Automation Pilot & add the input to store the credentials for your alert notification service.

Step 12: Create 2 input keys and map the ANSUserInput values to them.

Step 13:Next, forward these results to SAP Alert notification service. Add an other executor called 'SendNotification' .

Step 14: Edit the parameters of SendNotification and add the following values.

Label	Value
data	$(.QueryDB.output.output[0])
password	$(.execution.input.ANSClientSecret)
url	value from ANS service key + /cf/producer/v1/resource-events
user	$(.execution.input.ANSClientID)

Step 15: Create a condition in SAP Alert notification service to send the email with the list of HDI containers.

 

Step 16: Create an action.

• Select the action type as Email


• Provide the name and description.

Go to the advanced properties, add the following details.

Note : Confirmation token will be sent to the email address. Copy the value/token and confirm the action and enable the email action.

Step 17: Assign the condition and action to a subscription.

Step 18: Trigger the command.

Step 19: Check the unformatted content of the email received as list of the SQL result.

You can schedule the above command too to run in a Bi-weekly manner in the SAP Automation Pilot.

Conclusion : Now you have the Bi-weekly report of unused HDI containers that potentially can be deleted. We intentionally do not show the automation of the deletion since it is absolutely not clear that whether these containers are used.

Note: CF commands like 'delete service (ds) ' can be used to delete these services once you have the certainty that these HDI containers are no longer needed.

Helpful Links: Execute SQL Commands and Create Custom Notifications with SAP Automation Pilot and SAP Alert Notifi...

The above tutorial explains a different scenario using SAP Automation Pilot & SAP Alerts Notification Service.

Hope this helps & Please share your valuable feedback.