- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?
Hello
I am pulling data from a MS SQL Server database via App DB Connect. I have an UTC timestamp field in the returned dataset, which I map to Splunk's TIMESTAMP column to have it for the _time field.
Splunk vrs is 8.2, DB Connect is 3.6.0
The problem:
The Splunk's _time field shows wrong hour, the difference to my local time vs UTC.
Question:
How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?
best regards
Altin
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you can add a TZ setting to the props.conf in db connect for that sourcetype.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI @nyc_jason
and thank you for the reply.
There is no props.conf under /<home>/splunk_app_db_connect/local
Since my DB Input is saved under an App called "Ub Security Hub" - and not under DB Connect, I did check also under /<home>/ub_security_hub/local. No no props.conf found there too.
Should I create one? If yes, under /splunk_app_db_connect/local or /ub_security_hub/local ?
And what should I write inside props.conf? Will the below
------- props.conf ---------
[<sourcetype_name>]
TZ=UTC
------- props.conf ---------
... be enough ?
please advise
best regards
Altin
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Actually, from the docs, this is now the method, when creating the database connection:
see the part re Timezone:
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for the reply @nyc_jason .
But the two parameters: timezone and localTimezoneConversionEnabled, in the documentation for db_connections.conf.spec, do appear first on version 3.7.0.
Our DB Connect is on version 3.6.0 - and the parameters above do NOT appear there:
db_connections.conf.spec - 3.6.0
How do we do this on DB Connect 3.6.0 ?
best regards
Altin
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Altink,
are you able to modify the SQL query to change the TIMESTAMP field? Here is a solution (using Oracle, but same premise):
Also, if thats the only connection you are using, you can also change the timezone at the jvm level, as it uses its own separate from the OS (unless they've been synced, but I usually see them using their own)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @nyc_jason
I can change the UTC timestamp in local timezone in my SQL query.
But this Question was asked on how do I do this on Splunk or DB Connect - and not outside.
Is this possible inside Splunk 8.2 and/or DB Connect 3.6.0 ?
best regards
Altin
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
since its an older version of dbx, you can try to add something like this to the connection string in the dbx jvm parameter settings:
-Duser.timezone=GMT
just be aware this would apply to all connections.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank You for this
but it would not be acceptable to have this thing applied to all Inputs.
best regards
Altin
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sounds like a great reason to upgrade to 3.7.0. 🙂
If this reply helps you, Karma would be appreciated.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
also the below
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use the strptime function to convert the field into a Splunk timestamp.
If this reply helps you, Karma would be appreciated.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you @richgalloway for the answer.
May be I am not getting something right, but I am not looking to display the incoming field into a UNIX timestamp.
I am looking to tell Splunk that this field is in UTC timezone and not local, so that the _time field is handled correctly.
I could do this in the SQL pull query, but I am looking for a way Splunk ingests an UTC timestamp via DB Connent
best regards
Altin
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The _time field is a Unix timestamp so if it's not being set correctly automatically, you can use strptime to do it manually. Of course, you first should choose a sourcetype that has the TZ=UTC setting, but if that doesn't work try adding this to your query.
| eval _time=strptime(database_timestamp . "Z", "<<database timestamp format>>Z")
If this reply helps you, Karma would be appreciated.