Splunk Search

Time conversion in dbxquery

jerinvarghese
Communicator

Hi all,

need help in converting the time format. I want to add another 10.5 hrs to the submit date.

Below query i tried and failed.

 

1. | eval Time=strftime(Submit_Date+10.5*3600,"%Y-%m-%d %l:%M:%S %p")
2. | fieldformat Time=strftime(Submit_Date+10.5*3600,,"%Y-%m-%d %H:%M:%S")

 

 

Complete code is below.

 

| dbxquery connection="ITDW" shortnames=true  query="SELECT
GETDATE() as 'CurrentTime',
[Incident_Number],
INC.[Company],
[Customer],
[Summary],
[Notes],
[Service],
[CI],
[Impact],
[Urgency],
[Priority],
[Incident_Type],
[Assigned_Support_Group],
[Assigned_Support_Organization],
[Status],
[Status_Reason],
[Resolution],
[Reported_Date],
[Responded_Date],
[Closed_Date],
[Last_Resolved_Date],
[Submit_Date],
[Last_Modified_Date],
[Owner_Group]
FROM [shared].[ITSM_INC_MAIN] INC
LEFT OUTER JOIN [shared].[ITSM_CMDB_People_Main] PPL
ON 
INC.Customer_ID = PPL.Person_ID
WHERE ([Assigned_Support_Group] = 'Ops-WAN and LAN Incidents') 
AND [Submit_Date] BETWEEN DATEADD(D,-3,GETDATE()) AND GETDATE()"
| table Customer, Incident_Number, Submit_Date

 

 

How can i edit the time in dbxquery, please help in that.

when ever I try strftime, the output is coming blank without no value.

Labels (6)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

I haven't used dbxquery but I suspect Submit_Date is coming back as a string, so you need to parse the string into a time value

...
| eval Time=strptime(Submit_Date, "<whatever time format the query is returning")
...

You can then add the 10.5 hours to this

...
| eval Time=Time+(10.5*3600)
...

 (you might be able to put the addition at the end of the previous eval)

Then, to change the way it is displayed, use fieldformat

...
| fieldformat Time=strftime(Time,"%Y-%m-%d %H:%M:%S")
...

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

I haven't used dbxquery but I suspect Submit_Date is coming back as a string, so you need to parse the string into a time value

...
| eval Time=strptime(Submit_Date, "<whatever time format the query is returning")
...

You can then add the 10.5 hours to this

...
| eval Time=Time+(10.5*3600)
...

 (you might be able to put the addition at the end of the previous eval)

Then, to change the way it is displayed, use fieldformat

...
| fieldformat Time=strftime(Time,"%Y-%m-%d %H:%M:%S")
...
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 ...