Splunk Search

Perform Eval on results on monthly TimeChart results

atornes
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

somesoni2
SplunkTrust
SplunkTrust

what is the content of your lookup file?

0 Karma

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

0 Karma

chris
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

chris
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

chris
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="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

0 Karma

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...