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?
what is the content of your lookup file?
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.
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.
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 ...
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
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.