topic Re: Help with evaluating date in Splunk Search

Help with evaluating date

gabarrygowin — Tue, 29 Sep 2020 20:17:17 GMT

Hi all,

Struggling to get my Service Now times to evaluate just tickets older than 30 days. The date picker isn't picking them up (suspect because I renamed to a user friendly "Ticket Created" column. Wanted help to insert an eval command and keep the "Ticket Created" column user friendly.

Here's my current search:

index=snow (NOT "https://generalatomicsdev.service-now.com/") (sourcetype=snow:sc_task  dv_assignment_group="SystemsInfra Operations" dv_state!=Closed*) OR (sourcetype=snow:incident assignment_group_name="SystemsInfra Operations" dv_state!=Resolved*) dv_assigned_to!="" | stats dc(number) by number,dv_assigned_to, dv_opened_at, sys_updated_on, sys_updated_by | sort dv_opened_at | fields - dc(number) | rename  number AS "Ticket Id", dv_assigned_to AS "Ticket Holder", dv_opened_at AS "Ticket Created", sys_updated_on AS "Last Updated", sys_updated_by AS "Updated By"

I've tried inserting a "search dv_opened_at>=-30d" several ways, but appears Splunk wants a defined date format that I struggle to figure out. So went with inserting "eval dv_opened_at-relative_time(now(),"-30d")" but that changed the format of the "Ticket Created" column.

Ideas?

Re: Help with evaluating date

poete — Fri, 06 Jul 2018 08:20:24 GMT

Hello @gabarrygowin,

your current search is not all here, right?

Re: Help with evaluating date

somesoni2 — Tue, 29 Sep 2020 20:20:51 GMT

If your dv_opened_at field value is in epoch format, try like this

index=snow (NOT "https://generalatomicsdev.service-now.com/") (sourcetype=snow:sc_task  dv_assignment_group="SystemsInfra Operations" dv_state!=Closed*) OR (sourcetype=snow:incident assignment_group_name="SystemsInfra Operations" dv_state!=Resolved*) dv_assigned_to!="" 
| where dv_opened_at>=relative_time(now(),"-30d")
| stats dc(number) by number,dv_assigned_to, dv_opened_at, sys_updated_on, sys_updated_by | sort dv_opened_at | fields - dc(number) | rename  number AS "Ticket Id", dv_assigned_to AS "Ticket Holder", dv_opened_at AS "Ticket Created", sys_updated_on AS "Last Updated", sys_updated_by AS "Updated By"

If it's a timestamp in string format, try like this (assuming the timestamp format is %Y-%m-%d %H:%M:%S, update per your date format)

index=snow (NOT "https://generalatomicsdev.service-now.com/") (sourcetype=snow:sc_task  dv_assignment_group="SystemsInfra Operations" dv_state!=Closed*) OR (sourcetype=snow:incident assignment_group_name="SystemsInfra Operations" dv_state!=Resolved*) dv_assigned_to!="" 
| where strptime(dv_opened_at,"%Y-%m-%d %H:%M:%S")>=relative_time(now(),"-30d")
| stats dc(number) by number,dv_assigned_to, dv_opened_at, sys_updated_on, sys_updated_by | sort dv_opened_at | fields - dc(number) | rename  number AS "Ticket Id", dv_assigned_to AS "Ticket Holder", dv_opened_at AS "Ticket Created", sys_updated_on AS "Last Updated", sys_updated_by AS "Updated By"

Re: Help with evaluating date

gabarrygowin — Tue, 29 Sep 2020 20:20:54 GMT

Hey Somesoni2!

Been a bit since I needed your lifeline. 🙂

I actually got it working after submitting the question and had to wait until today to add. Here's what I did that appears to work well:

"index=snow (NOT "https://generalatomicsdev.service-now.com/") (sourcetype=snow:incident (assignment_group_name="Applications (COTS)" OR assignment_group_name="Account Administration" OR assignment_group_name="SystemsInfra Operations") dv_state!=Resolved) OR (sourcetype=snow:sc_task (dv_assignment_group="Applications (COTS)" OR dv_assignment_group="Account Administration" OR dv_assignment_group="SystemsInfra Operations") dv_state!=Closed*) | rename number AS "Ticket Id", dv_assigned_to AS "Ticket Holder", dv_opened_at AS "Ticket Created", sys_updated_on AS "Last Updated" | where strptime('Ticket Created', "%m/%d/%Y %H:%M:%S %p")