Splunk Search

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

akriti
Explorer

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?

Labels (4)
Tags (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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 🙂

View solution in original post

akriti
Explorer

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=""

0 Karma

akriti
Explorer

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Explorer

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
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...