topic Re: Trying to get month over month with detail. in Splunk Search

Trying to get month over month with detail.

tmblue — Tue, 29 Sep 2020 22:52:54 GMT

My current working and pretty one is this:

|eval Owner=ProductName | stats sum(Cost) as Total by TimePeriod, Owner | eval Total=round(Total, 2) |chart values(Total) over TimePeriod by Owner

The above works great for showing my exact spend broken out on product per day (I have that as week to date), but I'm running around in circles trying to figure out how to get the same data but by month.

Not quite what I want:

|eval Owner=ProductName | timechart span=1mon sum(Cost) as Monthly_Cost | eval Monthly_Cost=round(Monthly_Cost, 2)

So I would like the top one but per month, vs per day.. I am just struggling trying to figure it out. The detail of the first one is a real good visual on if something is going a bit nuts. And being able to see the same breakout per month, would allow to see trending not just by month but by ProductName.

thanks!

Re: Trying to get month over month with detail.

Vijeta — Sun, 20 Jan 2019 03:36:40 GMT

You can use bin span.

|eval Owner= ProductName| bin span= 1mon TimePeriod| stats  sum(Cost) as Total by Owner TimePeriod| eval Total=Total(round,2)| chart values(Total) over TimePeriod by Owner

Re: Trying to get month over month with detail.

tmblue — Sun, 20 Jan 2019 03:52:28 GMT

Thanks, but getting an error.

So I fixed the bin command I believe it needs to be used as bin _time span=1mon, when I fix that. It doesn't like my TimePeriod (which is a field (%F) in my data:

Error in 'bin' command: Invalid argument: 'TimePeriod'

Re: Trying to get month over month with detail.

Vijeta — Sun, 20 Jan 2019 03:55:17 GMT

Just make it bin span=1mon _time and do the stats and chart using _time instead of TimePeriod.

Re: Trying to get month over month with detail.

tmblue — Sun, 20 Jan 2019 04:11:41 GMT

You know I started changing things over to that, but said "well _time has nothing to do with my TimePeriod, so I bailed.. but.. okay. So closer 🙂

If I keep it as is just replacing the TimePeriod with _time, it has a hard time with my total change to 2 decimal points and crys

Error in 'eval' command: The 'total' function is unsupported or undefined.

Now I remove ; eval Total=Total(round,2) , which I really can't do, numbers are just too long with out it.

This looks right, other than the 2 decimals and the _time is including hours and offset,

|eval Owner=ProductName| bin span=1mon _time | stats sum(Cost) as Total by Owner _time | chart values(Total) over _time by Owner

Now i see time reference like 2018-10-01T00:00:00.000-7:00 That's a bit long, would like it just to be 2018-10-01 or (%F)

But it's closer than I've been able to get so far 🙂

Re: Trying to get month over month with detail.

Vijeta — Sun, 20 Jan 2019 04:34:42 GMT

It’s | eval Total= round(Total,2)

My bad I didn’t realize while typing .

Re: Trying to get month over month with detail.

tmblue — Tue, 29 Sep 2020 22:52:57 GMT

HAH a bit of work but I'm dancing, needed to add the eval_time to change the format and your correction on my 2 decimal point fixed that piece. So yes !!!!!

Thank you for working it through with me, def the right pointers!