Add time range filter based on specific column when using loadjob savedsearch query

[maayan]

Hi,

I'm using: loadjob savedsearch because my query is big and it takes time to load.
I have some multi-select filters and i want to add input time range filter.
(| loadjob savedsearch="mp:search:queryName" | where $pc$ AND  $Version$ )

I'm not sure how to do that because i need to use a field called: Timestamp (i get it in my query, this is the time the event is written to the json file ) and not  the _time field.
In addition, I don't know how to use loadjob savedsearch with time range filter

Can you help me, please?

Thank,
Maayan

[ITWhisperer]

You can't use the time range filter on loadjob savedsearch for this purpose. The time range filter on the loadjob command applies to when the saved search was executed (and its results saved). It is not applied to the results themselves. For this you would have to use a where command (as you already have), and for time ranges, you should parse your timestamp field into and epoch time (using the strptime() function) so that it can be compared with other epoch time values e.g. the start and end of your desired time range.

[maayan]

thanks. I'm trying to do something like that but  it doesn't work:
(my TimeStamp field format is: 2023-11-07 16:43:05.227)

<form version="1.1" theme="dark">
<label>time try</label>
<search id="bla">

<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>

<query> | loadjob savedsearch="mp:search:query name"

| where $pc$ AND $version$ AND TimeStamp&gt;$field1.earliest$ AND TimeStamp&lt;$field1.latest$

</query>
</search>

<fieldset submitButton="true" autoRun="false">
<input type="time" token="field1">
<label></label>
<default>
<earliest>-1d@h</earliest>
<latest>now</latest>
</default>
</input>

<input type="multiselect" token="pc" searchWhenChanged="true">
<label>pc</label>
<choice value="%">All</choice>
<default>%</default>
<prefix>(</prefix>
<suffix>)</suffix>
<valuePrefix>(pc like("</valuePrefix>
<valueSuffix>"))</valueSuffix>
<delimiter> OR </delimiter>
<fieldForLabel>pc</fieldForLabel>
<fieldForValue>pc</fieldForValue>
<search base="bla">
<query> | where ( $version$)
| dedup pc| fields pc </query>
</search>
</input>
........

[ITWhisperer · Accepted solution]

As I said, you need to parse your timestamp field using the strptime() function so that you can compare it with other time values, e.g. earliest and latest. Having said that, you should probably use addinfo to get the min and max times used in the search.

[maayan]

Can you write me from your experience how to parse my timestamp field to be able to be compared with earliest, latest parameters please?

[ITWhisperer]

Try something like this

<query> | loadjob savedsearch="mp:search:query name"
| addinfo
| where $pc$ AND $version$ AND strptime(TimeStamp,"%F %T.%3N")&gt;info_min_time AND strptime(TimeStamp,"%F %T.%3N")&lt;info_max_time

</query>

[maayan]

thanks!! i used min, max because add_info didn't work for me. but it doesn't work, when i select a range (for example 4 hours) in the time filter the data that i get is not between this range. maybe i should do something with $field1.earliest$, $field1.latest$?

my code:

<search id="bla">

<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>

<query> | loadjob savedsearch="mp:search:query name"

| eventstats max(_time) as maxtime, min(_time) as mintime

| where $pc$ AND $version$
AND strptime(TimeStamp,"%F %T.%3N")&gt;mintime AND strptime(TimeStamp,"%F %T.%3N")&lt;maxtime
</query>
</search>

<fieldset submitButton="true" autoRun="false">
<input type="time" token="field1">
<label></label>
<default>
<earliest>-1d@h</earliest>
<latest>now</latest>
</default>
</input>

[maayan]

i added the command | add info 
and i think that it works
i will do validations but thanks a lot! 🙂

[ITWhisperer]

The command is addinfo not add_info - the problem with using "$field1.earliest$" and "$field1.latest$" is that they can contain string and not epoch times, whereas addinfo provides the epoch times derived from the timepicker.