topic Creating a graph with two columns of a table in Splunk Search

Creating a graph with two columns of a table

matansocher — Sun, 26 Mar 2017 22:17:40 GMT

Hello,

I need to make a simple line chart with the values of two columns of a table. the first column is the x axis and the second column is the y axis. At the meeting point of the two columns on the graph I need to represent with a dot, and to connect with a line between the dots.
Here is an example of the result I need:

In this example I need the opposite. The dates should be on the x axis and the values should be on the y axis.

Here is the code I am using:

| inputcsv MPSMilstonesCSV 
| sort TaskDeadline 
| fieldformat TaskDeadline = strftime(TaskDeadline, "%d/%m/%Y") 
| table Report_Milestone TaskDeadline

I don't know if i should use xyseries or timechart or a regular chart.

Thank you

Re: Creating a graph with two columns of a table

woodcock — Mon, 27 Mar 2017 00:08:06 GMT

Like this:

| inputcsv MPSMilstonesCSV
| sort TaskDeadline
| fieldformat TaskDeadline = strftime(TaskDeadline, "%d/%m/%Y")
| rename TaskDeadline AS _time
| table Report_Milestone TaskDeadline
| timechart avg(Report_Milestone)

Re: Creating a graph with two columns of a table

matansocher — Mon, 27 Mar 2017 12:32:47 GMT

the following code does not help me.
I did not understand why I should take the average in the timechart.
Also, the result doesn't give me the x axis as dates and the y axis as the names of the milestones(Report_Milestone).

Is there any other way to try this?

thank you

Re: Creating a graph with two columns of a table

somesoni2 — Mon, 27 Mar 2017 15:06:13 GMT

Assuming TaskDeadline is in epoch (based on usage of strftime in your question), try this

| inputcsv MPSMilstonesCSV 
 | sort TaskDeadline | eval _time=TaskDeadline 
 | fieldformat _time=strftime(_time, "%d/%m/%Y") 
 | table  _time Report_Milestone

Re: Creating a graph with two columns of a table

matansocher — Mon, 27 Mar 2017 21:22:53 GMT

The query that you are suggesting really gives me the table I need, but not the chart I need.

Is there a way to create a chart like that?

http://imgur.com/a/OlXHQ

thank you

Re: Creating a graph with two columns of a table

woodcock — Tue, 28 Mar 2017 00:45:59 GMT

That link has no image that I can see.

Re: Creating a graph with two columns of a table

matansocher — Tue, 28 Mar 2017 07:16:35 GMT

http://i67.tinypic.com/2195k06.png

I need the opposite - the y axis should be the values (milestones) and the x axis should be the dates.
thank you.

Re: Creating a graph with two columns of a table

woodcock — Wed, 29 Mar 2017 00:52:00 GMT

You are using the wrong terms. When you say X-axis in Splunkytown, everyone thinks that you are referring to the Visualizations tab and on that tab, you need the data formatted as I created in my answer.
I see now that when you say X-axis that you mean columns which is the term in Splunkytown that would cause us to think 'Statistics` tab. Now that I know what you are asking, check my latest answer (writing it now).

Re: Creating a graph with two columns of a table

woodcock — Wed, 29 Mar 2017 01:00:08 GMT

Like this:

| inputcsv MPSMilstonesCSV
| eval _time = strftime(TaskDeadline, "%d/%m/%Y")
| timechart span=1d avg(Report_Milestone)
| untable _time Report_Milestone count
| eval _time = strftime(_time, "%m/%d/%Y")
| xyseries Report_Milestone _time count

You might replace avg with first or sum but there has to be some kind of aggregating function when you use timechart (whether you "need one" or not). If you are sure that there is always 0 or 1 value for each Report_Milestone each day, then you can use first, but it is safest to use avg, just in case.