Custom dataset - show occurrences of certain log type across time

[ergotherego]

Hello everyone,

I am trying to create a custom line graph that will show occurrences of a certain log message over time, so you can easily see the spikes of when this event occurs a lot.

I started by creating a custom chart from Log View section showing the log results I care about. But I end up with a graph that doesn't work - there are no lines on it. Running the test query function in the dataset does show me the log results I care about.

Guessing the issue is related to grouping/aggregating somehow. If anyone can shed some light I would appreciate it!

These are 3 raw log entries, showing the log messages I care about:

itime=2018-02-21 14:35:05 vd=root rcvdbyte=0 craction=262144 app=443/tcp dtime=2018-02-21 14:35:04 date=2018-02-21 dstip=20.20.20.10 crscore=5 duration=0 sentbyte=0 crlevel=low proto=6 devid=FG200D4Q00000000 poluuid=460bde16-0364-51e7-c774-a7aab6ae4cd1 dstport=443 type=traffic devname=customerFirewall trandisp=noop sessionid=477839005 itime_t=1519248905 policyid=38 srcintf=port1 srcip=10.10.10.20 level=warning appcat=unscanned srcport=61075 logid=11 subtype=forward time=14:35:04 action=ip-conn dstintf=Trust itime=2018-02-21 09:56:20 vd=root rcvdbyte=0 craction=262144 app=443/tcp dtime=2018-02-21 09:56:19 date=2018-02-21 dstip=20.20.20.10 crscore=5 duration=0 sentbyte=0 crlevel=low proto=6 devid=FG200D4Q00000000 poluuid=460bde16-0364-51e7-c774-a7aab6ae4cd1 dstport=443 type=traffic devname=customerFirewall trandisp=noop sessionid=477083575 itime_t=1519232180 policyid=38 srcintf=port1 srcip=10.10.10.20 level=warning appcat=unscanned srcport=50652 logid=11 subtype=forward time=09:56:19 action=ip-conn dstintf=Trust itime=2018-02-21 08:27:12 vd=root rcvdbyte=0 craction=262144 app=443/tcp dtime=2018-02-21 08:27:11 date=2018-02-21 dstip=20.20.20.10 crscore=5 duration=0 sentbyte=0 crlevel=low proto=6 devid=FG200D4Q00000000 poluuid=460bde16-0364-51e7-c774-a7aab6ae4cd1 dstport=443 type=traffic devname=customerFirewall trandisp=noop sessionid=477022798 itime_t=1519226832 policyid=38 srcintf=port1 srcip=10.10.10.20 level=warning appcat=unscanned srcport=50577 logid=11 subtype=forward time=08:27:11 action=ip-conn dstintf=Trust

The fields I care about are:

dstip=20.20.20.10 dstport=443 craction=262144

This is the current query:

select from_itime(itime) as itime, string_agg(distinct ipstr(`dstip`), ' ') as dstip__agg_ from ###(select `itime`, `dstip` from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and ( ( 'Failed Connection Attempts to 20.20.20.10' = ANY(threats)) AND (( (`action`='ip-conn') ))) group by `itime`, `dstip` order by `itime`)### t group by `itime` order by `itime`

Desired output is Y-axis showing count of occurrences, and X-axis showing time (ideally per minute).

Thank you!

[ergotherego]

With some help from a friend, I got a bit closer using this query: 

select count(itime) as itime, from_itime(min(itime)) as nicetime, min(dstip) as ip from $log     where $filter and logid_to_int(logid)     not in (4, 7, 14)     and ( ( ( (`action`='ip-conn') ))     AND (`dstip` = inet('20.20.20.10'))) group by round(itime/300) order by nicetime

It seems to pick the first occurrence within a 5min timestamp and report on that, instead of determining the 5-minute time-slices that exist within the data, and counting occurrences there. E.g:

14:00 = 2 14:05 = 3 14:10 = 9 etc, etc

Attached is picture of the result I get from my test query.

[AtiT · Accepted solution]

Hello, could you try the below traffic dataset wheter it will fulfill what you need?

SELECT RIGHT($flex_timescale,16) AS hodex, COUNT(*) AS totalcount FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN(4, 7, 14) AND `action`='ip-conn' AND `dstip` = inet('20.20.20.10') GROUP BY hodex ORDER BY hodex ASC

 

[ergotherego]

That worked great, thank you!!!