Need help with doing math over 2 columns names which keep changing

[jiaqya]

i have 2 columns which keep changing their names. but the first few parts remain same.
ex:
column1 : Total: ABC , the ABC part keeps changing
column2 : Yes : ABC, the ABC part keeps changing.

both these columns have numbers and i want to multiply both these values to a new column.
|eval Comp= Total: ABC * Yes: ABC

how do i do this, while retaining the original column names..
i just need the Comp to be calculated...

[rsennett_splunk · Accepted solution]

I fully admit to being no expert in the use of FOREACH, but this is what you are looking for.
What I am not totally clear on is whether ABC represents a matching suffix or if they could be Total:ABC and Yes:DEF

The code below presumes they are the same

...|foreach Total:* [eval "new_<<MATCHSTR>>" = '<<FIELD>>' + 'Yes:<<MATCHSTR>>']

The brackets are a sub search... so this says:
make a new field that is called new_sameasprefix and contains the sum of the field outside the sub search and this other field with the same prefix FOR EACH time a field looks like this: Total:*

Worth taking a look at the doc for foreach and seeing if that solves your problem. Read through the Optional argument section to absorb the concept of <<MATCHSTR>> and how they are applied to the wild card.

[somesoni2]

Since the field names contains special characters, include appropriate quotes around it.

...|foreach Total:* [eval "new_<<MATCHSTR>>" = '<<FIELD>>' + 'Yes:<<MATCHSTR>>']

[jiaqya]

Thanks Somesoni, that is what i was missing previously, that worked perfectly now..

and thanks rsennett for the direction...

John.

[rsennett_splunk]

oh gosh. sorry. upvoted somesoni2's comment. Funny, I just answered another question with that very fact... that special characters will usually need to be wrapped in appropriate quotes! Answer amended for posterity.

[jiaqya]

Not a issue, i glad my issue is resolved now.. 🙂

[jiaqya]

And Thanks to you both again....

[jiaqya]

For more info that might help others, i was trying to work with geostats (that is where the : special character was coming into the column name for each site ) and was wanting to have the compliance calculation done on it. below is the exact query that is working for me now..

|geostats maxzoomlevel=18 globallimit=0 latfield=Latitude longfield=Longitude avg(g1) as g1 avg(a1) as a1 avg(a2) as a2 avg(c1) as c1 sum(Total) as Total sum(Yes) as Yes  ""
|foreach Total* [eval "Compliance<<FIELD>>" = ('Yes<<MATCHSTR>>'/'<<MATCHSTR>>')*100]

[jiaqya]

This does not look to be working for me.
the part ABC DEF , could keep changing...

i would also be ok, if i could rename all the Total:* as Total & Yes:* as Yes , without the * , that allows me to do eval for the calculation..
is this possible..

[rsennett_splunk]

Is the field literally 'Total:BLAH' 'Yes:BLAH' or 'Total:FOO' 'Yes:FOO' or 'Total:BAR' 'Yes:BAR' ?

because then it should work... that's what the wild cards are for.

You may want to explore the data and test that the pattern of field NAMES is what you think they are...
just to be sure...

[jiaqya]

Hi, here is the command i ran , but i get an error , i just tried to remove the : to make it even more clear.

: AGUA is the dynamic entry

| foreach Total* [eval Comp<> = <> + Yes<>]
Failed to parse templatized search for field 'Total: AGUA'

i am expecting the answer to be Comp: AGUA=sum of Total* and Yes* , is that right..

[rsennett_splunk]

take a look at the doc. it's confusing... but the <<FIELD>> and <<MATCHSTR>> is literal... not a place holder. so:

...|foreach Total:* [eval <<MATCHSTR>> = <<FIELD>> + Yes:<<MATCHSTR>>]

so when Total:AGUA and Yes:AGUA occur, for each Total:AGUA you get a new field called AGUA that is the sum of the original field, Total:AGUA and the other one... Yes:AGUA.

[jiaqya]

Total: ABC and Yes: ABC , these 2 would always match as these are coming from a previous "by text" query.
if the ABC changes to DEF, then it would be Total: DEF & Yes: DEF

yes, these are the same for a single row.
let me try and see...