topic how to display information for a specific time range everyday in Dashboards & Visualizations

how to display information for a specific time range everyday

nyasharashad59 — Tue, 29 Sep 2020 16:05:28 GMT

Good day

I have a query i have generated. I want the query to show me events from 11pm to 6am ONLY. So if i select from month to date it only shows information of the time range i have specified.

SubscriberId=$msisdn$ | stats sum(TBytes) as total, sum(RBytes) as received, sum(TxBytes) as transmitted | eval total_mb=total/1000/1000 | eval received_mb=received/1000/1000 | eval transmitted_mb=transmitted/1000/1000

Re: how to display information for a specific time range everyday

niketn — Wed, 04 Oct 2017 09:54:58 GMT

@nyasharashad59, you can use date_hour field to filter events based on specific hours your require:

<YourBaseSearch> SubscriberId=$msisdn$ date_hour=23 OR (date_hour>=0 AND date_hour<7)
| <YourRemainingSearch>

Re: how to display information for a specific time range everyday

kamlesh_vaghela — Wed, 04 Oct 2017 11:25:34 GMT

Hi nyasharashad59,

Can you please try below search??

SubscriberId=$msisdn$ | timechart sum(TBytes) as TBytes, sum(RBytes) as RBytes, sum(TxBytes) as TxBytes span=1s 
| convert ctime(_time) as Time timeformat="%H%M%S" 
| where (Time>230000 AND Time<235959) OR (Time<060000) 
| stats sum(TBytes) as total, sum(RBytes) as received, sum(TxBytes) as transmitted 
| eval total_mb=total/1000/1000 
| eval received_mb=received/1000/1000 
| eval transmitted_mb=transmitted/1000/1000

You can change Time Range in where condition.

I hope I will work.

Thanks

Re: how to display information for a specific time range everyday

DalJeanis — Wed, 04 Oct 2017 12:31:51 GMT

@kamlesh_vaghela - good start. Two suggestions... (1) Since time cannot be greater than 24, you don't need the second half of the first time condition. (2) the remaining time conditions will exclude items that happen at exactly 230000 and 06000000, so change those to >= and <=.

You could also just use the "%H" portion and test for >="23" and <="06"

Re: how to display information for a specific time range everyday

DalJeanis — Wed, 04 Oct 2017 12:32:36 GMT

@niketnilay - isn't the >=0 redundant?

Re: how to display information for a specific time range everyday

kamlesh_vaghela — Wed, 04 Oct 2017 12:48:19 GMT

Yeah, That's true.
It will be very much clear and simple to compare hours.

Thanks @DalJeanis.

Hi nyasharashad59,

Can you please try below revised search??

SubscriberId=$msisdn$ | timechart sum(TBytes) as TBytes, sum(RBytes) as RBytes, sum(TxBytes) as TxBytes span=1s 
| convert ctime(_time) as Time timeformat="%H" | where Time>=23 OR Time<6 
| stats sum(TBytes) as total, sum(RBytes) as received, sum(TxBytes) as transmitted 
| eval total_mb=total/1000/1000 
| eval received_mb=received/1000/1000 
| eval transmitted_mb=transmitted/1000/1000

Thanks

Re: how to display information for a specific time range everyday

niketn — Wed, 04 Oct 2017 13:48:26 GMT

Yes it is. Habit or reflex typed it without thinking 🙂

Re: how to display information for a specific time range everyday

DalJeanis — Wed, 04 Oct 2017 14:24:02 GMT

@kamlesh_vaghela <=6

Re: how to display information for a specific time range everyday

kamlesh_vaghela — Wed, 04 Oct 2017 14:30:12 GMT

Hi DalJeanis,
Here we are comparing Hours only so Don't you think <=6 will fetch event after 6 am also?? means events of (%H:%M) 6:10 ...6:50...6:59 .. We need events up to 6AM only.

Re: how to display information for a specific time range everyday

niketn — Tue, 29 Sep 2020 16:01:26 GMT

@DalJeanis, @kamlesh_vaghela, we should always consider filtering records upfront. So using date_hour in base search will have better performance as compared to filtering later in the search.