How to set token from specific row & field in a table?

[htrednek]

Ok, so I'm trying to consolidate some searches and one sticking point is that I've got an ugly base search chased by another doing an appendpipe to give me a summary row. Ideally I'd like it to be one search, however, I need to set tokens from the values in the summary but cannot seem to make that happen outside of the separate search.
I know it uses the $result.fieldname$ method and drilldowns use $row.column$. I'm kinda hoping there's a hybrid to specify the row# or last row specifically using the $result.xxx$ style. (If there is, I can 't get the syntax correct...)

<search id="bigolquery">
    <query> yadda yadda </query>
</search>
<search id="bigolsumm" base="bigoldquery">
    <query> 
        | appendpipe [ stats sum(fld1) as fld1 sum(fld2) as fld2 
        | eval fldavg=round(fld1/fld2*100,1)]
    </query>
    <done>
        <set token="fld1val">$result.fld1$</set>
        <set token="fld2val">$result.fld2$</set>
        <set token="fldavgval">$result.fldavg$</set>
    </done>
</search>

[woodcock · Accepted solution]

Add this:

... | eventstats last(xxx) AS _last_xxx

Then every row has an invisible field called _last_xxx with the last value of xxx so you can then use $result._last_xxx$.

[htrednek]

This works perfectly. Thank you!

[niketn]

@htrednek the default token $result.<fieldname>$ fetches the first row of result. So crooked way would be you use | reverse in your subsearch and your last row will become first row. Hence the $result.<fieldname>$ token will access the value from the last row.

[somesoni2]

Are you using bigolsumm query in any visualization Or it's just for setting the token?

[htrednek]

yes. It's displayed as part of the table as well as setting the tokens.

[somesoni2]

Ok.... as far as I know, you can't set tokens from specific row number in the table. What you can do is create another search which is not used in any visualization but is using base="bigoldquery" and running your appendpipe stats are regular stats. The purpose of this search will be just to set tokens.

<search id="bigolsummtok" base="bigoldquery">
     <query> 
         |  stats sum(fld1) as fld1 sum(fld2) as fld2 
         | eval fldavg=round(fld1/fld2*100,1)
     </query>
     <done>
         <set token="fld1val">$result.fld1$</set>
         <set token="fld2val">$result.fld2$</set>
         <set token="fldavgval">$result.fldavg$</set>
     </done>
 </search>