topic Re: Perform Eval on results on monthly TimeChart results in Splunk Search

Perform Eval on results on monthly TimeChart results

atornes — Fri, 28 Feb 2014 22:03:29 GMT

I basically have a 3 step problem. #1 is figured out.

1) I've created a monthly timechart adding summing up a bunch of results throughout the month with a command like:

timechart span=mon sum(activities) AS totalActivities BY Group

2) I have a static allotment value which I'm pulling from a lookup table that I'd like to compare to the totalActivities for each month (e.g. overage=totalActivities-allotment).

3) I'd like the timechart to list that overage by month if >0.

How can I do this? I can't figure out how to #2, let alone #3. Any ideas?

Re: Perform Eval on results on monthly TimeChart results

chris — Fri, 28 Feb 2014 22:26:28 GMT

I used this search to test:

index=_internal | stats count by date_month,sourcetype| lookup test date_month | eval overage=count-allotment | where overage>0 | chart sum(count) as sum max(overage) as overage over date_month by sourcetype

The test lookup has the following content

date_month,allotment
january,1200
february,1000

Adopted to the info in your question I'd try the following:

index=xy data with activities and Group | stats sum(activities) as totalActivities by date_month,Group | lookup test date_month | eval overage=totalActivities-allotment | where overage>0 | chart max(totalActivities) as totalActivities  max(overage) as overage over date_month

I hope this helps.

Re: Perform Eval on results on monthly TimeChart results

atornes — Mon, 28 Sep 2020 16:01:27 GMT

That helps, BUT if I want to use timechart instead of stats, and not exclude overages<0, how could I do so.

Say if I want to do a trend line of their use and graph their allotted as a static horizontal line.

sourcetype="mongoose_data" | WHERE account="SAMPLEACCOUNT" | timechart span=mon sum(activities) AS consumedMtd BY account
| LOOKUP customer_list accountName AS account OUTPUT allotment AS monthAlloted | table _time consumedMtd monthAlloted

I can't get it to append the monthAlloted, basically the lookup doesn't work.

Re: Perform Eval on results on monthly TimeChart results

atornes — Mon, 28 Sep 2020 16:01:30 GMT

That helps, BUT if I want to use timechart instead of stats, and not exclude overages<0, how could I do so.

Say if I want to do a trend line of their use and graph their allotted as a static horizontal line.

I can't get it to append the monthAlloted, basically the lookup doesn't work.

Re: Perform Eval on results on monthly TimeChart results

somesoni2 — Mon, 03 Mar 2014 21:10:01 GMT

what is the content of your lookup file?

Re: Perform Eval on results on monthly TimeChart results

chris — Mon, 28 Sep 2020 16:03:18 GMT

You loose the account field after the timechart so the lookup has to go before the timechart and the you have to somehow make sure you do not loose the allotment information in the timechart. The allotment is allways the same so the first or last function should work:
sourcetype="mongoose_data" | WHERE account="SAMPLEACCOUNT" | LOOKUP customer_list accountName AS account OUTPUT allotment AS monthAlloted | timechart span=mon first(monthAlloted) as monthAlloted sum(activities) AS consumedMtd BY account

Re: Perform Eval on results on monthly TimeChart results

chris — Wed, 05 Mar 2014 22:44:43 GMT

Oh, and the reason I used the stats command was to fake a timechart using the date_month field. I thoght the allotment was different per month not per user ...