topic Re: sparkline on the basis of column results in Dashboards & Visualizations

sparkline on the basis of column results

vranjith009 — Mon, 19 Oct 2015 08:31:39 GMT

Hi Splunkers,

I am new to Splunk. I was trying to create some dashboard with CSV files. I got some results as per the attached image and trying to add one sparkline column to the results. The sparkline should represent the three month data trend for each region (Y-axis will be ticket count and X-axis will be duration in months), and expecting the graph on the same raw on 4th column. Tried some options with sparkline, since "desired" keyword is variable, I hope I am unable to get the correct output. Can any one help on this? Thanks in advance.

Re: sparkline on the basis of column results

woodcock — Thu, 29 Oct 2015 20:38:14 GMT

Try this:

index=gh* sourcetype=csv  Country=india | eval epochtime=strptime(Resolve,"%d-%m-%Y") | eval desired=strftime(epochtime,"%b_%Y") |stats count(eval("Ticket No")) AS Total , count(eval(Level="Level1 - Tech." OR Level="Level 1 - Blackberry" OR Level="L1 Voice")) AS L1 sparkline(count(eval(Level="Level1 - Tech." OR Level="Level 1 - Blackberry" OR Level="L1 Voice")), 1d) AS "3 month daily Trend" BY desired Region

Re: sparkline on the basis of column results

woodcock — Sat, 31 Oct 2015 13:59:23 GMT

To do what you like, I think you need to use appendcols so try this:

Put your original OP search here | appendcols [index=gh* sourcetype=csv  Country=india | stats sparkline(count(eval(Level="Level1 - Tech." OR Level="Level 1 - Blackberry" OR Level="L1 Voice")), 1mon) AS "Monthly Trend" BY Region]

Re: sparkline on the basis of column results

vranjith009 — Wed, 04 Nov 2015 14:11:29 GMT

Hi Woodcock,

Thanks for the solution now I am getting sparkline in a better view as attached with full L1 tickets of data trend. If I am pointing through sparkline I am able to see the count variation . But dont know how sorting of L1 count is done by sparkline,? I was looking for the three L1 value graph on raw basis. I hope now i am getting atleast a graph with full ticket count. Is there any suggestion for sorting this total count graph on month order (like 07 , 08 ,09).

Re: sparkline on the basis of column results

woodcock — Thu, 05 Nov 2015 14:25:34 GMT

I made a mistake and should have used 1mon instead of 1m in my sparkline. I have corrected my answer so try again.

Re: sparkline on the basis of column results

vranjith009 — Thu, 12 Nov 2015 07:58:04 GMT

Hope now also I am getting same trend, Attaching the query output. Please suggest if sorting is possible.

Re: sparkline on the basis of column results

woodcock — Thu, 12 Nov 2015 18:05:25 GMT

So everything else is OK but you need to sort something, right? Describe the sorting that you need.

Re: sparkline on the basis of column results

vranjith009 — Fri, 13 Nov 2015 07:35:51 GMT

The columns ie 07_2015 , 08 and 09 provides me ticket counts of July , aug and sept months. Lets take an example of Ahmedabad location , each month count was 1121 , 970 ,1100. so I am expecting a graph as trend by using these three values ie for x axis Months (07 , 08 and 09) and Y axis ticket count. So my first location trend should start from comparatively high value (ie 1121 tickets) and then dip to low (ie 970 tickets) then again raise to 1100. Hope you understand.
In simple language, I need to draw a small graph on each raw by using three columns values.

Re: sparkline on the basis of column results

vranjith009 — Fri, 13 Nov 2015 07:37:42 GMT

i am not sure sparkline is the right tool for my requirement. Please guide

Re: sparkline on the basis of column results

woodcock — Sun, 15 Nov 2015 23:24:38 GMT

The problem is that you are running your search with All Time on the Timepicker and you only have data for the last 3 months. Try running the search for Last 3 Months and it should look the way you expect. The "problem" is that sparkline works like timechart and puts in "empty" (zero) values for each month.

Re: sparkline on the basis of column results

vranjith009 — Fri, 27 Nov 2015 12:40:48 GMT

Woodcock,

Thanks for the solution, I am not getting expected result in graph if I am adjusting to 3 months also. it seems that sparkline is working on time chart basis only.
Thanks for addressing my issue.