How do I display a 0 in a CloudWatch query stats section for a percent calculation - Stack Overflow

admin管理员组

文章数量:1127979

I am trying to do a simple percent display for a page that allows files to be uploaded. I have a log for the start of the file upload and a log for the end of the file upload and I wanted to build a dashboard that would show the percentage of file completions (every matched file upload and download message counts as a completion). I was wanting a simple way to just show a 0 percent for any bin that didn't have a value in it but apparently that isn't available. Since I have log messages regularly I figured out how to just fake it using the query below

fields @timestamp, @message
| fields strcontains(@message, "$=File.Upload=$ [Starting]") as @FileStart,
         strcontains(@message, "$=File.Upload=$ [Finished]") as @FileEnd
| stats sum(@FileStart) as FileStarts, sum(@FileEnd) as FileFinish, sum(@FileEnd) / (sum(@FileStart)) as HitPercentage by bin(1min)

That produces almost what I need : CloudWatch Ouput The main problem is that I have the 0/0 for the bins that don't have any file uploading logs in them. It doesn't display 0, it just shows nothing and I haven't been able to find exactly what value CloudWatch produces when the divisor is 0 for an arithmetic expression. I assume it's NaN or something like that. I couldn't find anything like NVL or IsNull that I'd use in a SQL expression to replace the invalid value with 0. I didn't see any function that I can put into the "stats" section to do a conditional eval of the percent calculation. I thought that this use case would be pretty common where you want to see a time series of some set of log statements over time where you might not always have something in a particular bin but I haven't been able to figure it out.

It's even more frustrating because it's exactly what the histogram in the CloudWatch results already shows but that's only for a count of the matching logs and I just need something a little more complex. It seems like this would be a very common thing to create and I thought it would be a default behavior when using the bins for the stat calculation to return 0 for any bin that didn't have matching data so that bar or line graphs would make sense for a particular time frame without having weird broken lines where there were no logs returned in the query.

I tried to figure out a way to use COALESCE on a computed column but could not figure out how to coalesce a computed value and a constant/literal value inside of the stats block. I tried to get IF() to work but it isn't valid inside of a stats block. I could not find any equivalent to IsNull or NVL or a ternary operator that can be used in the stats block.

I am trying to do a simple percent display for a page that allows files to be uploaded. I have a log for the start of the file upload and a log for the end of the file upload and I wanted to build a dashboard that would show the percentage of file completions (every matched file upload and download message counts as a completion). I was wanting a simple way to just show a 0 percent for any bin that didn't have a value in it but apparently that isn't available. Since I have log messages regularly I figured out how to just fake it using the query below

fields @timestamp, @message
| fields strcontains(@message, "$=File.Upload=$ [Starting]") as @FileStart,
         strcontains(@message, "$=File.Upload=$ [Finished]") as @FileEnd
| stats sum(@FileStart) as FileStarts, sum(@FileEnd) as FileFinish, sum(@FileEnd) / (sum(@FileStart)) as HitPercentage by bin(1min)

That produces almost what I need : CloudWatch Ouput The main problem is that I have the 0/0 for the bins that don't have any file uploading logs in them. It doesn't display 0, it just shows nothing and I haven't been able to find exactly what value CloudWatch produces when the divisor is 0 for an arithmetic expression. I assume it's NaN or something like that. I couldn't find anything like NVL or IsNull that I'd use in a SQL expression to replace the invalid value with 0. I didn't see any function that I can put into the "stats" section to do a conditional eval of the percent calculation. I thought that this use case would be pretty common where you want to see a time series of some set of log statements over time where you might not always have something in a particular bin but I haven't been able to figure it out.

It's even more frustrating because it's exactly what the histogram in the CloudWatch results already shows but that's only for a count of the matching logs and I just need something a little more complex. It seems like this would be a very common thing to create and I thought it would be a default behavior when using the bins for the stat calculation to return 0 for any bin that didn't have matching data so that bar or line graphs would make sense for a particular time frame without having weird broken lines where there were no logs returned in the query.

I tried to figure out a way to use COALESCE on a computed column but could not figure out how to coalesce a computed value and a constant/literal value inside of the stats block. I tried to get IF() to work but it isn't valid inside of a stats block. I could not find any equivalent to IsNull or NVL or a ternary operator that can be used in the stats block.

• amazon-cloudwatch

Share Follow asked Jan 8 at 15:44 Joshua LittleJoshua Little 122 bronze badges New contributor Joshua Little is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

As detailed here, you can now use OpenSearch SQL language with Cloudwatch.

In your case, you can try following SQL query:

SELECT date_trunc('MINUTE', `@timestamp`) minute,
    SUM(CASE WHEN (`@message` like '%$=File.Upload=$ [Starting]%') THEN 1 ELSE 0 END) FileStarts,
    SUM(CASE WHEN (`@message` like '%$=File.Upload=$ [Finished]%') THEN 1 ELSE 0 END) FileFinish,
    coalesce(
        try_divide(
            SUM(CASE WHEN (`@message` like '%$=File.Upload=$ [Finished]%') THEN 1 ELSE 0 END),
            SUM(CASE WHEN (`@message` like '%$=File.Upload=$ [Starting]%') THEN 1 ELSE 0 END)
        ), 0
    ) HitPercentage
FROM  `LogGroupA`
GROUP BY date_trunc('MINUTE', `@timestamp`)

本文标签： How do I display a 0 in a CloudWatch query stats section for a percent calculationStack Overflow