cloudwatch alarm for redshift queryduration

Question

I have below cloudwatch alarm defined in CF template for altering me on queries running for 30 mins or more.  
  
```  
    Type: AWS::CloudWatch::Alarm  
    Properties:   
      AlarmName: !Sub "awsredshift-${RSClusterName}-QueryDuration"  
      AlarmDescription: Redshift QueryDuration Alarm  
      Namespace: AWS/Redshift  
      MetricName: QueryDuration  
      Dimensions:   
        - Name: ClusterIdentifier  
          Value: !Ref RSClusterName  
        - Name: latency  
          Value: long  
      ActionsEnabled: true  
      AlarmActions:   
        - !Ref TopicARN  
      OKActions:   
        - !Ref TopicARN  
      ComparisonOperator: GreaterThanOrEqualToThreshold  
      DatapointsToAlarm: 1  
      EvaluationPeriods: 1  
      Period: 300  
      Statistic: Average  
      Threshold: 1800000000  
      TreatMissingData: missing  
```  
But its activating the alarms when there are no queries running that long, am I missing something?  
  
Also is there any way to customize the alarms to put logic in them, I would like to get the SQL text of the query which is running longer. Is there any way to do this via cloudwatch alarms? If not whats the best way to do it - probably lambda?

Accepted Answer

Hi nmakb,  
  
I think you're assuming the Redshift CloudWatch QueryDuration metric is something it's not.  It's not a query level metric.  It's a binned a QueryDuration dimensioned by ClusterIdentifier, latency (short, medium, long bins).  Then in your CloudWatch Alarm you're aggregating that in an average over a 300 period.    
  
Furthermore, I don't see a query id or anything related to join the individual metric data points data back to the source query.   
  
I think the best you could do with the current QueryDuration metric would be to create a CloudWatch Alarm on the metric.  trigger the Alarm on a theshold, set the action to a AWS Lambda to find queries in the alarm interval that were above the threshold.  Then lastly do something with the result in the Lamdba, like trigger another SNS topic or send an email.   
  
I hope this helps clarify what the CloudWatch QueryDuration metric is and isn't and provides you at least an idea of how you could build toward what you're looking to do.  
  
Regards,  
-Kurt

cloudwatch alarm for redshift queryduration

Relevant content