Alert/Notification to check max timestamp on a dataset

Rutuja

Hi,

I want to send an Alert/Notification to check max timestamp value on a dataset and and send an alert message if the date has not been updated for last 10 days.

Thanks for help!

Operating system used: windows

Turribeach

In your dataset metrics enable the Column statistics option and enable the Max metric on the timestamp column you want your alert on:

Now create a new Scenario in your project and add 3 steps (Compute metrics, Define variables and Send message) as follows:

Name the compute metrics "Compute_Metrics" (do not change this) and add the dataset where you added the Max metric.

On the Define variables step enable the Evaluated variables option and add a new variable called "Days_Difference" and use the following formula:

diff(now(), asDate(filter(parseJson(stepOutput_Compute_Metrics)['[PROJECT_KEY].[DATASET_ID]_NP']['computed'], x, x["metricId"]=="col_stats:MAX:[COLUMN_NAME]")[0].value, "yyyy-MM-dd'T'HH:mm:ss.SSSZ"), 'days')

Yopu need to replace the [PROJECT_KEY], [DATASET_ID] and [COLUMN_NAME] placeholders with your corresponding values as per your environment. See sample below:

On the Send message step change the Run this step to "If conditioned satified" and add the following logical expression:

Days_Difference > 10 && outcome == 'SUCCESS'

Now schedule the scenario as required in the Settings tab.

Turribeach

Try with this date format in the formula:

yyyy-MM-dd'T'HH:mm:ss.SSSSSSZ

Turribeach

Do you want a code or no code solution?

Rutuja

https://community.dataiku.com/discussion/comment/44147#Comment_44147

No code solution. I am new to Dataiku.

Rutuja

Thank you so much for the help. It was super helpful !

I am getting following error while running the Define variable step. Not getting what's wrong in the expression.

is dataset name and dataset id same ?

Cannot evaluate expression 'diff(now(), asDate(filter(parseJson(stepOutput_Compute_Metrics)['EMHEATMAPV1.CorkOutput_NP']['computed'], x, x["metricId"]=="col_stats:MAX:timestamp")[0].value, "yyyy-MM-dd'T'HH:mm:ss.SSSZ"), 'days')' as formula

Turribeach

Post a screen shot of the compute metrics scenario step, a screen shot of the metric you added in your dataset and paste the full URL you see in your browser when open the dataset. One of those names it’s wrong.

Rutuja

Please find the attached screenshot for your reference

em_1.PNG

em2.PNG

URL :11100/projects/EMHEATMAPV1/datasets/CorkOutput/explore/

Turribeach

OK I see your problem. You didn't follow the steps I outlined properly. You must name the step "Compute_Metrics".

My scenario step:

Your scenario step:

PS: You can copy/paste images directly from your clipboard so they show inline rather than as attachments. They are easier to see and easier to add.

Rutuja

[2024/08/06-15:46:04.254] [FT-ScenarioThread-ZdwIXxcz-29924] [INFO] [dip.reporter.lookup] scenario EMHEATMAPV1.DAILY_TIMESTAMP_MONITORING#2024-08-06-15-46-01-187 - [ct: 3032] Failure during evaluation of'diff(now(), asDate(filter(parseJson(stepOutput_Compute_Metrics)['AUTOMATED_REPORT.CorkOutput_NP']['computed'], x, x["metricId"]=="col_stats:MAX:timestamp")[0].value, "yyyy-MM-dd'T'HH:mm:ss.SSSZ"), 'days')' as formula: ExpressionError: parseJson failed: Missing value at 0 [character 1 line 1][2024/08/06-15:46:04.254] [FT-ScenarioThread-ZdwIXxcz-29924] [WARN] [dip.reporter.lookup] scenario EMHEATMAPV1.DAILY_TIMESTAMP_MONITORING#2024-08-06-15-46-01-187 - Cannot evaluate expression 'diff(now(), asDate(filter(parseJson(stepOutput_Compute_Metrics)['AUTOMATED_REPORT.CorkOutput_NP']['computed'], x, x["metricId"]=="col_stats:MAX:timestamp")[0].value, "yyyy-MM-dd'T'HH:mm:ss.SSSZ"), 'days')' as formulajava.lang.Exception: parseJson failed: Missing value at 0 [character 1 line 1]

I have updated the step1, but still getting same error

Turribeach

You have a space in the name. As I said in my instructions you must call it "Compute_Metrics".

Rutuja

[2024/08/06-16:

    

        
            
    


28:29.086] [FT-ScenarioThread-sVSxVcQi-30042] [INFO] [dip.reporter.lookup] scenario EMHEATMAPV1.DAILY_TIMESTAMP_MONITORING#2024-08-06-16-28-27-897 - [ct: 1157] Failure during evaluation of'diff(now(), asDate(filter(parseJson(stepOutput_Compute_Metrics)['AUTOMATED_REPORT.CorkOutput_NP']['computed'], x, x["metricId"]=="col_stats:MAX:timestamp")[0].value, "yyyy-MM-dd'T'HH:mm:ss.SSSZ"), 'days')' as formula: ExpressionError: Cannot retrieve field from null[2024/08/06-16:28:29.086] [FT-ScenarioThread-sVSxVcQi-30042] [WARN] [dip.reporter.lookup] scenario EMHEATMAPV1.DAILY_TIMESTAMP_MONITORING#2024-08-06-16-28-27-897 - Cannot evaluate expression 'diff(now(), asDate(filter(parseJson(stepOutput_Compute_Metrics)['AUTOMATED_REPORT.CorkOutput_NP']['computed'], x, x["metricId"]=="col_stats:MAX:timestamp")[0].value, "yyyy-MM-dd'T'HH:mm:ss.SSSZ"), 'days')' as formulajava.lang.Exception: Cannot retrieve field from null

Cannot retrieve field from null

Turribeach

What's "AUTOMATED_REPORT"? Your project key is EMHEATMAPV1 so not sure why you used AUTOMATED_REPORT in the expression.

Rutuja

when I retrieved the project key, it showed 2 names including "AUTOMATED_REPORT". I have fixed it now.

The formula is running successfully now, but their is issue with date format.

Unable to parse as date: 2024-07-22T07:26:04.000000+0000

Turribeach

What is the datatype of the timestamp column? Post a screen shot of the column heading and some values.

Rutuja

DATATYPE is date

Rutuja

It worked !

Thanks you for the solution! @Turribeach