topic Re: How to define a X axis and submit different datasets in Splunk Search

How to define a X axis and submit different datasets

undercoverbroth — Mon, 30 May 2016 13:20:20 GMT

Hello I want to Display the CPU used from a Server depending on the users are working on that Server for several Servers.
It is working fine for the following query for one server:

index="User_Index" | timechart span=5m values(Servername) AS Severname_User | appendcols [ search index="CPU_Index" | timechart span=5m avg(Servername) as Servername_CPU] | chart avg(Servername_CPU) AS Servername_CPU over Servername_User

but I wanna do that for multiple server in one Chart; something like:

...| timechart span=5m avg(Severname_CPU) by Server ...

but instead over _time I would like to have that result depending on the users on the Server. So I Need a X axis with something like 1 to 10000 and different sets of datapoints depending on the servername like:

X=ServernameA_Users Y=ServernameA_CPU
X=ServernameB_Users Y=ServernameB_CPU
X=ServernameC_Users Y=ServernameC_CPU

and so on........

but be Aware that Servername% is a whole column, so it's not just a Data Point, it is a set of Points, or a total graph.

Is there a possibility to create a Chart like that?

Re: How to define a X axis and submit different datasets

woodcock — Mon, 30 May 2016 13:52:57 GMT

I do not think anyone will understand your situation without more explicit details. Create a minimal sample dataset and show us those events. The mockup the desired output and show us that. Maybe also include some simple math if necessary.

Re: How to define a X axis and submit different datasets

undercoverbroth — Mon, 30 May 2016 14:28:11 GMT

Basically I have different Servers with users working on them. I Monitor the users working on the Servers and the CPU used on the Servers.
I can create timecharts like: ....|timechart values(CPU) by host.... this returns the CPU used per Server over the selected time Frame.

Also I can create a timechart like: ......|timechart dc(Users) by host...... this Returns the distinct Count of users working on the Servers over the selected timeframe.

But I wanna have a Chart where I can see the used CPU over the amount of users working for each Server.
I created the Output I wanna have in a simple Excel sheet as you can see attached.

Is there a possibility to create something like that in splunk. At the end I would like to have a dasboard like that.

Scr

Re: How to define a X axis and submit different datasets

woodcock — Mon, 30 May 2016 16:19:47 GMT

Perfect. Try this:

|noop | rename Comment AS "First, mockup sample data"
|stats count AS Servername
| eval Servername="Server A,Server B,Server C" | makemv delim="," Servername | mvexpand Servername
| eval Users-CPU=case((Servername="Server A"), "200::3,263::5,458::7,490::8,753::12,952::15",
                      (Servername="Server B"), "143::2,275::5,336::6,425::7,655::10,723::12",
                      (Servername="Server C"), "253::5,345::6,480::8,556::9,898::14,1073::18")
| makemv delim="," Users-CPU | mvexpand Users-CPU | rex field=Users-CPU "^(?<Users>\d+)::(?<CPU>\d+)$" | fields - Users-CPU

You will have something else as your base search, of course. Now you have 2 followon searches.

This is for the visualization (select Line Chart as your visualization type):

| chart avg(CPU) BY Users Servername

This is for your chart (select Statistics Table as your visualization type):

| chart values(*) AS * BY Servername

Re: How to define a X axis and submit different datasets

undercoverbroth — Tue, 31 May 2016 10:05:10 GMT

oh great thanks, that is definetly in the direction I wanna go.
But how can I fill the different Severnames with data from a Splunk query. The example above was just to Show what I wanna do or see.
At the end of the day, I wanna have a query which fills Server A and Server B and so on with data out of my Indexes.

I am really new to splunk maybe my question is stupid but can I do something like:

..... | eval Users-CPU=case((Servername="Server A"), "[query1]::[query2]",
(Servername="Server B"), "[query3]::[query4]",
(Servername="Server C"), "[query3]::[query4]")......|.....

and of course the quer* will return several rows of data.....

Re: How to define a X axis and submit different datasets

woodcock — Tue, 31 May 2016 11:54:03 GMT

That is exactly why I said to show me a minimal set of your actual raw events. If you will provide me with that information (forget about Splunk entirely), we will be able to give you a search that does it.

Re: How to define a X axis and submit different datasets

undercoverbroth — Tue, 31 May 2016 15:43:27 GMT

Okay,

basicly I created some summary Indexes which I fill with data. The columns are the servernames and the values are depending on the summary index the value I wanna have.

for example I have a summary index for CPU used.
If I search:

index=summary_cpu | timechart values(ServernameA) values(ServernameB) values(ServernameC)

it gives me the CPU used on the serverA, ServerB and ServerC over time.

also I can do a query over another index:

index=summary_User | timechart values(ServernameA) values(ServernameB) values(ServernameC)

this Returns the users on each Server over time for the timeframe I set.

Now I wanna have a Chart on which I can see the CPU used over the Users working on that Server.
So that means I wanna have a Dashboard with the CPU used on the Y-axis and the Users on the X-axis for each Server in one Chart.

Also as I saw the Output from your Suggestion and I noticed that it just takes the data Points and does not create a linear x axis.
for example if I have three data Points like:

200::5
250::6
800::15

it creates three Points which are each takes exactly a third of the Chart.
Is it possible for splunk creates a X-axis from 0 to 1000 in steps of 100 like:

0 | 100 | 200 | 300.......|1000

and then just fill in the data for each Server?
I mean splunk does it automatically with the Y-axis so it's pretty much what splunk does with the Y-axis, I just wanna have that behavior also with the X-axis.

If you Need more Information just ask.

thanks in advance

Re: How to define a X axis and submit different datasets

woodcock — Fri, 03 Jun 2016 07:45:47 GMT

OK, then try this (you will have to add all of your non-relevant fields to the end of the fields - host search* command or these will show up as Servers):

index=summary_cpu OR index=summary_User | fields - host search*
| multireport
    [ stats values(*) AS * BY _time index | search index="summary_User" | fields - index | untable _time Servername Users ]
    [ stats values(*) AS * BY _time index | search index="summary_cpu"  | fields - index | untable _time Servername CPU ]
| stats values(*) AS * BY _time Servername

Now you have 2 followon searches, each of which will be tacked onto the end of the search above:

1: This is for the visualization (select Line Chart as your visualization type):

| chart avg(CPU) BY Users Servername

2: This is for your chart (select Statistics Table as your visualization type):

| chart values(*) AS * BY Servername

P.S. The stats values(*) AS * BY _time index | in the front of each multireport clause, although it does absolutely nothing, IS important and the solution will not work properly without it.

For anyone interested in faking the raw data for this, you can use this:

|noop | rename Comment AS "First, mockup sample data"
|stats count AS time
| eval time="1,2,3,4,5,6" | makemv delim="," time | mvexpand time
| eval Servername-CPU=case((time="1"), "Server A::3,Server B::2,Server C::5",
                           (time="2"), "Server A::5,Server B::5,Server C::6",
                           (time="3"), "Server A::7,Server B::6,Server C::8",
                           (time="4"), "Server A::8,Server B::7,Server C::9",
                           (time="5"), "Server A::12,Server B::10,Server C::14",
                           (time="6"), "Server A::15,Server B::12,Server C::18")
| makemv delim="," Servername-CPU | mvexpand Servername-CPU | rex field=Servername-CPU "^(?<Servername>.*?)::(?<CPU>\d+)$" | fields - Servername-CPU
| fields time Servername CPU
| xyseries time Servername CPU | eval index="summary_cpu"
| append [
|noop | rename Comment AS "First, mockup sample data"
|stats count AS time
| eval time="1,2,3,4,5,6" | makemv delim="," time | mvexpand time
| eval Servername-User=case((time="1"), "Server A::200,Server B::143,Server C::253",
                            (time="2"), "Server A::263,Server B::275,Server C::345",
                            (time="3"), "Server A::458,Server B::336,Server C::480",
                            (time="4"), "Server A::490,Server B::425,Server C::556",
                            (time="5"), "Server A::753,Server B::655,Server C::723",
                            (time="6"), "Server A::952,Server B::723,Server C::1073")
| makemv delim="," Servername-User | mvexpand Servername-User | rex field=Servername-User "^(?<Servername>.*?)::(?<User>\d+)$" | fields - Servername-User
| fields time Servername User
| xyseries time Servername User | eval index="summary_User"]