Alert/Notification to check max timestamp on a dataset

Rutuja
Rutuja Registered Posts: 19 ✭✭✭
in Using Dataiku

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

Tagged:
· Share on FacebookShare on Twitter

Best Answers

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,925 Neuron
    Answer ✓

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

    image.png

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

    image.png

    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:

    image.png

    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'
    image.png

    Now schedule the scenario as required in the Settings tab.

    · Share on FacebookShare on Twitter
  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,925 Neuron
    Answer ✓

    Try with this date format in the formula:

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

    · Share on FacebookShare on Twitter

Answers

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,925 Neuron

    Do you want a code or no code solution?

    · Share on FacebookShare on Twitter
  • Rutuja
    Rutuja Registered Posts: 19 ✭✭✭
    https://community.dataiku.com/discussion/comment/44147#Comment_44147

    No code solution. I am new to Dataiku.

    · Share on FacebookShare on Twitter
  • Rutuja
    Rutuja Registered Posts: 19 ✭✭✭

    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

    · Share on FacebookShare on Twitter
  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,925 Neuron

    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.

    · Share on FacebookShare on Twitter
  • Rutuja
    Rutuja Registered Posts: 19 ✭✭✭

    Please find the attached screenshot for your reference

    em_1.PNG
    em2.PNG

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

    · Share on FacebookShare on Twitter
  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,925 Neuron

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

    My scenario step:

    image.png

    Your scenario step:

    image.png

    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.

    · Share on FacebookShare on Twitter
  • Rutuja
    Rutuja Registered Posts: 19 ✭✭✭
    image.png 
    [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

    · Share on FacebookShare on Twitter
  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,925 Neuron

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

    image.png

    · Share on FacebookShare on Twitter
  • Rutuja
    Rutuja Registered Posts: 19 ✭✭✭ 

    [2024/08/06-16:

    
    
    
        
            em4.PNG
    
    

    

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

    · Share on FacebookShare on Twitter
  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,925 Neuron

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

    · Share on FacebookShare on Twitter
  • Rutuja
    Rutuja Registered Posts: 19 ✭✭✭

    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

    · Share on FacebookShare on Twitter
  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,925 Neuron

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

    · Share on FacebookShare on Twitter
  • Rutuja
    Rutuja Registered Posts: 19 ✭✭✭
    image.png

    DATATYPE is date

    · Share on FacebookShare on Twitter
  • Rutuja
    Rutuja Registered Posts: 19 ✭✭✭
    edited August 7

    It worked !

    Thanks you for the solution! @Turribeach

    · Share on FacebookShare on Twitter
Bookmark this topic

Categories

Setup Info
    Tags
      Help me…