topic Re: How to create cumulative chart separated by a field in Splunk Search

How to create cumulative chart separated by a field

matansocher — Fri, 17 Mar 2017 16:02:09 GMT

I need to create a chart, looking like the example I added.
the chart needs to show the cumulative number of tasks opened until the date, but separated by "Severity".,

the following code does give me a cumulative count by time of the tasks but not separating by the field "Severity".

index=clearquest "Root Project"=IPA_4* "Project Name"=IPA_4* ("Task Type"="Enhancement A*" OR "Task Type"=Defe* OR "Task Type"=Doc*) "Resolution"=* "Severity"=* "Task ID"=* 
| dedup "Task ID" 
| reverse
| timechart span=1w count("Task ID") AS sum_of_tasks_per_week 
| streamstats sum(sum_of_tasks_per_week) AS TI_accumulate

how do I create that separation that I need like the graph I added?

Re: How to create cumulative chart separated by a field

niketn — Fri, 17 Mar 2017 16:13:12 GMT

Add by Severity clause to your timechart query. Also instead of performing a dedup and then count("Task ID"), you can use distinct_count() or dc() aggregate function.

| timechart span=1w dc("Task ID") AS sum_of_tasks_per_week by Severity
| accum S0-Critical
| accum S1-Severe
| accum "S2-Medium Impact"
| accum "S3-Low Impact"
| accum S4-Unknown 
| accum Complete

[UPDATED]

Added query to generate accum block dynamically based on number of Severity values(example considers max 5 values).
Also added accum to original query provided to consolidate both answers.

There might be a neat way of performing dynamic accum without supplying the field name, but I am not aware of the same. Following is what I could come up with. If you know maximum number of fields your can write a query with transpose and tail to get the field names and then generate accum query block dynamically without having to know the actual field names. (Info of Max. no of fields will be required query will adjust dynamically according to no. of fields available for search duration.). Following example assumes maximum five fields.

  <search id="baseSeverity">
    <query> index=clearquest "Root Project"=IPA_4* "Project Name"=IPA_4* ("Task Type"="Enhancement A*" OR "Task Type"=Defe* OR "Task Type"=Doc*) "Resolution"=* "Severity"=* "Task ID"=* 
| stats count by Severity</query>
        <earliest>$selTime.earliest$</earliest>
        <latest>$selTime.latest$</latest>
    <sampleRatio>1</sampleRatio>
    <done>
      <condition match="$job.resultCount$&gt;0">
        <set token="CountofSeverity">$job.resultCount$</set>
      </condition>
    </done>
  </search>
  <search base="baseSeverity">
    <query>| transpose
    | tail 1</query>
    <preview>
      <condition match="$CountofSeverity$==&quot;5&quot;">
        <set token="queryAccumToken">| accum "$result.row 1$" | accum "$result.row 2$" | accum "$result.row 3$" | accum "$result.row 4$" | accum "$result.row 5$"</set>
      </condition>
      <condition match="$CountofSeverity$==&quot;4&quot;">
        <set token="queryAccumToken">| accum "$result.row 1$" | accum "$result.row 2$" | accum "$result.row 3$" | accum "$result.row 4$"</set>
      </condition>
      <condition match="$CountofSeverity$==&quot;3&quot;">
        <set token="queryAccumToken">| accum "$result.row 1$" | accum "$result.row 2$" | accum "$result.row 3$"</set>
      </condition>
      <condition match="$CountofSeverity$==&quot;2&quot;">
        <set token="queryAccumToken">| accum "$result.row 1$" | accum "$result.row 2$"</set>
      </condition>
      <condition match="$CountofSeverity$==&quot;1&quot;">
        <set token="queryAccumToken">| accum "$result.row 1$" </set>
      </condition>
      <condition>
        <set token="queryAccumToken"> </set>
      </condition>
    </preview>
  </search>

Finally after your base search with timechart just add $queryAccumToken$

| timechart span=1w dc("Task ID") AS sum_of_tasks_per_week by Severity $queryAccumToken$

Please let me know if this solves your issue. If it does please accept the answer.

Re: How to create cumulative chart separated by a field

matansocher — Fri, 17 Mar 2017 21:38:24 GMT

thanks for the answer.
that change does give me what I think I need in the statistic table, but not in the visualization tab:

The chart keeps coming back to zero every weak, and I need the count to be cumulative.

Re: How to create cumulative chart separated by a field

DalJeanis — Sat, 18 Mar 2017 03:13:33 GMT

Assuming that your records are currently just the daily statistics, try the | accum command before going into the chart.

Re: How to create cumulative chart separated by a field

niketn — Tue, 29 Sep 2020 13:18:23 GMT

You will have to mention the field names generated by timechart

Re: How to create cumulative chart separated by a field

matansocher — Sat, 18 Mar 2017 11:33:06 GMT

thank you.
that gave me the result I wanted.
If I had many values for the field severity, Is there a way not to write manually the values of the field?

Re: How to create cumulative chart separated by a field

niketn — Sat, 18 Mar 2017 16:44:03 GMT

@matansocher, I have updated my answer. Please accept the answer if this solves your issue.
There might be SPL command available to perform accum without having to know the field names. But I am not aware of. However, I have written a condition block which should work only if you know what will be maximum number of Severity field values (example is with 5 but you can extend for more). This will not require you to know the field names.

Re: How to create cumulative chart separated by a field

matansocher — Sat, 18 Mar 2017 17:50:22 GMT

@niketnilay thank you for your answer. I accepted the answer as solved, it did solve my answer.
I am new to Splunk and I would really appreciate if you could tell me a bit about the code you wrote (the code that starts with ).
In addition, is there a way to add the mentionung of every weak. now the report only shows 4 months in every year.
thanks again.

Re: How to create cumulative chart separated by a field

niketn — Sat, 18 Mar 2017 20:08:21 GMT

You are currently only voted the answers/comments. For accepting you need to click Accept link below my answer. Only after that this question will be marked as answered.

The reason for your timechart showing only 4 points for a year is because timeshart adjusts x axis for showing labels dynamically, it can not accommodate all weeks. However, you can try setting the timechart properties and rotate the x-axis label by 90 to see if it helps.

As far as the code is concerned let me try to break it down a bit...

search id="baseSeverity" creates a base search to be reused later in the code. It is called Post Processing in Splunk. I have called base search through search base="baseSeverity"

preview and done are search event handlers for accessing search related info and assign the same to tokens to be used in the form.

job.resultCount is built-in token which contains the number if results returned by a search. In our case it is the number of Severity values we get for specified time.

result.<fieldname> is another Splunk's search related token which allows you to access single value result of result from the first row(it can not read multiple row, which is the reason why I have performed tail 1 in my query with transpose)

Transpose reverses the axis for result because of which the Severity values become columns from rows and can be access through generic field names created by transpose which is "row 1", "row 2" and so on (by default).

You can try the following queries step by step to understand:

Step 1

index=clearquest "Root Project"=IPA_4* "Project Name"=IPA_4* ("Task Type"="Enhancement A*" OR "Task Type"=Defe* OR "Task Type"=Doc*) "Resolution"=* "Severity"=* "Task ID"=* 
| stats count by Severity

Step 2

| transpose

Step 3

| tail 1

Re: How to create cumulative chart separated by a field

DalJeanis — Mon, 20 Mar 2017 03:11:55 GMT

when you put any code into the web interface, be sure to mark it with the code button (the one with 101 010 on it) so that the web interface will not "eat" everything that looks like html.

Re: How to create cumulative chart separated by a field

niketn — Mon, 20 Mar 2017 11:51:57 GMT

@DalJeanis, if that was for me, I have added my code to updated answer above. In my previous comment I was just dissecting some of the code blocks which are in bold font for explanation.