topic Re: How to subtract static value from timechart? in Splunk Search

How to subtract static value from timechart?

skoelpin — Tue, 15 Mar 2016 14:06:58 GMT

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

Re: How to subtract static value from timechart?

lquinn — Tue, 15 Mar 2016 14:22:53 GMT

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]

Re: How to subtract static value from timechart?

javiergn — Tue, 15 Mar 2016 14:23:28 GMT

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]

Re: How to subtract static value from timechart?

skoelpin — Tue, 15 Mar 2016 14:35:31 GMT

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

Re: How to subtract static value from timechart?

lquinn — Tue, 15 Mar 2016 14:43:37 GMT

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?

Re: How to subtract static value from timechart?

somesoni2 — Tue, 15 Mar 2016 14:51:56 GMT

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

Re: How to subtract static value from timechart?

skoelpin — Tue, 15 Mar 2016 15:13:54 GMT

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

Re: How to subtract static value from timechart?

skoelpin — Tue, 15 Mar 2016 15:15:10 GMT

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

Re: How to subtract static value from timechart?

lquinn — Tue, 15 Mar 2016 15:24:22 GMT

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

Re: How to subtract static value from timechart?

skoelpin — Tue, 15 Mar 2016 15:27:43 GMT

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]

Re: How to subtract static value from timechart?

skoelpin — Tue, 15 Mar 2016 15:32:19 GMT

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

Re: How to subtract static value from timechart?

skoelpin — Tue, 15 Mar 2016 15:41:37 GMT

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!

Re: How to subtract static value from timechart?

lquinn — Tue, 15 Mar 2016 15:49:32 GMT

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.