Splunk Search

Charting over column header

Contributor

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):

index=_internal
| stats c(action) as count1 by source
| join type=outer [ search index=_internal action=touch | stats dc(component) as count2 by source]
| join type=outer [ search index=_internal action=cancel | stats dc(component) as progress1 by source]
| eval %_progress10%=round(progress1*100/count1,1)
| eval %_count2%=round(count2/count1,1)
| fillnull
| fields source %_progress10% %_count2%

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

0 Karma
1 Solution

Motivator

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.

View solution in original post

Motivator

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.

View solution in original post

Contributor

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!

0 Karma

Motivator

'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!

0 Karma

Contributor

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.

0 Karma

Motivator

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.

0 Karma

Contributor

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

0 Karma

Motivator

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

0 Karma

Contributor

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?

0 Karma