topic Re: How to show open incidents by month in Splunk Search

How to show open incidents by month

t_splunk_d — Tue, 29 Sep 2020 14:11:53 GMT

I am trying to write a query to show number of open and closed incidents in a month. When I try the following in the timechart it does not bucket the time.

| timechart span=1mon dc(dv_incident) as Incident

This might be because the event time and the created time are different and timechart goes by _time.
So I assigned the created time to _time. | eval _time=sys_created_on but it does not work.

My search looks like: | eval _tme=sys_created_on | timechart span=1mon dc(dv_incident) as Incident
These are ServiceNow logs. How can I bucket the number of incidents opened in a particular month. Since the incident is updated it appears in other months and the count is duplicated.

Re: How to show open incidents by month

woodcock — Thu, 25 May 2017 11:40:31 GMT

Try sorting your data like this:

... | eval _time=sys_created_on | sort 0 - _time | timechart span=1mon dc(dv_incident)

Re: How to show open incidents by month

t_splunk_d — Tue, 29 Sep 2020 14:12:12 GMT

It does not work. Even though it sorts.
In below query I can see the results correctly
| eval _time=sys_created_on | sort 0 - _time | table _time dv_incident assignment_group_name

But when I execute the following it comes up with ZERO
| eval _time=sys_created_on | sort 0 - _time | timechart span=1mon dc(dv_incident) by assignment_group_name => returns zero for all the assignment_group_name

| eval _time=sys_created_on | sort 0 - _time | timechart span=1mon dc(dv_incident) => returns zero

One more observation I see even though I narrow down the search ( for one month) it brings all time.
The verbose mode show that the actual time in events is like this - NaN/NaN/aN NAN:NaN:NaN.000 AM

Is there a way to create summary indexing with the following query so that it stores the sys_created_on as _time and the timechart to count? I guess you are suggesting the same in the search but it does not work.

Bascially I want replace the _time with sys_created_on and do summary indexing. Will that write the events date will be written as sys_created_on?

Experts Need your suggestions/inputs and solution.

Re: How to show open incidents by month

woodcock — Thu, 25 May 2017 19:22:50 GMT

Show me one raw event and I am sure that I can fix it; the problem is that sys_created_on is a string, not an integer but I need to see the format of the string before I can help you covert it to an integer at which point all should work fine.

Re: How to show open incidents by month

t_splunk_d — Tue, 29 Sep 2020 14:12:20 GMT

Raw Event:
_time opened_at assignment_group_name dv_number dv_status closed_at
2016/03/28 7:06:55.000 PM 2016-03-28 19:06:55 IT Service Desk INC4752169 Open

2016/04/28 7:55:55.000 AM 2016-04-28 07:55:41 Server Group INC4752169 Open
2016/04/28 7:06:55.000 PM 2016-04-28 19:06:55 IT CC INC4752179 Open
2016/04/29 7:55:41.000 AM 2016-04-29 07:55:41 Server Group INC4752179 Open
2016/05/08 10:55:24.000 AM 2016-04-29 07:55:41 Server Group INC4752169 Closed 2016-05-08 10:55:23

I am looking for open incidents for Service Group
Month Assignment_Group count
March Service Group 0
April Service Group 2
May Service Group 0

Closed Incidents - ServiceGroup
March - 0
April - 0
May - 1

Following is my query which is not coming with correct counts:
| eval time =strptime(opened_at,"%Y-%m-%d %H:%M:%S")
| eval Time = strftime(time,"%Y/%m/%d %H:%M:%S")
|convert timeformat="%Y/%m/%d %H:%M:%S" mktime(Time) as epoch2
|eval _time = epoch2
|sort 0 - _time
| table _time dv_number opened_at assignment_group_name
|timechart span=1mon dc(dv_number) as inc by assignment_group_name

Re: How to show open incidents by month

woodcock — Thu, 25 May 2017 22:28:00 GMT

Like this:

| eval _time = strptime(opened_at,"%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| timechart span=1mon dc(dv_number) AS inc BY assignment_group_name

Re: How to show open incidents by month

rmarcum — Tue, 29 Sep 2020 14:12:28 GMT

Remember, at least based on my SN Splunk App experience:
1. opened_at may be GMT, and the rest of the dashboard (if that is the work product) is using _time which is probably LOCAL
2. indexing provides _time event timestamping via sys_updated_on probably giving _time as LOCAL
3. thus, we are collecting data for a time picker range of _time (update date snapshots)
4. considering all of this, I like to include a GMT offset for this "_time" solution provided above as follows; which should work for any time zone, and allows for daylight savings time (BTW, I have a habit of renaming SN fields immediately with an "_GMT" so I know which ones Splunk is indexing as GMT Vs LOCAL...which would be a function of your Admin's setup):

| rename COMMENT AS "the first part is the date conversion to epoch, but using sys_created_on"
| rename COMMENT AS "the second part calculates GMT offset using the fact that we index based on sys_updated_on so that field and _time gives us GMT offset (daylight or standard time)...being in California, I subtract the offset from the first part"
| eval _time=(strptime(sys_created_on_GMT,"%Y-%m-%d %H:%M:%S"))-((strptime(sys_updated_on_GMT,"%Y-%m-%d %H:%M:%S"))-(_time))

Re: How to show open incidents by month

t_splunk_d — Tue, 29 Sep 2020 14:15:26 GMT

@woodcock I modified the query and now seeing some numbers. The numbers are not tallying and I know the reason why, but could not translate it to SPL.

During a month if the ticket is opened and assigned to IT ServiceDesk and subsequently reassigned to some other assignment_group say IT DBA, then the query should not count it against IT ServiceDesk. But since the below query looks for only dv_state="Open" it is off. Is there a way to count only if the assignment_group_name=IT ServiceDesk and dv_state="Open" and it is the last occurring in the assignment. In otherwords it should be counted if it is assigned to other group. The stats last(assignment_group_name) does not work.

The following should not counted for IT ServiceDesk
_time opened_at assignment_group_name dv_number dv_status closed_at
2016/04/28 7:05:55.000 AM 2016-04-28 07:55:41 IT ServiceDesk INC4752169 Open
2016/04/28 7:06:55.000 PM 2016-04-28 07:55:41 IT CC INC4752169 Open

Following should be counted
_time opened_at assignment_group_name dv_number dv_status closed_at
2016/04/28 7:05:55.000 AM 2016-04-28 07:55:41 IT CC INC4752169 Open
2016/04/28 7:06:55.000 PM 2016-04-28 07:55:41 IT ServiceDesk INC4752169 Open

.... assignment_group_name="IT ServiceDesk" dv_state="Open"
| eval _time = strptime(opened_at,"%Y-%m-%d %H:%M:%S")
| sort 0 - _time |
| timechart span=1mon dc(dv_number) AS inc BY assignment_group_name
|stats last(assignment_group_name) <= does not work.

Re: How to show open incidents by month

t_splunk_d — Fri, 26 May 2017 00:49:55 GMT

@Marcum Thank you for your inputs, I haven't got to that stage yet!

Re: How to show open incidents by month

woodcock — Fri, 26 May 2017 01:33:56 GMT

So the search did not work?

Re: How to show open incidents by month

t_splunk_d — Tue, 29 Sep 2020 14:15:29 GMT

I replied to your message. But it did not appear yet. Again I am typing it. I am getting some numbers but it is not tallying and I know the reason why it is but could not translate it to SPL.

The following should NOT be counted against ServiceDesk. But the query is including it.

2016/04/28 7:06:55.000 AM 2016-04-28 07:55:41 ServiceDesk INC4752169 Open
2016/04/28 7:07:55.000 PM 2016-04-28 07:55:41 IT CC INC4752169 Open

The following should be counted against Service Desk but the query is not including it.
2016/04/28 7:06:55.000 AM 2016-04-28 07:55:41 ITCC INC4752179 Open
2016/04/28 7:06:55.000 AM 2016-04-28 07:55:41 ServiceDesk INC4752179 Open

I tried with the query:
index=.... assignment_group_name="ServiceDesk"
| eval _time=strptime(opened_at,"%Y-%m-%d %H:%M:%S")
|sort 0 - _time
|timechart span=1mon dc(dv_number) as inc by assignment_group_name
| stats latest(assignment_grup_name) dc(dv_number) <= does not work

Re: How to show open incidents by month

t_splunk_d — Fri, 26 May 2017 01:37:33 GMT

It did work but the numbers are not tallying. I posted an earlier comment but it did not appear. I have posted again.

Re: How to show open incidents by month

t_splunk_d — Fri, 26 May 2017 21:01:20 GMT

Any thoughts.

Re: How to show open incidents by month

woodcock — Fri, 26 May 2017 21:16:17 GMT

Finally you have given the detail that is required; try this:

| eval _time = strptime(opened_at,"%Y-%m-%d %H:%M:%S")
| sort 0 _time
| dedup dv_number
| timechart span=1mon dc(dv_number) AS inc BY assignment_group_name

Re: How to show open incidents by month

woodcock — Fri, 26 May 2017 21:16:38 GMT

See my new answer; we have it now.

Re: How to show open incidents by month

t_splunk_d — Sat, 27 May 2017 00:12:03 GMT

It is not coming up with the correct counts if Iexecute your query:

The following should NOT be counted against ServiceDesk. But the query is including it.

2016/04/28 7:06:55.000 AM 2016-04-28 07:55:41 ServiceDesk INC4752169 Open
2016/04/28 7:07:55.000 PM 2016-04-28 07:55:41 IT CC INC4752169 Open

Re: How to show open incidents by month

woodcock — Tue, 29 Sep 2020 14:15:45 GMT

OK, try this:

| eval _time = strptime(opened_at,"%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| eventstats min(_time) AS earliest_time BY dv_number
| where _time = earliest_time
| timechart span=1mon dc(dv_number) AS inc BY assignment_group_name

Note that this will count the 2nd set of events (which have identical times) once for each assignment_group_name, but the 1st set of events only for ServiceDesk because it is the only one that has the earliest opened_at time, which I think is the goal.

Re: How to show open incidents by month

woodcock — Sat, 27 May 2017 07:42:28 GMT

See my new answer.

Re: How to show open incidents by month

rmarcum — Tue, 29 Sep 2020 14:15:53 GMT

Might I suggest:

do not use dedup, but rather one of woodcock's favorites: stats values(*) AS * (prepending field values of key interest with _time), will keep any multivalue field in the correct order with time, i.e., the epoch number.
we can then use mvindex to obtain any value(s) of interest from the "mv array" that we desire...the last item in the array will be the closed ticket (or, if the ticket is still open at the end of the selected period that event will be the last record in the array...keep an eye on the "active" true/false field); the first item in the array will be the opened ticket (beware that some of these arrays will start with NULL until the assignment_group_name gets assigned (thus we must "walk" down the array until we reach the first non NULL value)
remember that _time can probably be used as a timechart scale for tickets opened, but a closed_at timechart scale must be used for closed tickets
with this foundation table in place, some cleaver additional coding will give you total control for reporting the changes in assignement groups (more often than most realize), contact_types (crazy, right?), asignees, etc. Plus, we can now calculate the time periods a ticket "dwells" within an assignement group or assignee.
finally, FCR calculations become "cake"

Might I now thank woodcock for his MANY posts that over time have given me the key pieces for this concept that makes the SN Splunk App untouchable by any tools reporting ServiceNow metrics...even some of the new stuff SN has added for Offprem cloud installations cannot touch this Splunk solution. The key in my mind is the 300 second snapshots in the Splunk solution. (NOT something to "dedup" away, but rather to leverage in an array generated via woodcock's stats trick). .giving us a GB a month footprint Vs 45GB a day with warehouse solutions trying to report on ServiceNow.

Regards.

Re: How to show open incidents by month

woodcock — Sun, 28 May 2017 00:42:06 GMT

To be fair, I stand on the shoulders of mighty Splunk contributors from my own past and while I have had my fair share of genuine innovations, stats values(*) AS * is not one of them. Sadly, the true origin may be lost but if anybody knows, do share!