topic Re: Need help with doing math over 2 columns names which keep changing in Splunk Dev

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

jiaqya — Sat, 31 Mar 2018 21:19:47 GMT

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...

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

rsennett_splunk — Sat, 31 Mar 2018 22:48:49 GMT

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.

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

jiaqya — Sat, 31 Mar 2018 22:53:03 GMT

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...

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

jiaqya — Sat, 31 Mar 2018 22:58:16 GMT

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..

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

rsennett_splunk — Sat, 31 Mar 2018 23:01:58 GMT

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...

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

jiaqya — Sat, 31 Mar 2018 23:11:08 GMT

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..

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

rsennett_splunk — Sat, 31 Mar 2018 23:15:27 GMT

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.

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

somesoni2 — Sun, 01 Apr 2018 05:11:35 GMT

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

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

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

jiaqya — Sun, 01 Apr 2018 05:28:51 GMT

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

and thanks rsennett for the direction...

John.

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

rsennett_splunk — Sun, 01 Apr 2018 07:11:57 GMT

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.

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

jiaqya — Sun, 01 Apr 2018 07:14:33 GMT

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

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

jiaqya — Sun, 01 Apr 2018 07:14:45 GMT

And Thanks to you both again....

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

jiaqya — Sun, 01 Apr 2018 15:30:57 GMT

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]