Splunk Search

How to subtract static value from timechart?

skoelpin
SplunkTrust
SplunkTrust

I have a timechart which tracks tax calls per half hour. We have monitoring set up which will hit our web service every 1 minute, there is no way to distinguish between a customer or monitor tax call in that index, it only shows the method and tax call. So I need to subtract 30 from each time slot so I can get rid of the monitoring from our results.

I have an extracted field called Tax which is the name of our web service name (CalculateTax and LookupTax).

Example

BEFORE

_time                               CalculateTax     LookUpTax
2016-03-14 00:00:00                 143           118
2016-03-14 00:30:00                 151           111
2016-03-14 01:00:00                 103            96
2016-03-14 01:30:00                    125               98

AFTER

_time                               CalculateTax     LookUpTax
    2016-03-14 00:00:00                 113           88
    2016-03-14 00:30:00                 121           81
    2016-03-14 01:00:00                 73             66
    2016-03-14 01:30:00                    95               68

Here's my current query

index=vertex7-access   Tax="*" | timechart  count by Tax
Tags (3)
0 Karma
1 Solution

lquinn
Contributor

You could just add a couple of eval's on the end of your search ...

index=vertex7-access Tax=* | timechart count by Tax | eval CalculateTax = CalculateTax - 30 | eval LookUpTax = LookupTax - 30

Alternatively you could use the foreach command to recalculate both fields at once ...

index=vertex7-access Tax=* | timechart count by Tax | foreach * [eval <<FIELD>>=<<FIELD>>-30]

View solution in original post

javiergn
SplunkTrust
SplunkTrust

Unless I didn't understand your question I would use foreach:

 index=vertex7-access   Tax="*" 
| timechart  count by Tax
| foreach *Tax [eval <<FIELD>>='<<FIELD>>' - 30]
0 Karma

skoelpin
SplunkTrust
SplunkTrust

Thanks for your input! The issue I'm having with this is that I made an extracted field called Tax which extracts both CalculateTax and LookUpTax. So that Tax field will have 2 values.. I tried taking this approach below but it did not work. CalculateTax70 and LookUpTaxAreas70 is the name of the web service calls which make up the field Tax

index=vertex7-access   Tax="*"  | timechart count by Tax | foreach *Tax [eval CalculateTax = CalculateTax70 - 30, LookUpTax = LookUpTaxAreas70 - 30]
0 Karma

lquinn
Contributor

You could just add a couple of eval's on the end of your search ...

index=vertex7-access Tax=* | timechart count by Tax | eval CalculateTax = CalculateTax - 30 | eval LookUpTax = LookupTax - 30

Alternatively you could use the foreach command to recalculate both fields at once ...

index=vertex7-access Tax=* | timechart count by Tax | foreach * [eval <<FIELD>>=<<FIELD>>-30]

skoelpin
SplunkTrust
SplunkTrust

This is very close to what I'm looking for. When I used your first search (The evals) it produced 4 columns.. It had my 2 original tax columns then it had the 2 new columns which were defined int he eval. How do I get rid of the 2 old columns and only keep the 2 new eval columns?

Here's my new search (This is producing an error now)

index=vertex7-access  Tax="*"  | eval CalculateTax = CalculateTax - 30 | eval LookUpTax = LookupTaxAreas - 30 | timechart count by CalculateTax, LookUpTax
0 Karma

lquinn
Contributor

The evals must go after the timechart command - does it still give you two extra columns then? It shouldn't as you are just replacing the two fields that have already been created - CalculateTax and LookupTax. What were the four columns?

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Yes, this search gave me 4 columns (2 old and 2 new)

index=vertex7-access Tax=* | timechart count by Tax | eval CalculateTax = CalculateTax - 30 | eval LookUpTax = LookupTax - 30
0 Karma

lquinn
Contributor

Ok, can you tell me the column names? What did the foreach command give you?

0 Karma

skoelpin
SplunkTrust
SplunkTrust

The column names are 'CalculateTax', 'CalculateTax70', LookUpTax', and 'LookupTaxAreas70'.. The new columns which are correctly subtracting 30 are called 'CalculateTax' and 'LookUpTax'.. So I need to get rid of the other 2 columns

The 2 values which make up the extracted 'Tax' field are called 'CalculateTax70' and 'LookupTaxAreas70'

Here's my query

index=vertex7-access Tax="*"  | timechart count by Tax | eval CalculateTax = CalculateTax70 - 30 | eval LookUpTax = LookupTaxAreas70 - 30
0 Karma

lquinn
Contributor

OK well that makes sense why you are getting 4 columns, you are calling the new fields something different. You can just add this to the end of your search...

| fields - CalculateTax70, LookupTaxAreas70

However as somesoni2 said, you are probably better using foreach instead. You don't need to substitute the <> part, just leave it as is.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

I went ahead and tacked on | fields - CalculateTax70, LookupTaxAreas70 at the end of my search and it successfully removed the old columns that I don't want.

I know this is an ugly query and there are better ways of doing it but I don't have a lot of time to doll it up and need a quick fix. Thanks for your help!

0 Karma

somesoni2
Revered Legend

The foreach would be better approach as it eliminates hard-coding of column names.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

I do agree on not hardcoding my values, but in this case, these web service calls will not change for a long time so it should be safe. I'll take a foreach approach and see if that works

0 Karma
Get Updates on the Splunk Community!

Announcing General Availability of Splunk Incident Intelligence!

Digital transformation is real! Across industries, companies big and small are going through rapid digital ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...