How to find events between time ranges for a service now integrated with Splunk

[varsh_6_8_6]

Hi

I am trying to see for a ticket that is not assigned to an analyst for the last 15 mins from the time of arrival. I have only the timestamp system_updated meaning when ever there is any change in the INC the timestamping gets updated for that event.

index="servicenow" INC* sourcetype="snow:incident" |where assigned_to = ""
|rename sys_updated_on as earliest
|eval date = strptime(earliest, "%Y-%m-%d %H:%M:%S.%3N")
| eval start=strftime(strptime(earliest, "%Y-%m-%d %H:%M:%S.%2N") + 1, "%Y-%m-%d %H:%M:%S.%2N")
| eval end=strftime(strptime(earliest, "%Y-%m-%d %H:%M:%S.%2N") + 900, "%Y-%m-%d %H:%M:%S.%2N")
|table ticket_number start end

So here I have taken the time when the assigned to field was empty and that is the iNC created time as well. From that next second to the 15 min I need to know the series of events with the help of  start and end values.  When I do so I am not able to see any events. Please help

[dtburrows3 · Accepted solution]

If I understand the question correctly something like this may work.

index=servicenow sourcetype=snow:incident 
    | fields + _time, number, sys_updated_on, dv_u_last_update, dv_state, active, dv_sys_class_name, dv_assigned_to
    | sort 0 +_time
    | eval
        dv_assigned_to=if(
            'dv_assigned_to'=="",
                null(),
                'dv_assigned_to'
            )
    | eventstats
        earliest(dv_state) as first_state,
        earliest(sys_updated_on) as first_timestamp,
        values(dv_assigned_to) as assignees
            by number
    ``` only include events from inc# that fall into state=new as its first event in the search time window ```
    | where 'first_state'=="New"
    | tojson str(sys_updated_on) str(dv_state) str(active) str(dv_assigned_to) output_field=snow_incident_json
    | stats
        values(first_timestamp) as first_timestamp,
        earliest(eval(case('dv_assigned_to'=='assignees', sys_updated_on))) as first_assignment_timestamp,
        list(snow_incident_json) as snow_incident_timestamp
            by number
    | foreach first*_timestamp
        [
            | eval
                first<<MATCHSTR>>_epoch=strptime('<<FIELD>>', "%Y-%m-%d %H:%M:%S")
            ]
    | eval
        minutes_since_incident_creation_to_assignment=round(('first_assignment_epoch'-'first_epoch')/60, 2)
    | where 'minutes_since_incident_creation_to_assignment'>15
    | fields - *_epoch

 The resulting dataset should look something like this.

I saw you mention that you needed to see the sequence of events that occurred for incidents that were unassigned for the initial 15 minutes after creation.

You can see the details are packaged as a multivalue field of json_objects. You should be able to add any field you want to this by just including in the tojson command.