topic Re: Add time range filter based on specific column when using loadjob savedsearch query in Dashboards & Visualizations

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

maayan — Tue, 12 Dec 2023 10:47:03 GMT

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

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

ITWhisperer — Tue, 12 Dec 2023 10:58:21 GMT

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.

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

maayan — Tue, 12 Dec 2023 11:53:37 GMT

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>$field1.earliest$ AND TimeStamp<$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>
........

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

ITWhisperer — Tue, 12 Dec 2023 11:57:17 GMT

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.

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

maayan — Tue, 12 Dec 2023 12:02:56 GMT

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

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

ITWhisperer — Tue, 12 Dec 2023 12:08:54 GMT

Try something like this

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

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

maayan — Tue, 12 Dec 2023 13:02:26 GMT

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")>mintime AND strptime(TimeStamp,"%F %T.%3N")<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>

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

maayan — Tue, 12 Dec 2023 13:19:17 GMT

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

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

ITWhisperer — Tue, 12 Dec 2023 13:29:34 GMT

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.

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

muhammad_luthfi — Thu, 05 Jun 2025 20:02:31 GMT

Hi,

I've solved my case, basically there are 2 step :

1. Build correct job output on saved search, better to specific time format :

| eval timeframe = strftime(_time, "%Y-%m-%d %H:%M:%S %Z") Output : timeframe hostname availability 2025-03-08 00:00:00 +07 HostA 100 2025-03-09 00:00:00 +07 HostA 100

2. On dashboard add time range panel rename token to $time_range$, then create handling to convert time to epoch

| loadjob savedsearch="admin:itsi:sla_availability" artifact_offset=0 | eval timeframe_epoch = strptime(timeframe, "%Y-%m-%d %H:%M:%S %z") | eval earliest_epoch = strptime("$time_range.earliest$", "%Y-%m-%dT%H:%M:%S.%3NZ") | eval latest_epoch = strptime("$time_range.latest$", "%Y-%m-%dT%H:%M:%S.%3NZ") | where timeframe_epoch >= earliest_epoch AND timeframe_epoch <= latest_epoch | table *

So, the output data will be specific time only :), as of know only date range selection only works,

will test for dynamic selection like last 7 days, last 30 day, etc