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? 🙂
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>
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>
Thanks! This seems to have done the trick 🙂
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
Unfortunatey it didnt work =/ I am still not getting the output I want.
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.
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? 🙂