How to generate dynamic columns in a table that display the difference in data based on date?

[fnazar]

Hi,

I am new to splunk so bear with me please.

I am trying to display data by each day in a chart and then right at the end, display the difference of last 2 days in one column and then display difference from last Week,

My query currently looks like this

index=x1 sourcetype=x2 | eval minutes=seconds/60 | chart sum(minutes) over customer by date_mday useother=f limit=50 | table customer 16 17 | rename 16 as day16 | rename 17 as day17 | eval diff=day17-day16

This gives me Yesterday and day before, which is perfectly fine.

However, I would like to do all this dynamically. So if the query ran on the 20th, it would show the day for 18th and 19th. I cannot hard code every single day of the month.

The final bit is to show all previous days as well as showing the difference weekly.

Cheers

[vganjare]

Hi,

Following query should help you.

index=x1 sourcetype=x2 | eval currentTime = now() | eval custom_date_mday=if(_time > (currentTime-86400), "Today", if(_time > (currentTime - 2*86400), "Yesterday",  if(_time > (currentTime - 3*86400), "DayBeforeYesterday", "OLDER"))) | eval minutes=seconds/60 | chart sum(minutes) over customer by custom_date_mday useother=f limit=50 | eval diff = Today-Yesterday

Basically, classify your events into Today, Yesterday, DayBeforeYesterday and Older. 86400 is the number of sec in 24 hrs. You can change the logic for getting the Today, Yesterday values. Once you have the expected range of values, you can use those in the difference calculation.

Thanks !!

[vganjare]

Hi,

Is this working for you?

Thanks!!

[fnazar]

Is there any way to achieve this?

[somesoni2]

Try this runanywhere sample search to get the dynamic diff between last two days.

index=_internal host="ccfs-HF1" sourcetype=mongod | eval date_mday=tonumber(strftime(_time,"%Y%m%d"))| stats count by splunk_server date_mday | streamstats current=f window=1 last(date_mday) as p_day by splunk_server | eventstats max(date_mday) as max max(p_day) as pmax by splunk_server | appendpipe [| where date_mday=max OR date_mday=pmax | stats first(count)  as prev last(count) as curr values(date_mday) as date_mday by splunk_server delim="-" | nomv date_mday | eval count=curr-prev | table splunk_server date_mday count] | eval date_mday=if(like(date_mday,"%-%"),"Diff-".date_mday,date_mday) | chart max(count) over splunk_server by date_mday | table splunk_server Day* *

Can you explain what do you expect as part of weekly different?

[fnazar]

Thanks @somesoni2 for the reply.

Is that really the only solution? Can't we specify some variable in my search string instead of 16 and 17 over here? *table customer 16 17*

I am just trying to understand if it's a limitation in Splunk or not - because dates are being returned by date_mday from "*chart sum(minutes) over customer by date_mday useother=f limit=50*"?

Also - I don't get any results after modifying it to my requirements. Mind you, I am doing sum(minutes) and replacing it later in your proposed solution.

As for weekly - that would be a comparison of whole weeks' worth of data to the week before. Does that make sense?

[fnazar]

Just to add - I cannot get the proposed solution to work since I get "No Results Found".