Splunk Search

Problem with tokens and earliest/latest

tsomod
Path Finder

Hi everyone!

So, I have this search:

index=XXXXX sourcetype=XXXXX earliest="$time_token.earliest$" latest="$time_token.latest$" NOT 
    [| inputlookup timestamped_users.csv 
    | where _time>=relative_time(now(), "$time_token.earliest$") AND _time<=relative_time(now(), "$time_token.latest$") 
    | rename "User IP" as src_ip] 
| dedup src_ip sortby +_time 
| rename src_ip as "User IP" 
| table "User IP", "_time" 
| stats dc("User IP") as "Returning Users" 
| appendcols 
    [ search index=XXXXX sourcetype=XXXXX earliest="$time_token.earliest$" latest="$time_token.latest$" AND 
        [| inputlookup timestamped_users.csv 
        | where _time>=relative_time(now(), "$time_token.earliest$") AND _time<=relative_time(now(), "$time_token.latest$") 
        | rename "User IP" as src_ip] 
    | dedup src_ip sortby +_time 
    | rename src_ip as "User IP" 
    | table "User IP", "_time" 
    | stats dc("User IP") as "New Users"] 
| transpose 
| rename "row 1" as "Count" "column" as Field

My goal is to compare the unique IPs stored along with their timestamp (first login) in the timestamped_users.csv lookup to the unique IPs that have logged in over time in order to find out how many "new" and "returning" users there have been over a user specified time period. This could probably have been done in a better way, but bear with me. The time period to search over is specified by a custom time token. My problem surfaces whenever I run this search with any preset time token values e.g "All time", "Last 30 days" etc. For example, if I run the search with "All time" the $time_token.earliest$ and $time_token.latest$ is evaluated as "" and "", which in turn results in no output from the search. Also, if I run the search with "Last 30 days" which is evaluated as "now". This is not accepted properly by the relative_time() command, and so again the second part of the search returns nothing. Do you have any ideas as to how I could get the output I want? 🙂

0 Karma
1 Solution

svendby90
Path Finder

A way of doing it, which seems to give the expected output, is to convert the time token to epoch using conditions when the token is first set (similar issue and solution here: https://answers.splunk.com/answers/489033/how-can-i-convert-a-timerange-token-to-an-epoch-va.html).

Try something like this in your XML:

<fieldset submitButton="false">
    <input type="time" searchWhenChanged="true">
      <label>...</label>
      <default>
        <earliest>-7d@w0</earliest>
        <latest>@w0</latest>
      </default>
      <change>
        <condition match="isnum($earliest$) OR isnum($latest$)">
          <eval token="earliest_time">$earliest$</eval>
          <eval token="latest_time">$latest$</eval>
        </condition>
        <condition>
          <eval token="earliest_time">relative_time(now(), $earliest$)</eval>
          <eval token="latest_time">relative_time(now(), $latest$)</eval>
        </condition>
      </change>
    </input>
  </fieldset>
  ...
        <search>
          <query>index=xxx sourcetype=xxx earliest="$earliest_time$" latest="$latest_time$" NOT 
     [| inputlookup timestamped_users.csv 
     | where _time>=$earliest_time$ AND _time<=$latest_time$
     | rename "User IP" as src_ip] 
 | dedup src_ip sortby +_time 
 | rename src_ip as "User IP" 
 | table "User IP", "_time" 
 | stats dc("User IP") as "Returning Users" 
 | appendcols 
     [ search index=xxx sourcetype=xxx earliest="$earliest_time$" latest="$latest_time$" AND 
         [| inputlookup timestamped_users.csv 
         | where _time>=$earliest_time$ AND _time<=$latest_time$ 
         | rename "User IP" as src_ip] 
     | dedup src_ip sortby +_time 
     | rename src_ip as "User IP" 
     | table "User IP", "_time" 
     | stats dc("User IP") as "New Users"] 
 | transpose 
 | rename "row 1" as "Count" "column" as Field</query> 
</search>

View solution in original post

svendby90
Path Finder

A way of doing it, which seems to give the expected output, is to convert the time token to epoch using conditions when the token is first set (similar issue and solution here: https://answers.splunk.com/answers/489033/how-can-i-convert-a-timerange-token-to-an-epoch-va.html).

Try something like this in your XML:

<fieldset submitButton="false">
    <input type="time" searchWhenChanged="true">
      <label>...</label>
      <default>
        <earliest>-7d@w0</earliest>
        <latest>@w0</latest>
      </default>
      <change>
        <condition match="isnum($earliest$) OR isnum($latest$)">
          <eval token="earliest_time">$earliest$</eval>
          <eval token="latest_time">$latest$</eval>
        </condition>
        <condition>
          <eval token="earliest_time">relative_time(now(), $earliest$)</eval>
          <eval token="latest_time">relative_time(now(), $latest$)</eval>
        </condition>
      </change>
    </input>
  </fieldset>
  ...
        <search>
          <query>index=xxx sourcetype=xxx earliest="$earliest_time$" latest="$latest_time$" NOT 
     [| inputlookup timestamped_users.csv 
     | where _time>=$earliest_time$ AND _time<=$latest_time$
     | rename "User IP" as src_ip] 
 | dedup src_ip sortby +_time 
 | rename src_ip as "User IP" 
 | table "User IP", "_time" 
 | stats dc("User IP") as "Returning Users" 
 | appendcols 
     [ search index=xxx sourcetype=xxx earliest="$earliest_time$" latest="$latest_time$" AND 
         [| inputlookup timestamped_users.csv 
         | where _time>=$earliest_time$ AND _time<=$latest_time$ 
         | rename "User IP" as src_ip] 
     | dedup src_ip sortby +_time 
     | rename src_ip as "User IP" 
     | table "User IP", "_time" 
     | stats dc("User IP") as "New Users"] 
 | transpose 
 | rename "row 1" as "Count" "column" as Field</query> 
</search>

View solution in original post

tsomod
Path Finder

Thanks! This seems to have done the trick 🙂

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi tsomod,

Instead of using token in lookup searches you can try addinfo command to get timebased records.
Can you please replace below lookup search in your search and try it?? It is handling "All time" search also.

| inputlookup timestamped_users.csv | addinfo | where ((NOT (isnum(info_max_time) AND info_max_time!="+Infinity")) OR _time > info_min_time AND _time < info_max_time) 

https://docs.splunk.com/Documentation/Splunk/6.6.1/SearchReference/Addinfo

Thanks

0 Karma

tsomod
Path Finder

Unfortunatey it didnt work =/ I am still not getting the output I want.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Basically, you need a two-step process. The first step is the panel itself, where the time_token gets set.

The second step is, on change of time_token, to calculate your start_token and end_token from those values. I'd suggest you just calculate and set the epoch time of the start and end, so if earliest is blank, it calculates to zero, and if latest is blank, it calculates to arbitrarily large.

0 Karma

tsomod
Path Finder

This is something like what I had in mind, I am just at a loss as to how to implement it. Do you have an example? 🙂

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!