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.
After struggling for almost two weeks, I concede that I lack the skill in getting the results. Following is the query I have come up but it is not providing me the results for opened incidents
for assignment_group_name - device desk
.
Index=snow | deduplication dv_sys_id
| eval dv_opened_at=strptime(dv_opened_at, "%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| eval trackingStr=_time."__".assignment_group_name
| fields _time trackingStr
| stats values(trackingStr) as trackingStr values(number) as number
| eval track1= mvfilter(match(trackingStr,"Service Desk"))
| table track1
I am not able to figure out how I can find the incidents handled by service desk.
See my new followup answer here:
https://answers.splunk.com/answers/541810/how-to-show-open-incidents-by-month.html?childToView=54380...
@woodcock Thanks.I am able to get around the _time to limit the events. I will incorporate your suggestions. But the main issue is getting to the assignment_groups
. Your query of counting the assignment group
will not work out as assignment groups
change dynamically.
I have made some progress and almost there:
index=snow
| eval dv_opened_at=strptime(dv_opened_at, "%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| eval trackingStr=_time."__".assignment_group_name
| fields _time trackingStr
| stats values(trackingStr) as trackingStr by number
| sort 0 -number
| eval track1= if(match(trackingStr,"Service Desk"1,0)
| where trackstr = 1
| eval last = mvindex(trackingSTR, -1)
|eval final = if(match(last,"Service Desk),1,0)
| where final = 1
The above shows the last assignment to Service Desk
. I need your expert advice if the search query can be fine tuned. I will still need to validate if my numbers match with SN.
@rmarcum - Is your query similar to this to calculate the opened incidents?
It cannot be. You do fields _time trackingStr
and then you do BY number
and because you dropped the number
field, your search will have no events from that point on. You really need to get your story straight on this. Every time you add a requirement, I have addressed it only to see you add another requirement. Now you have several different answers that have evolved way into the weeds and still nothing that works. Take a step back and add a comment to your Question that completely restated your actual requirements. Ideally this will include both sample events and a mockup of the desired outcome (with sample events that are well-constructed to demonstrate the shortfalls of the some of the partial solutions). At this point, I cannot really continue without a full reset.
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.
With your help I am able to restrict the results for a given month, I am still struggling to find a solution for the assignment_group_name. If I am search for tickets for ServiceDesk how can I accomplish. I am using transaction to group the incident by sys_id and get the last assigned group using mvlist.
.... Assignment_group_name="ServiceDesk" | transaction sys_id mvlist=assignment_group_name | eval last_assign = mvindex(assignment_group_name, 0) .....
This does not take in to account a scenario where service desk is assigned intially but reassigned to some other group. Is there a way to iterate through and eliminate the results and take only results which ends with the "ServiceDesk"?
Actually, I do not use transaction, but instead leverage the stats output. Meaning, I believe your focus should NOT just be finding the last CLOSED ticket event, but rather an approach of finding the complete Ticket Life Cycle, including tickets which are still OPEN--i.e., before deciding on your reporting output. It is kind of amazing once you see it. You can see this looking into the ServiceNow GUI for an incident ticket, but requires a lot of reading the many threads just to understand what happened for one ticket. Here you will see them all at once! Of course, you will want to go to SN to verify your results at some point using some sampling schedule.
Soooo, now deeper into my secret sauce (-: .... and, Please, anyone jump in to make this even better....
Before the 'stats trick', which I assumed you used "BY 'Ticket Number' or sys_id" to now have only one event per ticket, insert a concatenated string starting with _time:
| eval trackingSTR=_time."__".assignment_group_name."__".<any_other_fields _desired_for_timeline_tracking>
This will keep the timeline in order in the resulting tracking array field after stats. A double underscore is used as a unique value to allow for single underscores within field values that can interfere with later rex operations, when needed.
Now, you can use 'match' (which works with multivalue fields) with this new field to keep only tickets 'touched' by ServiceDesk at any time within the ticket life cycle....key words for this analysis that no prior reporting has ever done...Ticket Life Cycle. We need to stop thinking LAST assignment group, which is always the "state value" in the ServiceNow database and always reported in the past, resulting in an incomplete view of the process...love the Splunk App!!
If you add 'duration' (business or calendar) to the tracking string, you can also determine how long each assignment group has the ticket. You will be amazed at how many times these tickets are "passed around"...including "to" service desk from another assignment group who was the initial assignee...which it appears you are considering. And, when you see this time line array, I doubt you will want to only report on "Last Assignment Group".
Finally, with mvindex (0 & -1) you can easily find the first and last assignment groups. And, the others "involved" can be reported too. BTW, remember that FCR is calculated on First Assignment Group, not Last. BTW2, I use calendar time durations since the help desk is 24/7...and it is easier to calculate. ((-:
Thanks for you detailed reply. I have a basic question.
index=x assignment_group_name="ServiceDesk" | ...| stas ...
Will this above query to restrict only servicedesk eliminate other assignments groups?. I am trying to restrict the results so that I can work with smaller dataset. For example, if a ticket is assigned to some x group and then assigned to ServiceDesk will the above query catch it. Alternatively, if the ticket is assigned to ServiceDesk and then reassigned to some other group will it also be in the results? I want to be sure if my base query for the search is alright before proceeding on your excellent logic/approach.
The answer to both of your questions is "yes". Actually, a good idea to get a quick count.
Thus, coming out of the first pipe will still often be multiple events for each ticket (i.e., many events = one ticket). Thus, I guess you could also add "active=false" before the first pipe to find only the events for tickets closed by ServiceDesk, and there should only be one event per ticket for these. Then, from our previous discussions, you could use your filter to removed those tickets which have been closed that were opened during the previous period.
A test to prove this is:
index=snow sourcetype=snow:incident assignment_group_name="<your_choice" active="false" | stats count by assignment_group_name, active, number | sort - count
There should be no counts greater than 1. If there are, run this to see why.
index=snow sourcetype=snow:incident assignment_group_name="your_choice" active="false" number="ticket_number_with_count_greater_than_1" | table _time number assignment_group_name active *
In my case I have to watch for automated processes that happen and update the work notes more than 300 seconds after the ticket is closed. Knowing this, we can filter out any such events, or if VERY confident we know why, just dedup OR go directly to stats trick.
This is based on the Splunk App for SN polling the ServiceNow API frequently (default is every 300 seconds looking for changes in sys_updated_on.
Also, I should mention that things CAN change in the 300 second period and be MISSED by Splunk--e.g., changes in assignment groups. I am investigating the statistical significance of this. If it is an issue, since the Splunk SNOW index is such a small footprint, and incidents a VERY small part of that, I may go to 60 second hits on the SN API for snow:incident sourcetype and further test the impact on Splunk. Admin suggests "none".
While I am still validating the numbers, when i execute the query it brings up records of previous months, even though i specifically choose (for example from 01/04/2017 to 30/04/2017) it lists events from March 2017 probably because opened_at is in March 2017. Is there a way to eliminate those?
When I execute the query for Year to date the number for April changes. So it is not consistently counting the monthly figures for incidents opened in a month.
Also an incident can be opened at previous month and assigned in current month. For example,
2016/03/28 17:06:55.000 AM 2016-03-28 07:55:41 ServiceDesk INC4752169 Open
2016/04/28 7:07:55.000 PM 2016-04-28 07:55:41 IT CC INC4752169 Assigned
This should not be counted against Service Desk when i run the April period.
I think the scenarios explained by @rmarcum below (1 and 2) is not handled in the query. I tried the below query, which has lot of performance hits plus does not correctly handle the 1 and 2 of @rmarcum which I am looking for.
Please help!
index=....
| transaction dv_number mvlist=assignment_group_name maxspan=30d keepevicted=true
|eval last_state_change = mvindex(assignment_group_name, -1)
| eval dv_opened_at = strptime(dv_opened_at,"%Y-%m-%d %H:%M:%S")
|eval _time=coalesce(dv_opened_at, _time)
|sort 0 - _time
|table _time dv_number assignment_group_name
|rename last_state_change as assignment_group_name
| search assignment_group_name="ServiceDesk"
|stats values(*) as * _time
| table _time dv_number assignment_group_name
|timechart span=1mon dc(dv_number) AS inc BY assignment_group_name limit=20 useother=f
OK, here is another adjustment to limit events to the Timepicker's
range:
| eval _time = strptime(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 dv_number
| where _time = earliest_time
| timechart span=1mon dc(dv_number) AS inc BY assignment_group_name
That should do it.
Some ideas to leverage:
| rename COMMENT AS "### add FLAG for tickets opened before the start of metric time period ###"
| eval OpenPrePeriod=if(opened_at_GMT<StartOfTimePeriod_GMT,1,0)
Then at the appropriate time in the query for a particular dashboard panel (some panels should include preperiod, and some should not) you would leverage the "flag" with:
OpenPrePeriod=1
or
OpenPrePeriod=0
Use the timepicker to get StartOfTimePeriod_GMT which is the start of the time range selected from _time (i.e., _time = sys_updated_on_LOCAL set during indexing on most SN App installations)
Do the above after doing the | stats values(*) AS *
at which time the opened_at field will not be multi-value because it never changes. The sys_created_on is a good field also.
@rmarcum It seems you have logic nailed down. Will you be able to share your query for Opened Incidents you have with you?
| stats count(eval(OpenPrePeriod=1)) AS "Tickets Opened From Previous Period" count(eval(OpenPrePeriod=0)) AS "Tickets Opened This Period"
Might I suggest:
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.
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!
I stand corrected. I do follow most of the top 10 in the Splunk Leader Board, and I am sure I cannot accurately recall which key jewel I have obtained from which "expert". And, I am sure my work with Splunk over the past 10+ years includes many tips from each and every one.
https://answers.splunk.com/users/index.html?all=1&sort=karma
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
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
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