Splunk Search
Highlighted

Perform Eval on results on monthly TimeChart results

Path Finder

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?

Tags (2)
0 Karma
Highlighted

Re: Perform Eval on results on monthly TimeChart results

Motivator

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.

0 Karma
Highlighted

Re: Perform Eval on results on monthly TimeChart results

Path Finder

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="mongoosedata" | 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.

0 Karma
Highlighted

Re: Perform Eval on results on monthly TimeChart results

Motivator

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="mongoosedata" | WHERE account="SAMPLEACCOUNT" | LOOKUP customerlist accountName AS account OUTPUT allotment AS monthAlloted | timechart span=mon first(monthAlloted) as monthAlloted sum(activities) AS consumedMtd BY account

0 Karma
Highlighted

Re: Perform Eval on results on monthly TimeChart results

Motivator

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

0 Karma
Highlighted

Re: Perform Eval on results on monthly TimeChart results

Path Finder

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="mongoosedata" | 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.

0 Karma
Highlighted

Re: Perform Eval on results on monthly TimeChart results

SplunkTrust
SplunkTrust

what is the content of your lookup file?

0 Karma