How to build a query to get the count of opened and resolved incidents every hour in a day?

[akriti]

Hi,

I'm trying to build a query to get the count of opened and resolved incidents every hour in a day but the numbers are not tallying. Not sure if the issue might be the fact that ServiceNow uses GMT and therefore all the tickets have the dv_opened_at and dv_closed_at field in terms of GMT and the _time field is the local time which in my case is EST. I'm using the following query but not getting the correct numbers:

index=xyz 
|eval _time = strptime(dv_opened_at,"%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| addinfo
| where _time >= info_min_time AND _time <= info_max_time
| eventstats min(_time) AS earliest_time BY sys_id
| where _time = earliest_time
| timechart span=1h dc(sys_id) AS "Opened Tickets"
| appendcols
[ search index=xyz
|eval _time = strptime(dv_resolved_at,"%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| addinfo
| where _time >= info_min_time AND _time <= info_max_time
| eventstats min(_time) AS earliest_time BY sys_id
| where _time = earliest_time
| timechart span=1h dc(sys_id) AS "Closed Tickets"]

Does anyone know how I can fix the query to get the correct number of incidents opened and closed every hour on a specific day?

[akriti]

2/10/22 1:01:04.000 PM

endpoint="xyz",parent="",dv_parent="",caused_by="",dv_caused_by="",watch_list="",dv_watch_list="",sys_updated_on="2022-02-1018:01:04",dv_sys_updated_on="2022-02-1018:01:04",u_major_incident_duration="",dv_u_major_incident_duration="",u_resolved_by="",dv_u_resolved_by="",u_vendor_problem_number="",dv_u_vendor_problem_number="",skills="",dv_skills="",lessons_learned="",dv_lessons_learned="None",u_activity_code="",dv_u_activity_code="None",state="110",dv_state="Queued",knowledge="false",dv_knowledge="false",u_sub_assembly_1="NA",dv_u_sub_assembly_1="NA",u_sub_assembly_2="NA",dv_u_sub_assembly_2="NA",u_sub_assembly_4="",dv_u_sub_assembly_4="",u_callback_indicator="no",dv_u_callback_indicator="No",impact="4",dv_impact="4-Minimal",active="true",dv_active="true",u_user_participation_triage="Yes",dv_u_user_participation_triage="Yes",u_affected_date="2022-02-1017:50:21",dv_u_affected_date="2022-02-1017:50:21",u_record_producer="",dv_u_record_producer="",group_list="",dv_group_list="",u_escape_ticket="",dv_u_escape_ticket="None",u_location_number="",dv_u_location_number="",major_incident_state="",dv_major_incident_state="None",u_reported_severity="",dv_u_reported_severity="",correlation_display="",dv_correlation_display="",u_ac_jira_ticket_number="",dv_u_ac_jira_ticket_number="",u_service_desk_call_made="no",dv_u_service_desk_call_made="No",u_location_type="-- None--",dv_u_location_type="-- None--",u_voice_trust_reason="",dv_u_voice_trust_reason="None",u_qs_type="Incident",dv_u_qs_type="Incident",u_azure_business_group="",dv_u_azure_business_group="None",service_offering="",dv_service_offering="",u_diagnostics="",dv_u_diagnostics="None",u_integration_state="",dv_u_integration_state="None",follow_up="",dv_follow_up="",parent_incident="",dv_parent_incident="",u_external_source="",dv_u_external_source="",reopened_by="",dv_reopened_by="",u_external_ticket="",dv_u_external_ticket="",u_azure_technology="",dv_u_azure_technology="None",x_pd_integration_incident_key="",dv_x_pd_integration_incident_key="",u_user_request_for_update_re="",dv_u_user_request_for_update_re="",agile_story="",dv_agile_story="",escalation="0",dv_escalation="Normal",correlation_id="",dv_correlation_id="",u_line_of_business="",dv_u_line_of_business="None",u_tech_support_dispatch="0",dv_u_tech_support_dispatch="No",u_business_unit="",dv_u_business_unit="",u_callback="",dv_u_callback="",u_integration_provider_id="",dv_u_integration_provider_id="",u_integration_callback_flag="false",dv_u_integration_callback_flag="false",u_asset_details="",dv_u_asset_details="None",made_sla="true",dv_made_sla="true",u_vendor_closed="",dv_u_vendor_closed="",u_external_details="",dv_u_external_details="",u_remote_take_over="Attempted_but_not_successful",dv_u_remote_take_over="Attempted_but_not_successful",user_input="",dv_user_input="",sys_created_on="2022-02-1018:00:56",dv_sys_created_on="2022-02-1018:00:56",actions_taken="",dv_actions_taken="",route_reason="",dv_route_reason="",u_customer_induced="",dv_u_customer_induced="None",calendar_stc="",dv_calendar_stc="",u_callback_time="",dv_u_callback_time="",closed_at="",dv_closed_at="",u_vendor_confirmation="",dv_u_vendor_confirmation="",u_affected_companies="",dv_u_affected_companies="",u_received="",dv_u_received="",u_opened_by_group="ab5669c44fdf060066e000fe9310c7a4",business_impact="",dv_business_impact="",rfc="",dv_rfc="",time_worked="",dv_time_worked="",u_sla_exclusion_justification="",dv_u_sla_exclusion_justification="",u_escalated="",dv_u_escalated="",work_end="",dv_work_end="",subcategory="",dv_subcategory="None",close_code="",dv_close_code="None",assignment_group="a35669c44fdf060066e000fe9310c79e",business_stc="",dv_business_stc="",description="",dv_description="",u_ibm_mcms="",dv_u_ibm_mcms="",sys_id="9898f1161ba185102d1d8407ec4bcb7a",dv_sys_id="9898f1161ba185102d1d8407ec4bcb7a",u_qs_rca_required="false",dv_u_qs_rca_required="false",urgency="4",dv_urgency="4-Minimal",u_number_of_events="",dv_u_number_of_events="",company="",dv_company="",severity="4",dv_severity="4-Minor",overview="",dv_overview="None",u_boolean_3="false",dv_u_boolean_3="false",approval="notrequested",dv_approval="NotYetRequested",u_boolean_1="false",dv_u_boolean_1="false",u_support_language_pref="english",dv_u_support_language_pref="English",reopen_count="0",dv_reopen_count="0",sys_tags="",dv_sys_tags="",u_contact_number="",dv_u_contact_number="",u_sra_number="",dv_u_sra_number="",u_issue_start_time="",dv_u_issue_start_time="",u_work_item="",dv_u_work_item="None",u_resolution_subcategory="",dv_u_resolution_subcategory="None",location="822e0224dbb55300a86b5068dc961934",dv_location="YYZHC",u_mi_duration="",dv_u_mi_duration="",u_mi_managed_by="",dv_u_mi_managed_by="None",u_mi_impact_time="",dv_u_mi_impact_time="",u_ac_status_time="",dv_u_ac_status_time="",promoted_by="",dv_promoted_by="",u_total_duration="",dv_u_total_duration="",u_call_information="",dv_u_call_information="",u_integration_state_2="",dv_u_integration_state_2="",upon_reject="cancel",dv_upon_reject="CancelallfutureTasks",u_temporary_workaround="",dv_u_temporary_workaround="",u_left_courtesy_card="",dv_u_left_courtesy_card="None",approval_history="",dv_approval_history="",u_qs_major_incident="false",dv_u_qs_major_incident="false",u_shipping_carrier="",dv_u_shipping_carrier="None",number="INC1326925",dv_number="INC1326925",proposed_by="",dv_proposed_by="",u_kiosk_on_site_response_arrival_time="",dv_u_kiosk_on_site_response_arrival_time="",u_workaround_date="",dv_u_workaround_date="",u_qs_fcresolve="false",dv_u_qs_fcresolve="false",x_pd_integration_incident="",dv_x_pd_integration_incident="",u_device_name_ref="0da9ffdcdb863b00a86b5068dc961970",u_vendor_service="",dv_u_vendor_service="None",u_tech_time="",dv_u_tech_time="",order="",dv_order="",u_waybill_number="",dv_u_waybill_number="",cmdb_ci="52310ab14f4a5600329b9acf9310c7cb",work_notes_list="",dv_work_notes_list="",priority="4",dv_priority="4-Low",sys_domain_path="/",dv_sys_domain_path="/",u_non_business_related_support="",dv_u_non_business_related_support="None",business_duration="",dv_business_duration="",u_aimia_incident_number="",dv_u_aimia_incident_number="",u_ipad_serial_number="",dv_u_ipad_serial_number="",u_storage="",dv_u_storage="None",approval_set="",dv_approval_set="",x_pd_integration_incident_id="",dv_x_pd_integration_incident_id="",u_shipped="",dv_u_shipped="",universal_request="",dv_universal_request=",dv_assigned_to="",u_mfp_hostname="",dv_u_mfp_hostname="",sla_due="",dv_sla_due="UNKNOWN",u_travel_time="",dv_u_travel_time="",u_integration_source_ci_id="",dv_u_integration_source_ci_id="",upon_approval="proceed",dv_upon_approval="ProceedtoNextTask",u_opportunity_details="",dv_u_opportunity_details="",u_integration_source_system="",dv_u_integration_source_system="",u_asset_name="",dv_u_asset_name="",x_pd_integration_conf_bridge="",dv_x_pd_integration_conf_bridge="",u_sra="",dv_u_sra="None",u_critical_application_1="",dv_u_critical_application_1="None",promoted_on="",dv_promoted_on="",u_vendor_scheduled="",dv_u_vendor_scheduled="",child_incidents="0",dv_child_incidents="0",u_number_of_opportunities="",dv_u_number_of_opportunities="",task_effective_number="INC1326925",dv_task_effective_number="INC1326925",resolved_by="",dv_resolved_by="",u_imaging_reimaging="",dv_u_imaging_reimaging="None",opened_by="964ede251be46c102d1d8407ec4bcbec",u_mi_start_time="",dv_u_mi_start_time="",u_service_restore_time="",dv_u_service_restore_time="",sys_domain="global",dv_sys_domain="global",u_restoral_action="NA",dv_u_restoral_action="NA",proposed_on="",dv_proposed_on="",u_operational_impact="0",dv_u_operational_impact="None",u_actual_start="",dv_u_actual_start="",u_tmpchtktemlsent="false",dv_u_tmpchtktemlsent="false",u_vendor_po_number="",dv_u_vendor_po_number="",business_service="",dv_business_service="",u_detected_by_monitoring="false",dv_u_detected_by_monitoring="false",u_location_floor="",dv_u_location_floor="",u_on_hold_reason="",dv_u_on_hold_reason="",u_failed_attempts="",dv_u_failed_attempts="None",expected_start="",dv_expected_start="",opened_at="2022-02-1017:50:21",dv_opened_at="2022-02-1017:50:21",u_business_feature="",dv_u_business_feature="",reopened_time="",dv_reopened_time="",resolved_at="2022-02-1018:50:21",dv_resolved_at="2022-02-1018:50:21",u_kiosk_on_site_response="",dv_u_kiosk_on_site_response="None",cause="",dv_cause=""

[akriti]

In the event you can see that the event timestamp is in EST while the dv_opened_at and dv_resolved_at fields are in GMT

[PickleRick · Accepted solution]

The event timestamp in splunk is internally stored as a "unix time" - number of seconds that passed since midnight Jan 1st 1970 (the epoch). It's only rendered in the user interface according to your local timezone settings.

When you parse a timestamp with strptime() and the timestamp doesn't include the timezone information, it's getting parsed in your local timezone as well. So the same string - for example "2022-02-1018:01:04" when parsed as "%F%T" will give you your local 18:01:04. But if you append a "Z" to it to get "2022-02-1018:01:04Z" and parse it with timezone information ("%F%T%Z"), you'll get your time parsed properly as GMT timestamp. But if you strftime it back to string representation, you'll get the timestamp rendered in your local timezone.

See for yourself.

| makeresults
| eval localtimestring="2022-02-1018:01:04"
| eval gmttimestring=localtimestring."Z"
| eval localtime=strptime(localtimestring,"%F%T")
| eval gmttime=strptime(gmttimestring,"%F%T%Z")
| eval localrendered=strftime(localtime,"%c")
| eval gmtrendered=strftime(gmttime,"%c")

 Yes, I know it's confusing sometimes 🙂

[akriti]

Hey @PickleRick  thanks for the explanation! I now understand how it works!

I will try it out and let you know if it works in my query!

[PickleRick]

Show us your events. I've never seen data from ServiceNow.