how to display information for a specific time range everyday

[nyasharashad59]

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

[kamlesh_vaghela]

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

[DalJeanis]

@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"

[kamlesh_vaghela]

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

[DalJeanis]

@kamlesh_vaghela <=6

[kamlesh_vaghela]

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.

[niketn]

@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.

[niketn]

@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>

[DalJeanis]

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

[niketn]

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