All Apps and Add-ons

Query a DateTime field in the SQL query inside the splunk log

preethamony
New Member

I need to query the DateTime field value, which has a value greater than 3months duration.
The DateTime field is inside the SQL query where condition (for eg: DateTime >= "2018-06-28T08:15:12.712Z").

How can I do this?

Tags (2)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try this query.

index=foo | rex "CALL_TimeStamp=\"(?<CALL_TS>\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d.\d{3}\w)[\s\S]+UserID="(?<UserID>[^"]+)[\s\S]+to_datetime\('(?<mod_TS>[^']+) | eval called=strptime(CALL_TS, "%Y-%m-%dT%H:%M:%S.%3N%Z"), mod=strptime(mod_TS, "%Y-%m-%dT%H:%M:%S%Z") | eval days=(called-mod)/86400 | where days > 180 | stats dc(UserID)
---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

More information, please. What is your use case? Is the data already in Splunk or are you trying to get it from the database into Splunk? Are you trying to parse the query?

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

preethamony
New Member

Hello Richgalloway,

the log looks like the following

Time Event

10/1/18 CALL_TimeStamp="2018-10-01T20:27:23.994Z-IH" ID="TEST"
8:27:24.017 PM UserID="EMP01"
Starting RESTful Servoce (1811P0):
QueryMode : Standard
Query Operation : QUERY
field1, field2, field3, field4, field5, field6, field7, field8, field9, field10 FROM Emplpyee

WHERE last_modified_on >= to_datetime('2018-10-01T14:17:20Z')

In the above splunk log, there are two timestamps & an "UserID"
1. CALL_Timestamp
2. last_modified_on

I would like to get the total number of Users (UserID) queried the data for more than 6 months (by calculating the aforementioned timestamps). I am pretty new to splunk. would like to learn more. i am looking forward to hear from you

Regards,
Preetha

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...