Alert/Notification to check max timestamp on a dataset

Rutuja · August 1

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 · August 2

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 · August 6

Try with this date format in the formula:

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

Turribeach · August 1

Do you want a code or no code solution?

Rutuja · August 1

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

No code solution. I am new to Dataiku.

Rutuja · August 2

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 · August 2

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 · August 5

Please find the attached screenshot for your reference

em_1.PNG

em2.PNG

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

Turribeach · August 5

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 · August 6

[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 · August 6

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

Rutuja · August 6

[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 · August 6

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

Rutuja · August 6

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 · August 6

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

Rutuja · August 6

DATATYPE is date

Rutuja · August 7

It worked !

Thanks you for the solution! @Turribeach

Alert/Notification to check max timestamp on a dataset

Best Answers

Answers

Categories

Setup Info

Tags