Search blocks of time (certain hours)

[mmoermans]

Hi there,

I'm trying to get a chart of total firewall connections dropped (action=dropped) from the checkpoint firewall of the last 14 days but in two blocks of time: one between 07:00 – 19:00 (7am-7pm) and one between 19:00 – 07:00 (7pm-7am) and then a mediaan of both of the two weeks. This in a datamodel (network traffic) so the general date_hour>=7 doesn't seem to work.

How do I search for those two blocks of time in my query over 2 weeks?

[gcusello · Accepted solution]

Hi mmoermans,
you should use in your search "eval" command to create your blocks:

| eval block=if(date_hour>6 AND date_hour<20,"Block1","Block2")

in this way you can create stats using this new field.
Bye.
Giuseppe

[mmoermans]

Am I doing something wrong with the following search?:

| tstats `summariesonly` count from datamodel=Network_Traffic where host=firewall AND block=Block1 by All_Traffic.action
| eval block=if(date_hour>6 AND date_hour<20,"Block1","Block2")

[gcusello]

If you use tstats you have to save all the fields you use before tscollect, otherwise you haven't them and you need to extract from _time at search time.
In other words, you have to add to your search, before eval the following command
| eval date_hour=strftime(_time,"%H")
or modify the porevous eval in this way:

| tstats `summariesonly` count from datamodel=Network_Traffic where host=firewall AND block=Block1 by All_Traffic.action
| eval block=if(strftime(_time,"%H")>6 AND strftime(_time,"%H")<20,"Block1","Block2")

Bye.
Giuseppe