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

[preethamony]

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?

[richgalloway]

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)

[richgalloway]

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?

[preethamony]

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