Slide 24
Slide 24 text
CDN Hit Ratioの可視化
WITH
capture(`cs-uri-stem`, r'.+/(?P[^?/]+)(/)?(\?.+)?') as (`uri-stem-filename`), –- QueryStringの除去、ディレクトリの場合の／有無を正規化したもの
if(position(`uri-stem-filename`, '.') is null, 'page', 'asset') as `page-or-asset`, -- 正規化されたファイルパートを元に、拡張子有無でアセット類かどうかを判別
if(`x-edge-response-result-type` in ('Hit', 'RefreshHit'), 'hit', 'miss') as `hit-or-miss` -- CloudFrontでのキャッシュ状況
SELECT
filter(count(*), WHERE `hit-or-miss` = 'hit') / count(*) * 100 as 'overall',
filter(count(*), WHERE `hit-or-miss` = 'hit' AND `page-or-asset` = 'page') / filter(count(*), WHERE `page-or-asset` = 'page') * 100 as 'page',
filter(count(*), WHERE `hit-or-miss` = 'hit' AND `page-or-asset` = 'asset') / filter(count(*), WHERE `page-or-asset` = 'asset') * 100 as 'asset'
FROM Log
WHERE
`client-ip` IS NOT NULL AND `cs-host` = {{hostname}}
SINCE 30 minutes ago TIMESERIES
24
NRQLの紹介