Splunk Search

How to execute a dbxquery in Splunk by adjusting only the time tokens?

_pravin
Communicator
Hi Y'all,
 
I am trying to execute a dbxquery in Splunk by adjusting only the time tokens.
Splunk server is in a time zone and the database server is also in the same time zone but when I execute the query I can see that the query is executing with a lag of an hour.
 
When I execute the same in the database directly, I can see that there is no lag and the query executes perfectly.
Only difference I could sense is the usage of epoch time conversion in SPL and no conversion used in the database query which I feel might be a time zone difference between the servers.
 
 

 

 

| dbxquery query="select 100-(COUNT(DISTINCT (week)) *100 / (DATEDIFF (DATE_FORMAT(FROM_UNIXTIME('1680559200'), '%Y%m%d') , DATE_FORMAT(FROM_UNIXTIME('1672527600'), '%Y%m%d'))/7)) from `mxone_db`.`pdt_disruptions` where DATE_FORMAT (DATE, '%Y%m%d' ) between DATE_FORMAT(FROM_UNIXTIME('1672527600'), '%Y%m%d') AND DATE_FORMAT(FROM_UNIXTIME('1680559200'), '%Y%m%d')" connection="dbuser"

 

 

Solution: 47.3118
 

 

 

select 100-(COUNT(DISTINCT (week)) *100 / (DATEDIFF (DATE_FORMAT('2023-04-03', '%Y%m%d') , DATE_FORMAT('2023-01-01', '%Y%m%d'))/7)) from pdt_disruptions 
where DATE_FORMAT (DATE, '%Y%m%d' ) between DATE_FORMAT('2023-01-01', '%Y%m%d') AND DATE_FORMAT('2023-04-03', '%Y%m%d')

 

 

Solution: 46.7391
 
Not sure where is this query getting lost?
Please let me know if you have any clue of this issue.
 
Regards,
Pravin
Labels (1)
Tags (1)
0 Karma
1 Solution

tscroggins
Influencer

Hi,

Splunk DB Connect time zone conversion is explained here: https://docs.splunk.com/Documentation/DBX/3.8.0/DeployDBX/Createandmanagedatabaseconnections 

You appear to be using MySQL or a derivative; MySQL support for time zones is explained here: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html 

If both MySQL and Splunk are properly configured, time zone conversion should work as expected when using native date types (not string types). If not, you can perform an explicit conversion:

DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME('680559200'), 'Etc/UTC', 'PST8PDT'), '%Y%m%d')

To compare dates, time zone conversion shouldn't be necessary:

DATEDIFF(FROM_UNIXTIME('1680559200'), FROM_UNIXTIME('1672527600'))

 

View solution in original post

tscroggins
Influencer

Hi,

Splunk DB Connect time zone conversion is explained here: https://docs.splunk.com/Documentation/DBX/3.8.0/DeployDBX/Createandmanagedatabaseconnections 

You appear to be using MySQL or a derivative; MySQL support for time zones is explained here: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html 

If both MySQL and Splunk are properly configured, time zone conversion should work as expected when using native date types (not string types). If not, you can perform an explicit conversion:

DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME('680559200'), 'Etc/UTC', 'PST8PDT'), '%Y%m%d')

To compare dates, time zone conversion shouldn't be necessary:

DATEDIFF(FROM_UNIXTIME('1680559200'), FROM_UNIXTIME('1672527600'))

 

_pravin
Communicator

Hi @tscroggins ,

 

Thanks for the help. It seemed to fix the issue.

 

Regards,

Pravin

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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