topic Re: Charting over column header in Splunk Search

Charting over column header

guilhem — Mon, 28 Sep 2020 12:35:26 GMT

Hi everyone!
I'm a new splunk user, and I have a quesion about chart formatting.

Here is the results of a search I've made:

v column Header1 Column Header2 Column Header3 Column Header 4 Column Header 5 ...
X 100 91.2 85.7 81.0 76.2 ...
Y 120 110 100 90 75.3 ...
Z 121 120 98 40 36 ...
.
.
.

EDIT: The column header names are results of a eval expressions, and not extracted from fields.

I would like to chart the value so that I get this result below:

[Whatever] X Y Z ...
columnHeader1 100 120 121
columnHeader2 91.2 110 120
columnHeader3 85.7 100 98 ...

. .
. .
. .

So that I can use a pretty graph to draw each series X, Y, Z according to the x-axis, which will be the name of the column headers

I've tried many things, I've tried to use the transpose, but it doesn't work weel because it gives me:

Column Row 1 Row 2 Row 3 ...
v X Y Z
columnHeader1 100 120 121
columnHeader2 91.2 110 120
columnHeader3 85.7 100 98 ...

. .
. .
. .

I saw that we can rename the column header, but I don't know the values of X, Y, Z beforehand, so I can't use rename, except if there is a way to rename by the value of a field, and then remove the first row, which I don't know how to do either.

Any help would be much appreciated!

EDIT 2: Thanks for answering so fast, here is the search I am running (on another data set/ fields, I have just transposed it to the _internal index):

And the results I get (values aren't real values):

           source                 %_progress10%             %_count2%

1 license_usage.log 0.3 2.0
2 metrics.log 0.4 0.9
3 splunkd.log 0.5 0.4
4 splunkd_access.log 0.6 0.7
5 web_access.log 0.7 0.6
6 web_service.log 1.9 0.1

What I would like to do is to have this:

[whatever] license_usage.log metrics.log .....
%progress10% 0.3 0.4
%_count2% 2.0 0.9

Many thanks

Re: Charting over column header

emiller42 — Tue, 09 Oct 2012 14:35:21 GMT

I would recommend the use of xyseries here. Try the following to see it in action:

index=_internal | stats count by host sourcetype | xyseries host sourcetype count

This will use the values for the host and sourcetype fields for your row and column headers, respectively. (The format is 'xyseries row_identifier column_identifier data_value') Try swapping host and sourcetype in the above example to see how the output changes.

This should accomplish what you're looking for nicely.

Re: Charting over column header

guilhem — Tue, 09 Oct 2012 14:56:03 GMT

Thanks for the answer, however I must precise that the column header names are not field contents, they are manually named by me. So except if there is a way to xyseries on a list of values instead of the content of a field, I cannot use it.

Or maybe I am missing something?

Re: Charting over column header

emiller42 — Tue, 09 Oct 2012 14:57:57 GMT

Can you provide some example data and the search you're using to get your current results?

Re: Charting over column header

guilhem — Tue, 09 Oct 2012 15:57:02 GMT

I've added an example in the first post using _internal index.

Re: Charting over column header

emiller42 — Tue, 09 Oct 2012 16:08:23 GMT

without changing what you've already provided, you could try
| untable source fields value | xyseries fields source value

I'm not getting useful results with the _internal search you posted, so I can't really test thoroughly. however, it works with a simpler example:

index=_internal | timechart count by sourcetype | untable _time series value | xyseries series _time value

Build it pipe by pipe to see how it's behaving at each step.

Re: Charting over column header

guilhem — Wed, 10 Oct 2012 07:20:48 GMT

Thank you very much!

Indeed it worked, but what I wasn't aware of is that the "fields" name is somewhat a keyword in the splunk language and you can use it as a global name for all your column header (not sure if I am clear, or if I have understood it correctly), the same happen for the "value" keyword.

Using " | untable source fields value", I was able to put the results in the right format, so I can chart it after with ease.

Re: Charting over column header

emiller42 — Wed, 10 Oct 2012 07:35:42 GMT

'Fields' and 'value' are arbitrary labels. Replace 'fields' and 'value' with 'peanut_butter' and 'jelly' in the example I gave and you will still get proper results.

Glad this worked for you!

Re: Charting over column header

guilhem — Wed, 10 Oct 2012 08:31:33 GMT

That's what I was suspecting, so in fact it always work, I was just confused on how the untable command operate, but now I'm clear, as I saw it in action.

Thanks!