Im working on a search using a db query. The search and charting im trying to do is as follows:
| dbquery Utility "select count(value) as count, name as name, value as value from eavrecord where name not IN ('completed_at','all') group by value order by name, count(name) desc" | chart max(count) as count by value | sort by name, value
Now the sql returns 3 columns, a count of each "value" which is associated with one of 21 "names" For example the name "a" can have 5 different values "dog,cat,mouse, etc" and there is a row for each record of this.
What I would like to build is a stacked column chart so each bar is a name "A,B, etc" and the seperating colors in the chart are the counts of the values with the legend reflecting the value attributes.
I can't seem to get splunk to do this.
OK, my region just started a long weekend and nothing to do this morning. Good change to play with DB Connect and learn from Answers 🙂
I assume your SQL result is something similar to the following:
mysql> select count(value) as count, name, value from animal group by value,name order by name;
+-------+---------+---------+
| count | name | value |
+-------+---------+---------+
| 9 | name-1 | value-5 |
| 9 | name-1 | value-1 |
| 8 | name-1 | value-4 |
| 10 | name-1 | value-2 |
| 16 | name-1 | value-3 |
| 19 | name-10 | value-1 |
| 5 | name-10 | value-3 |
| 10 | name-10 | value-4 |
| 14 | name-10 | value-2 |
| 8 | name-10 | value-5 |
| 11 | name-11 | value-4 |
| 10 | name-11 | value-3 |
| 12 | name-11 | value-2 |
| 9 | name-11 | value-5 |
then, I just created a table with xyseries.
then, just click the result chart button just next to export link, change formatting option-> stack mode to stacked.
is this similar to what you are looking for?
Dude, thats awesome, I would like to end up sorting that chart by Name, then count in descending order from left to right, but I can probably figure that out easy enough. I was running into an issue with anything over 10 "values" being reported as "Other" the xyseries, fixed this.
Thank you much!
Shawn
OK, my region just started a long weekend and nothing to do this morning. Good change to play with DB Connect and learn from Answers 🙂
I assume your SQL result is something similar to the following:
mysql> select count(value) as count, name, value from animal group by value,name order by name;
+-------+---------+---------+
| count | name | value |
+-------+---------+---------+
| 9 | name-1 | value-5 |
| 9 | name-1 | value-1 |
| 8 | name-1 | value-4 |
| 10 | name-1 | value-2 |
| 16 | name-1 | value-3 |
| 19 | name-10 | value-1 |
| 5 | name-10 | value-3 |
| 10 | name-10 | value-4 |
| 14 | name-10 | value-2 |
| 8 | name-10 | value-5 |
| 11 | name-11 | value-4 |
| 10 | name-11 | value-3 |
| 12 | name-11 | value-2 |
| 9 | name-11 | value-5 |
then, I just created a table with xyseries.
then, just click the result chart button just next to export link, change formatting option-> stack mode to stacked.
is this similar to what you are looking for?
Seems that you are still stuck with something else.
If you are OK with this answer for now, please accept the answer by clicking the check mark.
And for other questions you may have, post them as a separate post.
Ive tried to edit the jscart.conf for the maxresults and its still not working. So while the Stacked bar charts are working, there is a lot of data not being rendered.
also I have a lot of data so im running into a truncation issue with the amount of returned data
Dude, thats awesome, I would like to end up sorting that chart by Name, then count in descending order from left to right, but I can probably figure that out easy enough. I was running into an issue with anything over 10 "values" being reported as "Other" the xyseries, fixed this.
Thank you much!
Shawn