topic Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one? in Getting Data In

How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

altink — Thu, 15 Sep 2022 15:12:50 GMT

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

Re: Timestamp field as UTC via DB Connect

richgalloway — Thu, 15 Sep 2022 14:03:40 GMT

Use the strptime function to convert the field into a Splunk timestamp.

Re: Timestamp field as UTC via DB Connect

altink — Fri, 16 Sep 2022 14:18:24 GMT

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

Re: Timestamp field as UTC via DB Connect

richgalloway — Fri, 16 Sep 2022 15:27:54 GMT

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")

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

nyc_jason — Sat, 17 Sep 2022 05:56:54 GMT

you can add a TZ setting to the props.conf in db connect for that sourcetype.

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

altink — Mon, 19 Sep 2022 14:42:03 GMT

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

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

altink — Mon, 19 Sep 2022 14:52:35 GMT

also the below

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

nyc_jason — Mon, 19 Sep 2022 15:27:53 GMT

Actually, from the docs, this is now the method, when creating the database connection:

https://docs.splunk.com/Documentation/DBX/3.10.0/DeployDBX/Createandmanagedatabaseconnections#Create_a_database_connection

see the part re Timezone:

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

altink — Tue, 20 Sep 2022 13:46:38 GMT

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

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

richgalloway — Tue, 20 Sep 2022 14:10:26 GMT

Sounds like a great reason to upgrade to 3.7.0. 🙂

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

nyc_jason — Tue, 20 Sep 2022 14:21:21 GMT

Hi Altink,

are you able to modify the SQL query to change the TIMESTAMP field? Here is a solution (using Oracle, but same premise):

https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-configure-time-in-Splunk-DB-Connect-and-the-Add-on-for/m-p/399225

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)

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

altink — Tue, 20 Sep 2022 15:52:42 GMT

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

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

nyc_jason — Tue, 20 Sep 2022 16:35:25 GMT

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.

Re: How do I tell Splunk (or DB Connect) that the incoming timestamp field is an UTC one?

altink — Wed, 21 Sep 2022 13:09:26 GMT

Thank You for this

but it would not be acceptable to have this thing applied to all Inputs.

best regards
Altin