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

[JeiLucero]

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=aiam_itsm_ticket_ptest_ctest_index * _raw="" problem_mapping="" system_user="" ticket_source="" | fillnull value="Not Defined"| search Assignee_Site_Country = $tok_country$ | ***where (time_submitted > "3/1/2017 12:00:00 AM" AND time_submitted < "3/15/2017 12:00:00 AM")* | search Ticket_Type=Incident | table ticket_number ,problem_abstract, severity, time_submitted, Last_Modified_Date,service_restored_date, owner_name, current_ticket_state, work_queue, asset_id, Tool, ticket_source,Ticket_Type, system_user, Assignee_Site_Country | rename ticket_number as "Incident Number" ,problem_abstract as "Description", time_submitted as "Time Submitted", severity as "Severity", owner_name as "Ticket Assignee", current_ticket_state as "Status", work_queue as "Assignment Queue", asset_id as "Portfolio Group", Tool as "Asset", ticket_source as "Ticket Source", system_user as "Requestor", Assignee_Site_Country as "Assigned Country", service_restored_date as "Resolved date", Last_Modified_Date as "Last Modified Date"

NOT WORKING SEARCH QUERY:
index=aiam_itsm_ticket_ptest_ctest_index * _raw="" problem_mapping="" system_user="" ticket_source="" | fillnull value="Not Defined"| search Assignee_Site_Country = $tok_country$ | ***eval submitted_date = strpTime(time_submitted, "%m/%d/%y %H:%M:%S %Z")|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)* | search Ticket_Type=Incident | table ticket_number ,problem_abstract, severity, time_submitted, Last_Modified_Date,service_restored_date, owner_name, current_ticket_state, work_queue, asset_id, Tool, ticket_source,Ticket_Type, system_user, Assignee_Site_Country | rename ticket_number as "Incident Number" ,problem_abstract as "Description", time_submitted as "Time Submitted", severity as "Severity", owner_name as "Ticket Assignee", current_ticket_state as "Status", work_queue as "Assignment Queue", asset_id as "Portfolio Group", Tool as "Asset", ticket_source as "Ticket Source", system_user as "Requestor", Assignee_Site_Country as "Assigned Country", service_restored_date as "Resolved date", Last_Modified_Date as "Last Modified Date"

[JeiLucero]

Hi All,

I was able to resolve the issue by converting the time_submitted to: strptime(time_submitted, "%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=aiam_itsm_ticket_ptest_ctest_index * _raw="**" problem_mapping="" system_user="" | fillnull value="Not Defined"|eval time_submitted = strptime(time_submitted, "%m/%d/%Y %I:%M:%S %p") | eval ticket_start_time = $start_tok.earliest$ | eval ticket_end_time = $start_tok.latest$ | where (time_submitted > ticket_start_time AND time_submitted < ticket_end_time) | eval time_submitted = strftime(time_submitted, "%m/%d/%Y %I:%M:%S %p")| search ticket_source="" Tool = "$tok_asset$" Ticket_Type= "$tok_tick_type$" current_ticket_state = "$tok_status$" | table ticket_number, Reported_Date ,problem_abstract, severity, time_submitted, Last_Modified_Date,service_restored_date, owner_name, current_ticket_state, work_queue, asset_id, Tool, ticket_source,Ticket_Type, system_user, Assignee_Site_Country | rename ticket_number as "Incident Number" ,problem_abstract as "Description", time_submitted as "Time Submitted", severity as "Severity", owner_name as "Ticket Assignee", current_ticket_state as "Status", work_queue as "Assignment Queue", asset_id as "Portfolio Group", Tool as "Asset", ticket_source as "Ticket Source", system_user as "Requestor", Assignee_Site_Country as "Assigned Country", service_restored_date as "Resolved date", Last_Modified_Date as "Last Modified Date"

[jkat54]

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 ticket_start/end_time is in epoch and therefore submitted_data needs to be in epoch as well.

Thanks,
jkat54

[JeiLucero]

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