topic Re: Line Chart Overlay based on previous month and previous month-1 in Splunk Search

Line Chart Overlay based on previous month and previous month-1

ronaldtanhj — Tue, 20 Oct 2020 07:15:28 GMT

Hi,

I would like to compare the data of the previous month to the month before (i.e. now its October, so the default search will show September and August line chart in one panel).

In the edit search portion of the dashboard, I've set the Time Range to 'Tokens' and Earliest Token to "@mon' and Latest Token to '@mon-1'

May I know how I can include the chart for Earliest Token of "@mon-1' and Latest Token to '@mon-1' within the same search/panel?

Thanks.

Re: Line Chart Overlay based on previous month and previous month-1

thambisetty — Tue, 20 Oct 2020 07:30:49 GMT

you can use below earliest and latest to consider previous two months

index=test earliest=-2mon@mon latest=-0mon@mon

you can see below the earliest and latest will consider from starting of August to end of September.

Re: Line Chart Overlay based on previous month and previous month-1

ronaldtanhj — Tue, 20 Oct 2020 07:44:08 GMT

Hi,

Thanks for the reply.

Noted that the earliest and latest command is at the start of the spl.

However, I am trying to compare 2 months of data using a linechart. How can I go about overlaying the charts by setting the earliest and latest point? (i.e. Put August line chart over Sept line chart) - and later on calculating the different and putting it in a separate chart.

Thanks and apologies as I am quite new to Splunk.

Re: Line Chart Overlay based on previous month and previous month-1

ITWhisperer — Tue, 20 Oct 2020 08:11:49 GMT

Depending on your aggregation function (I have used the sum of counts from events)

-- base query | eval date_mday=printf("%02d",date_mday) | stats sum(count) as count by date_mday date_month | xyseries date_mday date_month count

You should set your time period to earliest=-2mon@mon and latest=@mon as previously suggested by @thambisetty

Re: Line Chart Overlay based on previous month and previous month-1

ronaldtanhj — Wed, 21 Oct 2020 13:05:25 GMT

Hi ITWhisperer,

Thanks for your reply.

I'm trying to understand the your reply but couldn't get the query to work.

I understand that by starting off with earliest=-2mon@mon and latest=-@mon, it bounds the timeframe to current month-1 and current month -2 (i.e now its October, so the data will be between start August and end September"

However, what I couldn't quite understand is the following:

| eval date_mday=printf("%02d",date_mday)

Question 1: May I know what the printf function does and how "%02d" divides the data into August and September?

Question 2: Shouldn't there be 2 of such an 'eval'

i.e

eval date_1=printf(xxxx, date_1) to set the boundary between start and end August

eval date_2=printf(xxxx, date_2) to set the boundary between start and end September.

Question 3: Lets say I want to specify a date for date_1 and date_2, how can i change the line? - I've tried eval date_2=printf("1/1/2020, date_2) while setting the timepicker to all time and no defining the 'earliest' and 'latest' at the start - but faced an error message.

Question 4: What does date_month represent in the case below? Does it not require an 'eval' like when date_mday was defined?

| stats sum(count) as count by date_mday date_month

for my case, I am performing a series of eval and stats sum by several fields in order to get the final value.

sample:

|stats latest(fieldX) as X, earliest(fieldY) as earliest Y by field1, field2
|eval A=X-Y
|stats sum(A) as B by field 2

I'm trying to plot this value B (a barchart that is based on field 2 which is unique) and compare the different between the previous month and the month before it.

Question 5:

| xyseries date_mday date_month count

With my requirement for this, should the plotting of chart be like:
|xyseries date_1 date_2 B

Apologies for the long list of question as I am new to Splunk.

Thanks in advance.

Re: Line Chart Overlay based on previous month and previous month-1

ITWhisperer — Wed, 21 Oct 2020 13:32:32 GMT

Answer 1: the printf puts a leading zero on date_mday so that the ordering is in numeric order otherwise you would end up with 1, 10, 11, 12, 13, ... 2, 20, 21 etc

Answer 2: no, you want the x-axis to be the same for both lines, which is why the printf above is done.

Answer 3: assuming you have an epoch date, you can format the way it is displayed either by eval or fieldformat date_1=strftime(date_1,"%d/%m/%Y")

Answer 4: date_month (like date_mday) is a field splunk has usually automatically added and in your case would contain one of two values, namely, august and september (which is what you asked for)

Answer 5: xyseries specifies which dimensions you want on the x-axis (day_mday), y-axis (count) and series (date_month) (it might have been better to call it xseriesy to make it easier to remember the order of the parameters!)

Re: Line Chart Overlay based on previous month and previous month-1

ronaldtanhj — Tue, 27 Oct 2020 07:36:47 GMT

Hi ITWhisperer,

Thanks for the detailed explanation.

However, I still couldn't get the output I want.

Is there another option to use the 'Chart Overlay' when creating a dashboard? Can i pass another value from another chart to this 'Overlay'?

Thanks.

Re: Line Chart Overlay based on previous month and previous month-1

ITWhisperer — Tue, 27 Oct 2020 07:51:25 GMT

Overlay works for one series but your requirement was for two and I don't think you can do that (although I might be wrong).

Re: Line Chart Overlay based on previous month and previous month-1

ronaldtanhj — Wed, 28 Oct 2020 06:27:53 GMT

Hi IT whisperer.

Thanks for your reply, now I understand how 'xyseries' works.

I believe what I intend to plot can be done using 'xyseries'

This is a sample of requirement on excel.

With that, I believe that using xyseries, the first input should be 'Item' to represent the item1-6 on the X axis.

Y Y should be Measurement(August or any month that's previously defined) and.

Is there anyway to define the month?

I've tried briefly but it couldn't work:

E.g. eval prev1mon = strftime( _time, "%M)

eval prev2mon =.....

|stats sum(X1) as measurement1 by item, prev1mon

|stats sum (X2) as measurement2 by item, prev2mon

|xyseries item measurement1 measurement2

Is this the correct approach? And how should this be changed to reach my intended output?

Thanks in advance!

Re: Line Chart Overlay based on previous month and previous month-1

ITWhisperer — Wed, 28 Oct 2020 08:48:37 GMT

The time for each event is already broken down for you, as I used in my original response date_mon is the month. Having said that, you appear to be trying to get the month as a number (btw, %M gives minutes, %m gives months). So try:

-- base query | eval date_mday=printf("%02d",date_mday) | eval month=strftime(_time,"%m") | stats sum(count) as count by date_mday month | xyseries date_mday month count

The stats command will reduce the fields passed down the pipe to only those output by the stats command so

|stats sum(X1) as measurement1 by item, prev1mon |stats sum (X2) as measurement2 by item, prev2mon

won't do what you are expecting as the second stats will have no X2, or prev2mon to work with so you will get no results.

In this instance, creating two different fields, prev1mon and prev2mon is not the right approach since you already have a field which identifies the month each event is from so you can use that in the by clause of the stats command (as I have shown).

Re: Line Chart Overlay based on previous month and previous month-1

ronaldtanhj — Fri, 30 Oct 2020 05:03:31 GMT

Hi,

I have tried based on your suggestion but it shows 'no results found'

I have also modified it based on my requirement (there are 2 key conditions and the final graph's xaxis should be shown in a discrete form based on condition1) understand.

-- base query

| eval date_mday=printf("%02d",date_mday)

| eval month=strftime(_time,"%m")

|stats count as ABC by date_mday, month, condition1, condition2

|eval EFG=round (ABC/1000,3)

|stats sum(EFG) as 'XYZ' by condition1, date_mday, month

|xyseries condition1, date_mday month

| stats sum(count) as count by date_mday month

| xyseries date_mday month count

Question 1:

Do note that there are some calculations in values in front, will this impact the use of xyseries?

Question 2:

Based on your reply

My requirement is for the X axis to be discrete unique assets and y axis to be a value for 2 different periods. Maybe I can provide u an analogy;

To compare the difference in expenses for different areas over August and september.

So for this case, the X axis will be transport , leisure and food - represented in 3 separate points.

The y axis will be the value of $$ which was previously calculated based on several conditions.

The graph will be shown in 2 lines, one red which represents August spendings and one yellow which represents Spetember spends.

Question 2: is your suggestion for this output? There may be some miscommunication.

Thanks!

Re: Line Chart Overlay based on previous month and previous month-1

ITWhisperer — Fri, 30 Oct 2020 07:14:02 GMT

If you are no longer plotting by day of the month but using discrete conditions you don't need to bother with date_mday (unless you are doing two separate plots).

You would normally use xyseries once to set up the table for the plot, it is fine to do calculations can be done before the xyseries (as above).

Re: Line Chart Overlay based on previous month and previous month-1

ronaldtanhj — Fri, 30 Oct 2020 08:28:04 GMT

Hi,

I'm now able to plot it. Thanks!

To add on to that, I currently have it in my dashboard and I am trying to add a picker picker option to set the 2 separate months (might not be in sequence, e.g January Vs June).

Since the earliest and latest is defined by -2mon@mon and -0mon@mon, I'm unable modify it.

I've tried using the token in the dashboard but was not able to display the correct month and legend. Any advise on this issue.

Thanks!

Re: Line Chart Overlay based on previous month and previous month-1

ITWhisperer — Fri, 30 Oct 2020 08:55:34 GMT

If your original question is solved, please mark the appropriate answer as the solution.

In answer to your further question, the way I would approach it is to use a time picker input to allow the user to select the required time span. I would use the tokens from this to provide earliest and latest to your main query. I would also use them in a multi-select dropdown to allow the user to pick months from the timeframe, which would also be used in your main query to filter the desired months. If you only want two months, you could do this with two (single select) dropdowns and if the user selects the same month for both, they would only get one line.