I need to pass a earliest and latest value in a dashboard in version 6.3.1

[3DGjos]

Hello, im making this dashboard for a client:

table 1: groups transactions by their duration:

   <query> mysearch | transaction  | table  transaction_start transaction_end  transaction_duration transaction_ID</query>
            </search>
           <drilldown>
           <set token="id">$row.transaction_ID$</set>
           </drilldown>

The ID token goes to my table 2, which makes something like:

<query>search  ID=$id$ | table Time  _raw  | sort - Time</query>

and the problem is with table 3. This is what I need for my table 3: I need to show the raw events that happened between the transaction_start and transaction_end time.

The problem would be solved with eval token and strptime to pass an epoch time format date (my format is strftime(_time, "%d/%m/%y %H:%M:%S:%3N")) but in this version, drilldown and convert date to epoch does not work for me. neither the tag

Something like:

table3:

index=myindex earliest=$epoch_from_transaction_start$ latest=$epoch_from_transaction_start$

to bring all of the events, along with the transaction events.

tried with the map command, but it limits me to only the events of the transaction.

Thanks a lot!

[3DGjos · Accepted solution]

Hello, I managed to solved, thanks @niketnilay for the logic, @woodcock for the advice, I reworked my searches and im not using transaction anymore, and @Sukisen1981 for the advice

Here is what I did:

table 1: 

(@woodcock the range function did not work, so I made a workaround)

 <search id="base1" base="base0">
              <query>| search   mysearch | stats  min(_time) AS start max(_time) AS finalization values(nombre_de_clase) AS "Nombre de clase" values(servicio) AS Servicio BY ID | eval duration= (finalization- start) |table start finalization duration ID "Nombre de clase" Servicio | fieldformat start= strftime(start, "%d-%b-%Y %H:%M:%S:%3N")
     | fieldformat finalization= strftime(finalization, "%d-%b-%Y %H:%M:%S:%3N")  | fieldformat duration= strftime(duration, "%M:%S:%3N")
           </query>
            </search>

            <drilldown>
              <set token=mymanytokens>
              <set token="base1_earliest">$row.start$</set>
              <set token="base1_latest">$row.finalization$</set>
            </drilldown>

Then these tokens goes to a basesearch (I've got like 3 of those for other porpuses) hidden in the XML for doing the transation from date to epoch:

  </search>
  <search id="base3">
    <query>| makeresults
           | eval earliest=round(strptime("$base1_earliest$", "%d-%b-%Y %H:%M:%S:%3N"),3), latest=round(strptime("$base1_latest$", "%d-%b-%Y %H:%M:%S:%3N"),3)</query>
    <earliest>-1s</earliest>
    <latest>now</latest>
    <finalized>
      <set token="base3_earliest">$result.earliest$</set>
      <set token="base3_latest">$result.latest$</set>
    </finalized>

And then, I've got my table 3, which searches in the whole index for what happened between the transaction time, consuming the tokens generated by base3:

 <event id="table3" depends="$wholevents$">
        <title>events between$base1_earliest$ and $base1_latest$</title>
        <search>
          <query>index=myindex</query>
          <earliest>$base3_earliest$</earliest>
          <latest>$base3_latest$</latest>
        </search>

      </event>

Thanks a lot!

[woodcock]

Then you should click Accept on your answer and UpVote any answers or comments that helped you get here.

[woodcock]

First of all DO NOT USE transaction in production, especially with no field arguments. It appears that your events already have a transaction_ID field so you can do something like this:

... | stats range(_time) AS duration min(_time) AS transaction_start max(_time) AS transaction_end list(_raw) AS events BY transaction_ID

When you are making dates presentable, DO NOT USE eval, instead use fieldformat, like this, so that the integer/time_t nature of the value does not change:

... | fieldformat transaction_start = strftime(transaction_start, "%c")
| fieldformat transaction_end = strftime(transaction_end, "%c")

Now, on to your real question(s); you should be able to add more tokens like this:

<drilldown>
   <set token="id">$row.transaction_ID$</set>
   <set token="transaction_start">$row.transaction_start$</set>
   <set token="transaction_end">$row.transaction_end$</set>
</drilldown>

[niketn]

@3DGjos while you may not work with <eval> strftime in 6.3, you can still try independent search in 6.3 where you pass the _time value as token to the independent search and use eval to convert the same to string time. Then you can use <preview> or <finalized> search event handler (in version 6.4 and above they are <progress> and <done> respectively.) depending on when you want the token to get resolved.

Something like the following <search> which is not part of any visualization row or panel can be used as an independent search and takes the _time epoch token from second table to give string time as new token.

  <!-- Independent search to convert epoch time to string time -->
  <search>
    <query>
      | makeresults
      | eval selectedTime=strftime($tokSelectedTransactionTime$, "%d/%m/%y %H:%M:%S:%3N")
    </query>
    <earliest>-1s</earliest>
    <latest>now</latest>
    <finalized>
      <set token="tokSelectedStringTime">$result.selectedTime$</set>
    </finalized>
  </search>

Please try the following run anywhere example and confirm as I have no way of testing the code in 6.3.

<dashboard>
  <label>Second Table for Time Drilldown</label>
  <!-- Independent search to convert epoch time to string time -->
  <search>
    <query>
      | makeresults
      | eval selectedTime=strftime($tokSelectedTransactionTime$, "%d/%m/%y %H:%M:%S:%3N")
    </query>
    <earliest>-1s</earliest>
    <latest>now</latest>
    <finalized>
      <set token="tokSelectedStringTime">$result.selectedTime$</set>
    </finalized>
  </search>
  <row>
    <panel>
      <title>Click to get _time for independent search</title>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd_ui_access
|  table _time _raw
|  reverse</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">5</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <drilldown>
          <set token="tokSelectedTransactionTime">$click.value$</set>
        </drilldown>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Show selected Epoch time and corresponding String Time</title>
      <table>
        <search>
          <query>
            | makeresults
            | fields - _time
            | eval selectedTimeEpoch=$tokSelectedTransactionTime$
            | eval selectedTimeString="$tokSelectedStringTime$"
          </query>
          <earliest>-1s</earliest>
          <latest>now</latest>
        </search>
      </table>
    </panel>
  </row>
</dashboard>

[Sukisen1981]

Hi @3DGjos -

In the xml of your first panel, after the query tag ends, try setting the time tokens in the done section, soothing kike this

<done>
            <eval token="earliest">blah blah</eval>
<eval token="latest">blah blah</eval>
          </done>

You might need to retrofit your epoch times using strptime/strftime. But this should work.
Basically, we are saying - after the first table search is completed (and hence the done tags), capture the values of your transaction start and end times in tokens,pass and use these tokens in the 3rd table

[niketn]

@Sukisen1981 just FYI <done> search event handler was not available in Splunk 6.3 🙂

[Sukisen1981]

hi @3DGjos and @niketnilay - apologize, I had not seen the splunk version in the question although it is mentioned in bold in the title 😞 .. many apologies

[niketn]

@Sukisen1981 no need to apologize we are all trying to help over here as much as we can. It would be difficult to get 6.3 stack when current Splunk version is already on 7.3