topic Re: How can I complete many (70) similar transformations without doing it manually? in Dashboards & Visualizations

How can I complete many (70) similar transformations without doing it manually?

iKate — Mon, 14 Aug 2017 14:58:32 GMT

Hi guys,

1) I've got a table like this:

month       A1     A2    A3   ....  A70
2017.08     x1     y1    z1
2017.07     x2     y2    z2
2017.06     x3     y3    z3
...

2) Then it is transformed with some logic and formatting like:

A1=if(month>"2017.08", A1."(".A1/A1_plan.")", A1)
A2=if(month>"2017.08", A2."(".A2/A2_plan.")", A2)
...
A70

3) Then it is transposed and resulting table looks like:

     ... 2017.06    2017.07     2017.08 .....
A1       x11        x22         x33
A2       y11        y22         y33
A3       z11        z22         z33

How can I perform transformations from step 2 without making 70 evaluations? If I do it in one query Splunk Web crashes.. Evaluations are almost the same for different An.

If there is a parameter for a column names like $column_name$, this can solve the issue with just one string like:

$column_name$=if(month>"2017.08", $column_name$."(".$column_name$/$column_name$_plan.")", $column_name$)

Is there such token or how else the problem can be solved?

Thanks in advance!
Katerina

Re: How can I complete many (70) similar transformations without doing it manually?

sbbadri — Mon, 14 Aug 2017 15:07:05 GMT

may be try with foreach,

| foreach A* [eval <<FIELD>> = if(month>"2017.08", <<FIELD>>."(".<<FIELD>>/<<FIELD>>_plan.")", <<FIELD>>)

Re: How can I complete many (70) similar transformations without doing it manually?

somesoni2 — Mon, 14 Aug 2017 15:09:32 GMT

Try the foreach command.

your search generating output as 1)
| foreach A* [| eval  "<<FIELD>>"=if(month>"2017.08", '<<FIELD>>'."(".'<<FIELD>>'/'<<FIELD>>_plan'.")", A1)]

Do you have fields A1_plan in your result set? If yes, then try this

your search generating output as 1)
| foreach A* [| eval  "<<FIELD>>"=if(month>"2017.08" AND NOT (like("<<FIELD>>","%_plan"), '<<FIELD>>'."(".'<<FIELD>>'/'<<FIELD>>_plan'.")", A1)]

Re: How can I complete many (70) similar transformations without doing it manually?

richgalloway — Mon, 14 Aug 2017 15:16:36 GMT

I think the foreach command will help. Try this as a start.

... | foreach A* [eval <<FIELD>>=if(month>"2017.08", <<FIELD>>."(".<<FIELD>>/<<FIELD>>_plan.")", <<FIELD>>)]

Re: How can I complete many (70) similar transformations without doing it manually?

iKate — Mon, 14 Aug 2017 15:28:17 GMT

Awesome! It does exactly what I need, thanks! Please post it as an answer and I'll mark it as accepted.

Re: How can I complete many (70) similar transformations without doing it manually?

sbbadri — Mon, 14 Aug 2017 15:31:01 GMT

Cool... Converted to answer

Re: How can I complete many (70) similar transformations without doing it manually?

iKate — Mon, 14 Aug 2017 15:57:10 GMT

Thanks, it works but @sbbadri was the first to answer)

Re: How can I complete many (70) similar transformations without doing it manually?

iKate — Mon, 14 Aug 2017 16:00:20 GMT

Wow so many answers at once! Thanks for help, it works perfectly!