Getting Data In

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

altink
Contributor

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

Labels (1)
0 Karma

nyc_jason
Splunk Employee
Splunk Employee

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

0 Karma

altink
Contributor

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

0 Karma

nyc_jason
Splunk Employee
Splunk Employee

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:

timezone.png

altink
Contributor

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

0 Karma

nyc_jason
Splunk Employee
Splunk Employee

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)

0 Karma

altink
Contributor

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

Tags (1)
0 Karma

nyc_jason
Splunk Employee
Splunk Employee

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.

0 Karma

altink
Contributor

Thank You for this

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

best regards
Altin

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Sounds like a great reason to upgrade to 3.7.0.  🙂

---
If this reply helps you, Karma would be appreciated.
0 Karma

altink
Contributor

also the below

altink_0-1663598906878.png

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

altink
Contributor

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.
0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...