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

[ronaldtanhj]

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. 

[ITWhisperer]

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 

[ronaldtanhj]

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.

[ITWhisperer]

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

[ronaldtanhj]

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.

[ITWhisperer]

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

[ronaldtanhj]

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!

[ITWhisperer]

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

[ronaldtanhj]

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 

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

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!

[ITWhisperer · Accepted solution]

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

-- base query
| eval month=strftime(_time,"%m")
| stats count as ABC by month condition1 condition2
| eval EFG=round(ABC/1000,3)
| stats sum(EFG) as XYZ by condition1 month
| xyseries condition1 month XYZ

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

[ronaldtanhj]

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!

[ITWhisperer]

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.

[thambisetty]

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.

[ronaldtanhj]

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.