Query Logs - Calculating QPS

Hi All--

I have been asked to aggregate up query logs from a database platform. This platform does not have a good way to get accurate QPS counts, but it does send the query details to it's logs. So, we have devised a plan to consume the logs in their raw form via filebeat, send to logstash to parse out some fields, and then we'll write a separate process to combine the details.

Example logs:
Apr 29, 2020 @ 15:45:37.737 Started executing Command (queryId=123): SELECT * FROM TABLE
Apr 29, 2020 @ 15:45:39.424 Finished Executing Command (queryId=123): Took .04 seconds

Problem
So, given that my goal is to get to QPS. I need to figure out a way to accurately show over time how many queries are running at any given second. In the past we have just counted based on the datetime of the start log, but that just gives us queries started per second, and isn't accurate.

We really need to account for any given second a query that was running or is currently running to get an accurate QPS metric.

Does anyone have a suggested way of doing this with elasticsearch? I was considering doing this via a separate process that grab from the raw log data index and dump data into a new index with the data combined into one object per query.

Thanks!
Drew

https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html might be able to do what you want.