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.

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:

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 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.

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 Notification Service

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

Hope this helps & Please share your valuable feedback.