Getting Data In

How to configure the timezone by sourcetype?

renanprado96
Path Finder

I'm doing like this:

FIELD_NAMES = DATAAREAID,RECID,DATAAREAID2,ITEMID,TRANSDATE,SUMOFQTYSEND,SUMOFQTYRET,RECIDLINE,TRANSDATETIME,DATAAREAID3,ITEMNAME
INDEXED_EXTRACTIONS = csv
TIME_PREFIX = .{0,}TRANSDATETIME=
TIME_FORMAT = %s%3N
TZ = America/Sao_Paulo
FIELD_DELIMITER = ,
KV_MODE = none
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
category = Structured
description = Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled = false
pulldown_type = true

I have to just put TZ = America / Sao_Paulo?

Event example:
alt text

7/18/16
7:52:04.000 AM  
"2016-07-18 07:52:04" DATAAREAID="206", RECID=5637144593, DATAAREAID#2="206", ITEMID="002.0001.168", TRANSDATE=1468810800000, SUMOFQTYSEND=1.000000000000, SUMOFQTYRET=0E-12, RECIDLINE=5637279183, TRANSDATETIME=1468839124000, TRANSDATETIMETZID=37001, DATAAREAID#3="206", ITEMNAME="PRINT-INS-RICOH 5000 TINTA DYE PLUS CYAN"

Thank you!

0 Karma
1 Solution

dennisaraujo
Path Finder

Hi guys,
I had the same problem.

Problem: Splunk connected via DBConnect v2 in SQL, recorded time field in SQL with GMT timezone, but Splunk interprets the data as localtime.

Changing the configuration file Splunk \ etc \ apps \ splunk_app_db_connect \ Local \ props.conf include the TZ settings the result is the same, nothing changes.

The TZ parameter configuration works out of DBConnect v2.

My solution in SQL:

SELECT CONVERT (datetime, SWITCHOFFSET (CONVERT (datetimeoffset, MyTable.UtcColumn) DATENAME (TzOffset, SYSDATETIMEOFFSET ()))) AS ColumnInLocalTime FROM MyTable

Works, just run the query in DBConnect v2.

View solution in original post

0 Karma

kairobin
Path Finder

Renandprado96: Did it work?

0 Karma

kairobin
Path Finder

Did it work?

0 Karma

dennisaraujo
Path Finder

Hi guys,
I had the same problem.

Problem: Splunk connected via DBConnect v2 in SQL, recorded time field in SQL with GMT timezone, but Splunk interprets the data as localtime.

Changing the configuration file Splunk \ etc \ apps \ splunk_app_db_connect \ Local \ props.conf include the TZ settings the result is the same, nothing changes.

The TZ parameter configuration works out of DBConnect v2.

My solution in SQL:

SELECT CONVERT (datetime, SWITCHOFFSET (CONVERT (datetimeoffset, MyTable.UtcColumn) DATENAME (TzOffset, SYSDATETIMEOFFSET ()))) AS ColumnInLocalTime FROM MyTable

Works, just run the query in DBConnect v2.

0 Karma

renanprado96
Path Finder

Thank you brother!

0 Karma

woodcock
Esteemed Legend

Try this:

[YourSourceTypeHere]
TIME_PREFIX = TRANSDATE\s*=\s*
TIME_FORMAT = %s%3N
TZ = America/Sao_Paulo
KV_MODE = auto
0 Karma

renanprado96
Path Finder

I did, restarted, injected new data, but still did not work.
Did I do something wrong?

[dynamicsAX_csv]
FIELD_NAMES = DATAAREAID,RECID,DATAAREAID2,ITEMID,TRANSDATE,SUMOFQTYSEND,SUMOFQTYRET,RECIDLINE,TRANSDATETIME,DATAAREAID3,ITEMNAME
INDEXED_EXTRACTIONS = csv
TIME_PREFIX = TRANSDATETIME\s*=\s*
TIME_FORMAT = %s%3N
TZ = America/Sao_Paulo
KV_MODE = auto
FIELD_DELIMITER = ,
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
category = Structured
description = Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled = false
pulldown_type = true

Thanks for the support...

0 Karma

woodcock
Esteemed Legend

Yes, try it with ONLY what I gave you. Your extra stuff is overcomplicated.

0 Karma

renanprado96
Path Finder

I tried to use just what you ordered (without extra content), insert new data, but still did not work. At the researched it was to be simple, do not know what's going on. I will try to choose a solution by the query for now ...

[dynamicsAX_csv]
FIELD_NAMES = DATAAREAID,RECID,DATAAREAID2,ITEMID,TRANSDATE,SUMOFQTYSEND,SUMOFQTYRET,RECIDLINE,TRANSDATETIME,DATAAREAID3,ITEMNAME
INDEXED_EXTRACTIONS = csv
TIME_PREFIX = TRANSDATE\s*=\s*
TIME_FORMAT = %s%3N
TZ = America/Sao_Paulo
KV_MODE = auto
0 Karma

woodcock
Esteemed Legend

NO! Get red of INDEXED EXTRACTIONS. How can I be more clear? Use ONLY the settings that I listed. Your data ALREADY has KVPs so let's make it simple and use them.

0 Karma

renanprado96
Path Finder

Or by regex

0 Karma

adamblock2
Path Finder

The following appears in the Splunk documentation (http://docs.splunk.com/Documentation/Splunk/6.4.2/Data/Applytimezoneoffsetstotimestamps)

Configure time zones by adding a TZ attribute to the appropriate stanza in props.conf. The TZ attribute recognizes zoneinfo TZ IDs. (See all the time zone TZ IDs in the zoneinfo (TZ) database.) Inside the stanza for a host, source, or source type, set the TZ attribute to the TZ ID for the desired time zone. This should be the time zone of the events coming from that host, source, or sourcetype.

0 Karma

renanprado96
Path Finder

But I put TZ = America / Sao_Paulo, "America / Sao_Paulo" is quoted in this list.
And it did not work!

Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...