Hello, The solution you provided to me is working well over my local environment (time zone is IST). I am successfully able to get the previous date wrt what is selected in the time picker.   But over my production environment (time zone is EST), it's not showing me the correct timestamp. Please suggest. @ITWhisperer  ... View more

Thanks, It works 🙂 @ITWhisperer  ... View more

Hello, I tried to set a default time on page load but I am getting below error:   Dashboard XML: <form> <label>Display Time Picker</label> <init> <set token="earliestdaybefore">-1d@d</set> <set token="latestdaybefore">@d</set> </init> <fieldset submitButton="false"> <input type="time" token="time" searchWhenChanged="true"> <label>HVA Timeframe</label> <default> <earliest>@d</earliest> <latest>now</latest> </default> <change> <condition> <eval token="earliestdaybefore">relative_time(relative_time(now(),$time.earliest$),"-1d")</eval> <eval token="latestdaybefore">relative_time(relative_time(now(),$time.latest$),"-1d")</eval> </condition> </change> </input> </fieldset> <row> <panel> <title>Previous Dates</title> <table> <search> <query>| makeresults | eval earliestdaybefore= $earliestdaybefore$, latestdaybefore=$latestdaybefore$ | eval earliest_time=strftime(earliestdaybefore,"%Y-%m-%dT%H:%M:%S"), latest_time=strftime(latestdaybefore,"%Y-%m-%dT%H:%M:%S") | table earliest_time, latest_time, earliestdaybefore, latestdaybefore | fields - _time</query> <earliest>$earliestdaybefore$</earliest> <latest>$latestdaybefore$</latest> </search> <option name="drilldown">none</option> <option name="refresh.display">progressbar</option> </table> </panel> </row> </form> @ITWhisperer  ... View more

Thanks mate. It helped me. @ITWhisperer  ... View more

How can I achieve that using  change handler of timer1? Please share the code so that I can understand. @ITWhisperer  ... View more

Thanks for you answer. Your answer helped me what I want to achieve. ... View more

Hello Dear, I need the data to be sort by descending order of the max_time_each_day and by data_source. like below:   But the output of your query is as below:     ... View more

@ITWhisperer Thank you so much for your efforts. 🙂 ... View more

Thanks for making my day Champ :)... Basically I am concerned about the actual times.. so your previous solution works perfectly. Only one last thing is left that suppose if I execute the SPL query today and if any data source not sent the data today , in this case I need the event_count =0 for today as well and as soon as I get the data for that data source then event_count should be that actual count. ... View more

Hello, Thanks for your response. Here below is my query. | tstats count as per_day_count latest(_time) as LTime_per_day where (index=xxxx earliest=-14d@d latest=now) groupby source _time | bin span=1d _time | lookup data_sources.csv source OUTPUTNEW data_source, Monitor | search Monitor=Yes | eval Last_event_time_per_day=strftime(LTime_per_day,"%b %d,%Y %H:%M:%S %p %Z") | join type=outer data_source [ tstats count latest(_time) as LTime values(sourcetype) as sourcetype where (index=yyyy* OR (index=zzzz sourcetype=*amp*)earliest=-d@d latest=now) groupby source _time | bin span=1d _time | lookup data_sources.csv source OUTPUTNEW data_source Monitor | eval Last_event_time=strftime(LTime,"%b %d,%Y %H:%M:%S %p %Z") | eval status= if(count > 0, "Data Received","NO Data Received") ] | eval Last_event_time=strftime(LTime,"%b %d,%Y %H:%M:%S %p %Z") | table data_source sourcetype count Last_event_time Last_event_time_per_day status per_day_count _time | fillnull value="NO Data Received" status | sort - status | rename count as "Event Count" | sort + sourcetype | fillnull value="-" | streamstats window=7 current=f list(per_day_count) as count1 by data_source | stats latest(per_day_count) as today_count, max(Last_event_time_per_day) as max_time_each_day by _time data_source | sort data_source, -_time and here below is a sample subset returned by the above query: max_time_each_day data_source today_count Sep 15,2021 07:25:01 AM EDT ABC 14503 Sep 14,2021 23:59:51 PM EDT ABC 51570 Sep 13,2021 23:59:57 PM EDT ABC 56331 Sep 12,2021 23:59:59 PM EDT ABC 55717 Sep 11,2021 23:59:51 PM EDT ABC 54480 Sep 10,2021 23:59:49 PM EDT ABC 65367 Sep 09,2021 23:59:59 PM EDT ABC 61999 Sep 08,2021 23:59:57 PM EDT ABC 55405 Sep 07,2021 23:59:51 PM EDT ABC 62327 Sep 06,2021 23:59:48 PM EDT ABC 54137 Sep 05,2021 23:59:56 PM EDT ABC 49224 Sep 04,2021 23:59:54 PM EDT ABC 47783 Sep 03,2021 23:59:52 PM EDT ABC 52699 Sep 02,2021 23:59:53 PM EDT ABC 70145 Sep 01,2021 23:59:57 PM EDT ABC 79071 Sep 14,2021 10:05:16 AM EDT XYZ 21 Sep 13,2021 10:32:58 AM EDT XYZ 23 Sep 10,2021 11:30:07 AM EDT XYZ 22 Sep 09,2021 09:51:28 AM EDT XYZ 19 Sep 08,2021 09:56:16 AM EDT XYZ 19 Sep 05,2021 04:32:44 AM EDT XYZ 19 Sep 02,2021 10:03:06 AM EDT XYZ 19 Sep 01,2021 04:32:54 AM EDT XYZ 19 Sep 15,2021 04:32:00 AM EDT PQR 229 Sep 14,2021 04:31:59 AM EDT PQR 268 Sep 13,2021 04:32:03 AM EDT PQR 302 Sep 12,2021 04:31:59 AM EDT PQR 302 Sep 11,2021 04:32:15 AM EDT PQR 297 Sep 10,2021 04:32:00 AM EDT PQR 305 Sep 09,2021 04:32:04 AM EDT PQR 267 Sep 08,2021 04:32:02 AM EDT PQR 267 Sep 07,2021 04:32:12 AM EDT PQR 305 Sep 06,2021 04:32:01 AM EDT PQR 305 Sep 05,2021 04:31:53 AM EDT PQR 195 Sep 04,2021 04:31:52 AM EDT PQR 157 Sep 03,2021 04:32:01 AM EDT PQR 267 Sep 02,2021 04:31:59 AM EDT PQR 157 Sep 01,2021 04:32:53 AM EDT PQR 305 Sep 14,2021 10:05:15 AM EDT DST 103 Sep 13,2021 10:33:00 AM EDT DST 109 Sep 10,2021 11:30:07 AM EDT DST 106 Sep 09,2021 04:31:55 AM EDT DST 105 Sep 08,2021 09:51:06 AM EDT DST 36 Sep 07,2021 15:44:18 PM EDT DST 71 Sep 02,2021 04:31:59 AM EDT DST 105 Sep 01,2021 16:44:02 PM EDT DST 105   My requirement is that to fill the time gaps for data_source "XYZ" and "DST" and for these two data sources I should have today_count=0 for those gaps. Thanks in advance. Regards, ... View more

Hello, Thanks for your prompt reply but query that you shared is not giving expected result. I've achieved 7d rolling average and variance. But now I've to fill the gaps in "Last Event Time" column.  i.e.  I want to show missing dates with 0 event count. Could you please suggest how to fill that gap? Data Source Last Event Time Event Count 7d rolling average event count Variance %Variance data_source1 Sep 13,2021 10:32:58 AM EDT 23 20 3 15 data_source1 Sep 10,2021 11:30:07 AM EDT 22 17 5 30 data_source1 Sep 09,2021 09:51:28 AM EDT 19 14 5 36 data_source1 Sep 08,2021 09:56:16 AM EDT 19 11 8 73 data_source1 Sep 05,2021 04:32:44 AM EDT 19 9 10 112 data_source1 Sep 02,2021 10:03:06 AM EDT 19 6 13 217 data_source1 Sep 01,2021 04:32:54 AM EDT 19 3 16 534 data_source1 Aug 31,2021 10:13:22 AM EDT 19 0 19 0 ... View more