Search hosts view generates too broad database queries

The Hosts tab in Mentat corresponding to the Search hosts view leads to a database query that is too broad and wastes system resources (and takes too long to execute).
The view allows to display results for a single IP address, yet it generates a DB query such as:

SELECT * FROM events AS "_mentatq(83_eaedjv)_" INNER JOIN events_json USING(id) WHERE "detecttime" >= '2020-12-29T12:00:00+00:00'::timestamptz AND "detecttime" <= '2021-01-05T12:00:00+00:00'::timestamptz

The scope of the query could be decreased, from at least two angles:

1. The returned data set could be filtered to only contain events related to the IP queried.
2. Returning all metadata columns JOINed by the JSON document is redundant, most of the data is read, formatted and transferred, but not processed afterwards.

assigned to @Jan_Mach

By Radko Krkoš on 2024-12-26T14:07:08

added C-Design P4 S-New T-Bug labels

changed milestone to %Backlog

A test run on mentat-hub for a single IP address not present took 5 minutes and 23 seconds, a rerun did not finish after 12 minutes (the query is heavily IO bound).

Relation 'relates' to: 6926

By Radko Krkoš on 2021-01-05T12:44:36

assigned to @radko_krkos and unassigned @Jan_Mach

By Radko Krkoš on 2024-12-26T14:22:19

Well, question is - isn't this usecase already covered (also in a more optimized way) by Timeline?

By Pavel Kácha on 2021-01-06T14:34:14

Exactly, but I cannot answer that, hence the To be discussed flag.

By Radko Krkoš on 2021-01-06T14:52:39

Based on videocall, this is a (somewhat) deprecated feature, it will be configured to only be accessible by admins and its role taken over by the Timeline feature. One day, this might be revisited and the implementation improved, if distinct (from Timeline) use cases are found.

By Radko Krkoš on 2021-01-12T07:16:28

unassigned @radko_krkos

By Radko Krkoš on 2024-12-26T14:22:20

added S-Deferred label and removed S-New label

Relation Relates - Feature 6926 Replace Hosts by Timeline

By Pavel Kácha on 2021-01-13T13:35:08

Originally deferred, but closing - if we decide to revive it, it will have to be reviewed thoroughly and reworked anyway.

By Pavel Kácha on 2021-03-09T15:27:07

added S-Closed label and removed S-Deferred label

closed