Hi all,
I'm trying to explain my problem here and hope you'll provide a solution to me...
I've a jdbc input that reads occurrences in a database, especially a datetime field in UTC:
evtUtcDate
I need to enrich the event using a jdbc_static filter to get the data regarding this datetime:
jdbc_static {
loaders => [ {
id => "refdata"
query => "SELECT * FROM XXXX"
local_table => "refdata"
} ]
local_db_objects => [ {
name => "refdata"
index_columns => ["evtStart","evtEnd"]
columns => [ ["evtStart", "date"], ["evtEnd", "date"], ["info", "varchar(200)"] ]
} ]
local_lookups => [ {
query => "SELECT info FROM refdata WHERE evtStart <= :evt_date AND evtEnd >=:evt_date"
parameters => {
evt_id => "[id]"
evt_date => "[evtUtcDate]"
}
target => "tmp_ref"
} ]
}
My problem is that in the referential database, the format of evtStart and evtEnd is "YYYY-mm-dd" in CET timezone.
So when I compare it to an UTC date, the result could be wrong and makes logstash retrieving and enriching the event with the wrong information.
Ex:
Evt:
evtUtcDate= 2020-05-24 23:30:00.000Z
Reference:
evtStart=2020-05-15 evtEnd=2020-05-24 info=TEST1
evtStart=2020-05-25 evtEnd=2020-05-30 info=TEST2
Event is enriched with TEST1 instead of TEST2
The only way I imagine is to convert one of the date (evtUtcDate) to a new one with the CET timezone and format it as "%Y-%m-%d"
- Is there any other solution?
- How can I do that with Logstash?