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

Elaido
posta 2 mesi fa142 visualizzazioni
2 Risposte
1
Risposta accettata

Try writing the filter like this:

WHERE timestamp>=to_unixtime(date_add('hour', -1, now()))*1000
ESPERTO
Leo K
con risposta 2 mesi fa
  • 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
ESPERTO
con risposta 2 mesi fa
  • 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.

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande