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

[altink]

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

[nyc_jason]

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

[altink]

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

[nyc_jason]

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...

see the part re Timezone:

[altink]

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

[nyc_jason]

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-...

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)

[altink]

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

[nyc_jason]

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.

[altink]

Thank You for this

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

best regards
Altin

[richgalloway]

Sounds like a great reason to upgrade to 3.7.0.  🙂

[altink]

also the below

[richgalloway]

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

[altink]

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

[richgalloway]

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