By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Athena query that filter result from the last 1 hour ?

0

Do anyone know how to filter athena waf log from the last hour?

for example SELECT count(*) AS countRequests,httprequest.clientip, terminatingruleid, httprequest.uri FROM "waf_logs" WHERE date >=date_format(current_date - interval '7' day, '%Y/%m/%d') GROUP BY httprequest.clientip,terminatingruleid, httprequest.uri ORDER BY count(*) DESC

Topics
Analytics
Tags
Amazon Athena
Language
English
Elaido
asked 10 months ago600 views
2 Answers
1
Accepted Answer

Try writing the filter like this:

WHERE timestamp>=to_unixtime(date_add('hour', -1, now()))*1000
EXPERT
Leo K
answered 10 months ago
profile pictureAWS
EXPERT
Jordan Sandri
reviewed a day ago
  • Elaido
    10 months ago

    works! many thanks

0

Try this

SELECT 
    count(*) AS countRequests,
    httprequest.clientip,
    terminatingruleid,
    httprequest.uri
FROM 
    "waf_logs"
WHERE 
    date_diff('hour', from_iso8601_timestamp(timestamp), now()) <= 1
GROUP BY 
    httprequest.clientip,
    terminatingruleid,
    httprequest.uri
ORDER BY 
    countRequests DESC;
profile picture
EXPERT
Oleksii Bebych
answered 10 months ago
  • Elaido
    10 months ago

    FUNCTION_NOT_FOUND: line 9:23: Unexpected parameters (bigint) for function from_iso8601_timestamp. Expected: from_iso8601_timestamp(varchar(x)), from_iso8601_timestamp(char(x)) This query ran against the "default" database, unless qualified by the query.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content