Mentat

The general query, covering all cases discussed in #6257 and statistician, is as follows:

SELECT
    {since} + {interval} * (width_bucket(detecttime, (SELECT array_agg(buckets) FROM generate_series({since}, {until}, {interval}) AS buckets)) - 1) AS bucket, /* if more than one bucket and time-based data required; leave out for statistician */
    width_bucket(detecttime, (SELECT array_agg(buckets) FROM generate_series({since}, {until}, {interval}) AS buckets)) AS bucket, /* if more than one bucket and bucket index only is required - a bit faster; leave out for statistician */
    {column} AS set, /* if second dimension is used */
    unwrap({column}) AS set, /* if second dimension is used and based on array values */
    lower(unwrap({column})) AS set, /* to work around case issues, but this should be fixed on import - remember P2D2? */
    COUNT(*)
FROM events WHERE
    detecttime > {since} AND detecttime < {until}
GROUP BY
    bucket, /* leave out for statistician */
    set /* if second dimension is used */
ORDER BY bucket /* if ordered set is important (i.e. a dict is not built from the results), otherwise leave out - a bit faster */
;

The {} quoted strings should be replaced by parameters, {since} and {until} are user input, {interval} is calculated somehow, {column} is the second dimension column, beware, it must be psycopg2.sql.Identifier() and cannot be supplied to psycopg2.query(), but to psycopg2.sql.SQL.format().

So, the specializations are as follows.
The simplest case for statistician:

SELECT
    COUNT(*)
FROM events WHERE
    detecttime > {since} AND detecttime < {until}
;

The time binned counts for graphing with timestamps:

SELECT
    {since} + {interval} * (width_bucket(detecttime, (SELECT array_agg(buckets) FROM generate_series({since}, {until}, {interval}) AS buckets)) - 1) AS bucket,
    COUNT(*)
FROM events WHERE
    detecttime > {since} AND detecttime < {until}
GROUP BY
    bucket
;

Any additional dimension for aggregation has to be specified in both the column specifier, with any decompositions using unwrap() and in the GROUP BY part (so naming it using AS is advised).

One additional remark, for attributes with very large value ranges, consider adding a HAVING clause limiting to those with more than one occurrence. For example, for the source IPs:

SELECT
    {since} + {interval} * (width_bucket(detecttime, (SELECT array_agg(buckets) FROM generate_series({since}, {until}, {interval}) AS buckets)) - 1) AS bucket,
    unnest(events.source_ip) AS src,
    COUNT(*)
FROM events WHERE
    detecttime > {since} AND detecttime < {until}
GROUP BY bucket, src
HAVING COUNT(*) > 1;

I find such filtering as quite useful as far as frequent offenders go. This would also limit the amount of data returned tremendously, so the performance impact might be substantial for longer timeframes.

Notes about recent development:

• I have added new search form multi select box for restricting set of aggregations that will be performed.
• The "limit in database" search option is not implemented yet.
• A have added following additional calculations: source and target ports, source and target types, detector types and protocols.
• A have added in memory calculations of overall aggregations from timeline data to make comparison with database query providing same result.
• Attached file illustrates current performance:
  • Time window was 3 days.
  • Total time was 00:02:54.
  • 00:02:11 - searching and fetching rows.
  • 00:00:42 - postprocessing.
    • 00:00:12 - converting rows to statistical structures.
    • 00:00:20 - truncating all timelines and pies.
    • 00:00:10 - recalculating pies from untruncated timelines and truncating (for comparison with db).

You can see, that aggregation queries for IPs, source and target ports were most demanding and took 5s, 14s, and 2s respectively, while when calculating the same data from timeline datasets it took only 2s, 5s, and 0,6s respectively. So I guess the conclusion here is to drop aggregation aggregations in database and calculate them from timeline aggregations.

Pavel Kácha wrote:

Hotovo, týdenní dotaz trvá cca 2 minuty pro výpočet všech agregací pro eventy, abuse, kategorie, detektory, IP, třídy, závažnosti.

• přidat zdrojové porty, cílové porty, služby, tagy detektoru, tagy zdroje, tagy cíle; uvidíme, probereme.
• limitování top N na straně PQ, porovnat s nelimitovaným výstupem (možná checkbox na výběr?)
• zkusit nahradit agregační dotaz sečtením z grafových dat v Pythonu

There are following steps remaining to be done:

• Change the pie chart for multivalue sets like IPs according to Radko`s recommendation.
• Consider again limiting/truncating on the side of the database. In this approach we however loose the "REST" and total sum, some of the rows will be omitted. Also the outer query will be much more complex in the where section, it should in fact be the same as the where section in the inner query, in my opinion. So is this ok?
• Implement caching.
• Consider, choose and implement some chart lazy loading mechanism, so that the user is not forced to wait 2 minutes for all datasets when he is only interested in one or two.
  1. On request presearch and display first chart, others will be fetched and calculated on tab switch.
  2. On request present empty page and fetch all charts on tab switch. This is a variant of the previous, because the first chart will be immediately requested and fetched.
  3. Let user choose which charts to calculate and render.
  4. Keep the things the way they are now, always calculate and display all data.

Jan Mach wrote:

Conclusions from yesterdays meeting:

• Based on some measurements we will use following approach:
  • Aggregation and timeline aggregations will always be calculated, those data are needed for all other visualisations.

Done. Overall event count aggregation and timeline aggregations are always searched and calculated.

• Perform aggregation aggregations in Python code - calculate them by traversing timeline calculations.

Done. I have disabled all aggregation aggregation queries. Only timeline aggregation queries are executed, aggregation aggregations are calculated from them afterwards.

• Perform _timeline aggregations in database. For datasets with high number of keys (IPs, ports, ...) use limiting directly within database to restrict number of rows that need to be fetched and processed.

Done. IP, port and abuse group aggregations are toplisted by configurable limit number, all other aggregations are unlimited.

• Enforce upper time interval threshold for all searches to enable caching.

Done. There is a default for upper time interval threshold. It is calculated by using current timestamp with anything below hours set to zero to achieve nice looking time window.

• Use server side caching for search results.

TBD

• Use client side caching in browser`s javascript.

TBD

• By default display only total event count statistics, all other tabs will be fetched on demand.

TBD

• Keep the configurable toplist limit and list of precalculated statistics, but remove them from form to reduce the number of options user has to understand.

Done. These parameters are available to API via URL parameters and are display in the HTML form only to the administrator.

These decisions are based on following key data:

• un-toplisted database search for source-port statistics, 2 weeks of data
  • timeline search and fetch: 1:18s, 11 122 841 rows
  • convert rows -> dataset: 28s
  • toplisting: 15s
• toplisted database search for source-port statistics, 2 weeks of data:
  • search and fetch: 1:05s, 17 148 rows
  • convert rows -> dataset: 0.1s
• aggregation search and fetch:
  • in database: 16s
  • in code: 10s from untoplisted, 0.1s from toplisted

According to the measurement database toplisting scales much better than Python code toplisting.

We are happy with current state of the matters for now. New issue #6332 was created as a follow up of this one to implement remaining features like caching and on demand loading.