topic Re: ticket count every open months in Splunk Search

ticket count every open months

jonathan_yan5 — Thu, 27 Jul 2017 07:57:45 GMT

how can i count "several" tickets as "OPEN" every month including when it was created(create_date, mmddyyyy) to the month it was resolved(mmddyyyy), only given values are open date and resolve date. the ticket record is not found in between the months of created and resolved dates.

Chart visualization should be like a timechart

example:
ticket 1 : created(07202016), resolved(09222016) -> should be counted in July, august, september months as OPEN

Re: ticket count every open months

somesoni2 — Thu, 27 Jul 2017 12:24:43 GMT

Could you share some sample events?

Re: ticket count every open months

jonathan_yan5 — Thu, 27 Jul 2017 12:48:13 GMT

Sample:

TICKET NO. Created date Resolved date
Ticket-1 | 2017 / 06 | 2017 / 09
Ticket-2 | 2017 / 01 | 2017 / 04
Ticket-3 | 2017 / 02 | 2017 / 05
Ticket-4 | 2017 / 03 | 2017 / 06
Ticket-5 | 2017 / 05 | 2017 / 08

When i do statistics for the 5 tickets above the result is:

Ticket 1: will only be counted in June(06) and September(09), created and resolved dates respectively
Ticket 2: will only be counted in January(01) and Apr(04), created and resolved dates respectively
Ticket 3: will only be counted in Feb(02) and May(05), created and resolved dates respectively
Ticket 4: will only be counted in March(03) and Jun(06), created and resolved dates respectively
Ticket 5: will only be counted in May(05) and Aug(08), created and resolved dates respectively

For ticket 1 i want it to be counted as well in July and August because it was only resolved on Sept.
Same logic with others.

What i want my statistics to look like:

Year-month = Count
2017-January = 1

2017-February = 2
2017-March = 3
2017-April = 3
2017-may = 3
2017-June = 3
2017-July = 2
2017-August = 2
2017-September = 1
2017-October = 0
2017-November = 0
2017-December = 0

then doing the timechart

Re: ticket count every open months

niketn — Thu, 27 Jul 2017 18:35:18 GMT

Hi jonathan_yan5, I know the following does not give you what you asked but I feel Timeline Custom Visualization (https://splunkbase.splunk.com/app/3120/) is a better match for your use case as compared to Timechart.

Following is a run anywhere search which mocks that sample data you have provided and then converts Date to Epoch Time to calculate duration of ticket resolution.

  <row>
    <panel>
      <viz type="timeline_app.timeline">
        <search>
          <query>| makeresults
| eval data="Ticket-1 | 2017 / 06 | 2017 / 09;Ticket-2 | 2017 / 01 | 2017 / 04;Ticket-3 | 2017 / 02 | 2017 / 05;Ticket-4 | 2017 / 03 | 2017 / 06;Ticket-5 | 2017 / 05 | 2017 / 08"
| fields - _time
| makemv delim=";" data
| mvexpand data
| eval mvData=split(data,"|")
| eval TICKET_NO=mvindex(mvData,0)
| eval Created_date=strptime(replace(mvindex(mvData,1),"(\d{4}) \/ (\d{2})","01/\2/\1"),"%d/%m/%Y")
| eval Resolved_date=strptime(replace(mvindex(mvData,2),"(\d{4}) \/ (\d{2})","01/\2/\1"),"%d/%m/%Y")
| fields - data mvData
| eval _time= Created_date
| eval duration = (Resolved_date-Created_date) * 1000
| table _time TICKET_NO duration</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="timeline_app.timeline.axisTimeFormat">DAYS</option>
        <option name="timeline_app.timeline.colorMode">categorical</option>
        <option name="timeline_app.timeline.maxColor">#DA5C5C</option>
        <option name="timeline_app.timeline.minColor">#FFE8E8</option>
        <option name="timeline_app.timeline.numOfBins">6</option>
        <option name="timeline_app.timeline.tooltipTimeFormat">DAYS</option>
        <option name="timeline_app.timeline.useColors">0</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </viz>
    </panel>
  </row>

Re: ticket count every open months

DalJeanis — Thu, 27 Jul 2017 21:56:38 GMT

Here's another way of looking at it.

Your search that for each ticket gets created and resolved in mmddyyyy format

| rename COMMENT as "Change created and resolved into epoch time
| eval CreatedEpoch=strptime(created,"%m%d%Y") 
| eval ResolvedEpoch=strptime(resolved,"%m%d%Y")

| rename COMMENT as "Format created and resolved dates as/of the start of their month, add a month to the resolved date because thats when we reduce the count."
| eval openMonth=relative_time(CreatedEpoch,"@mon")
| eval closeMonth=relative_time(ResolvedEpoch,"+1mon@mon")

| rename COMMENT as "Release one +1 record for the open and one -1 for the close."
| eval mytimes=mvappend("openMonth=".openMonth, "closeMonth=".closeMonth)
| table mytimes
| mvexpand mytimes
| rex field=mytimes "^(?<TheMonth>[^=]+)=(?<TheTime>.+)$"
| rename TheTime as _time
| eval OpenCount=if(TheMonth="openMonth",1,-1)

| rename COMMENT as "Now we sum it all up."
| stats sum(OpenCount) as OpenCount by _time

You need to pull all events that are not yet closed at the beginning of the first month, or which have already been opened by the end of the last month, then kill all summary records before the beginning of the period you are interested in or after the end of the period. If you need any help with that logic, just let us know

Re: ticket count every open months

jonathan_yan5 — Mon, 31 Jul 2017 02:12:42 GMT

Hi Daljeanis,
thank you for your script.
i have tried using it but the year is 1970, also the months a ticket should be opened is not correct.

Please take note of this example:

"ticket AAA" is created june2017 and resolved Sept2017
therefore months june, july, august, and september should have an open ticket count of 1.
that makes it:

june = 1
july = 1
aug=1
sept = 1 ("ticket AAA" is open for some of the days in sept so i counted it also as open on the resolve date)