Snowflake Alert and Email Notification with Usecase — 2

In this blog post, we will explore the process of creating a procedure that allows for the customization of email messages. To learn more about the steps involved, please refer to Part 1 of this series by clicking here.

Step 4: Crafting a Custom Procedure to Extract Details of Users with Expired Passwords and Notify the Appropriate Administrators,

CREATE OR REPLACE PROCEDURE PASSWORD_EXPIRY_NOTIFICATION()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
try
{
    var Password_Expiry_Notification = `
        SELECT NAME,DISPLAY_NAME,CREATED_ON,LAST_SUCCESS_LOGIN,EXPIRES_AT,PASSWORD_LAST_SET_TIME 
  FROM  SNOWFLAKE.ACCOUNT_USAGE.USERS WHERE PASSWORD_LAST_SET_TIME > DATEADD(DAY, -20, CURRENT_TIMESTAMP())
  AND DELETED_ON IS NULL`;

    rs = snowflake.execute({ sqlText: Password_Expiry_Notification });

    while (rs.next()) {
        User_Name = rs.getColumnValue(1);
        User_Display_Name = rs.getColumnValue(2);
        User_Created_On = rs.getColumnValue(3);
        User_Last_Success_Login = rs.getColumnValue(4);
        User_Expires_At = rs.getColumnValue(5);
        User_Password_Last_Set_Time = rs.getColumnValue(6);

        var proc = `
            call system$send_email(
                'Password_Expiry_Int',
                'XXXXX@gmail.com',
                'User Name -> ` + User_Name + ` <- Time for Password Reset',
                'User Display Name: ` + User_Display_Name + `
                \nUser Created On: ` + User_Created_On + `
                \nUser Last Success Login: ` + User_Last_Success_Login + ` 
                \nUser Expires At: `+User_Expires_At+`
                \nUser Password Last Set Time: ` + User_Password_Last_Set_Time + `'
            )
        `;

        var stmt = snowflake.createStatement({ sqlText: proc });
        var result = stmt.execute();
    }

    result = "Password Expiry Notification successfully sent to registered stakeholders.";
}
catch (err) {
    result = "Failed: Code: " + err.code + "\n  State: " + err.state;
    result += "\n  Message: " + err.message;
    result += "\nStack Trace:\n" + err.stackTraceTxt;
}

return result;
$$;

Here’s a breakdown of the key components and functionalities of the procedure:

1. Procedure Signature: The procedure is defined with the “CREATE OR REPLACE PROCEDURE” statement. It returns a string and is implemented in the JavaScript language.
2. Execute As Caller: This procedure is executed under the permissions of the caller. It ensures the proper security context for sending notifications.
3. Main Logic: The core logic of this procedure is as follows:

• It initiates a SQL query to the Snowflake ACCOUNT_USAGE.USERS view to retrieve user details.
• The query selects relevant user information, including their name, display name, account creation date, last successful login, password expiration date, and the time when the password was last set.
• The query filters users whose password was last set more than 20 days ago and who have not been deleted.
• The results of this query are processed in a loop, where each user’s information is extracted and used to compose an email notification.

Email Notification: For each user, an email notification is generated using the system$send_email function. The email is sent to the designated recipient (e.g., system administrator) and contains information about the user whose password is about to expire. This information includes the user's name, display name, account creation date, last login, expiration date, and password last set time.

Exception Handling: The procedure is designed to handle any errors that may occur during execution. If an error is encountered, it captures relevant error information such as error code, state, message, and stack trace, which can be invaluable for debugging and issue resolution.

Result: The procedure returns a string indicating the outcome of the process. If successful, it notifies stakeholders that password expiration notifications have been sent. In case of an error, it provides details about the error for further investigation.

After successful execution of the above procedure, following result will get displayed,

If you wish to call the procedure mentioned above when a notification is needed or when specific conditions in Snowflake data are met, you can establish a Snowflake Alert. More details about Snowflake Alerts will be covered in our upcoming blog.

References:-

• https://www.snowflake.com/

About me:

I am a Data Engineer and Cloud Architect with experience as a Senior Consultant at EY GDS. Throughout my career, I have worked on numerous projects involving legacy data warehouses, big data implementations, cloud platforms, and migrations. If you require assistance with certification, data solutions, or implementations, please feel free to connect with me on LinkedIn.