Getting Data In

How can I use the value from date time picker for my search query?

Explorer

I'm trying to utilize the date time picker (tok_starttime) for my start time and end time. Our report contains a column named time_submitted where the tok_starttime should filter the value from.

Whenever I use the query below on a search, it works fine. However, when I tried replacing the value of time_submitted with value from date time picker token, the report shows as does not load and is just returning no results found.

The idea is, the user should be able to filter the report using start time and end time. Please help on what should be the correct query for the date time pickers and if it is possible. Thank you in advance.

WORKING SEARCH QUERY:
index=aiamitsmticketptestctestindex * _raw="***" problemmapping="" system_user="" ticketsource="*" | fillnull value="Not Defined"| search AssigneeSiteCountry = $tokcountry$ | where (timesubmitted > "3/1/2017 12:00:00 AM" AND timesubmitted < "3/15/2017 12:00:00 AM") | search TicketType=Incident | table ticketnumber ,problemabstract, severity, timesubmitted, LastModifiedDate,servicerestoreddate, ownername, currentticketstate, workqueue, assetid, Tool, ticketsource,TicketType, systemuser, AssigneeSiteCountry | rename ticketnumber as "Incident Number" ,problemabstract as "Description", timesubmitted as "Time Submitted", severity as "Severity", ownername as "Ticket Assignee", currentticketstate as "Status", workqueue as "Assignment Queue", assetid as "Portfolio Group", Tool as "Asset", ticketsource as "Ticket Source", systemuser as "Requestor", AssigneeSiteCountry as "Assigned Country", servicerestoreddate as "Resolved date", LastModifiedDate as "Last Modified Date"

NOT WORKING SEARCH QUERY:
index=aiamitsmticketptestctestindex * _raw="***" problemmapping="" system_user="" ticketsource="*" | fillnull value="Not Defined"| search AssigneeSiteCountry = $tokcountry$ | eval submitteddate = strpTime(timesubmitted, "%m/%d/%y %H:%M:%S %Z")|eval ticketstarttime = $tokstarttime.earliest$| eval ticketendtime = $tokstarttime.latest$| search (submitteddate > ticketstarttime AND submitteddate < ticketendtime) | search TicketType=Incident | table ticketnumber ,problemabstract, severity, timesubmitted, LastModifiedDate,servicerestoreddate, ownername, currentticketstate, workqueue, assetid, Tool, ticketsource,TicketType, systemuser, AssigneeSiteCountry | rename ticketnumber as "Incident Number" ,problemabstract as "Description", timesubmitted as "Time Submitted", severity as "Severity", ownername as "Ticket Assignee", currentticketstate as "Status", workqueue as "Assignment Queue", assetid as "Portfolio Group", Tool as "Asset", ticketsource as "Ticket Source", systemuser as "Requestor", AssigneeSiteCountry as "Assigned Country", servicerestoreddate as "Resolved date", LastModifiedDate as "Last Modified Date"

0 Karma

Explorer

Hi All,

I was able to resolve the issue by converting the timesubmitted to: ***strptime(timesubmitted, "%m/%d/%Y %I:%M:%S %p")*** then using the earliest and latest value from my date time dropdown token. Afterwards, I converted it back again to a readable format using: strftime(time_submitted, "%m/%d/%Y %I:%M:%S %p").

See below working query:

index=aiamitsmticketptestctestindex * _raw="***" problemmapping="" system_user="" | fillnull value="Not Defined"|eval timesubmitted = strptime(timesubmitted, "%m/%d/%Y %I:%M:%S %p") | eval ticketstarttime = $starttok.earliest$ | eval ticketendtime = $starttok.latest$ | where (timesubmitted > ticketstarttime AND timesubmitted < ticketendtime) | eval timesubmitted = strftime(timesubmitted, "%m/%d/%Y %I:%M:%S %p")| search ticketsource="*" Tool = "$tokasset$" TicketType= "$tokticktype$" currentticketstate = "$tokstatus$" | table ticketnumber, ReportedDate ,problemabstract, severity, timesubmitted, LastModifiedDate,servicerestoreddate, ownername, currentticketstate, workqueue, assetid, Tool, ticketsource,TicketType, systemuser, AssigneeSiteCountry | rename ticketnumber as "Incident Number" ,problemabstract as "Description", timesubmitted as "Time Submitted", severity as "Severity", ownername as "Ticket Assignee", currentticketstate as "Status", workqueue as "Assignment Queue", assetid as "Portfolio Group", Tool as "Asset", ticketsource as "Ticket Source", systemuser as "Requestor", AssigneeSiteCountry as "Assigned Country", servicerestoreddate as "Resolved date", LastModifiedDate as "Last Modified Date"

0 Karma

SplunkTrust
SplunkTrust

Try changing your time eval portion of your search to this:

eval submitted_date = strpTime(time_submitted, "%s")
| eval ticket_start_time = $tok_starttime.earliest$
| eval ticket_end_time = $tok_starttime.latest$
| search (submitted_date > ticket_start_time AND submitted_date < ticket_end_time)*

I believe you'll find ticketstart/endtime is in epoch and therefore submitted_data needs to be in epoch as well.

Thanks,
jkat54

Explorer

Hi jkat54,

Thank you for your assistance. I tried the suggestion but didn't work but your post give me some idea on how to resolve it. With a help from my colleague, we were able to find a way to resolve the issue . I posted the resolution below.

Thanks,
JEI

0 Karma