Logstash multiple jdbc plugin ingestion

[Vladpov]

Hey guys,

I have set up MariaDB database from which I transport data to Elasticsearch via Logstash jdbc plugin. The ingestion of the data from database processlist table is being done every second. So every second I got count of users name into Elasticsearch like:

user1
user1
user1
user2
user3
user3
etc…

There could be a lot of users that are connected from different places and I need to count them. From the example above we can see that there is occurrence of user1 3 times, user2 1 time and user 3 2 times: (user1:3, user2:1, user3:2). As the data are recorded every second there is not much difference between the value of occurence but if I query it in Grafana the numbers add up every second so after 2-3 second there could be something like (user1:9, user2:3, user3:6) but the exact numbers are still (user1:3, user2:1, user3:2).

Is there any option to aggreage the data over a minute. I mean in minute interval see the value of 1 second record? I could ingest the data every minute but I need to do it every second for another dashboard with the same datasource.

Maybe there is possibility to ingest data with one second schedule period to one index and with one minute perion into another index?

GET /mariadb_processlist1/_search?
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "now-1m"
      }
    }
  }, 
  "aggs": {
    "NAME": {
      "terms": {
        "field": "user.keyword",
        "size": 15
      }
    }
  }
}

output is

 "buckets" : [
    {
      "key" : "user1",
      "doc_count" : 3900
    },
    {
      "key" : "user2",
      "doc_count" : 3180
    },
    {
      "key" : "user3",
      "doc_count" : 2520
    },
    {
      "key" : "user4",
      "doc_count" : 1020
    },
    {
      "key" : "user5",
      "doc_count" : 420
    },

but for one second period it's:

  "buckets" : [
    {
      "key" : "user1",
      "doc_count" : 65
    },
    {
      "key" : "user2",
      "doc_count" : 53
    },
    {
      "key" : "user3",
      "doc_count" : 42
    },
    {
      "key" : "user4",
      "doc_count" : 17
    },
    {
      "key" : "user5",
      "doc_count" : 11
    },

I know it’s not as clear as it could be so if there is any problem with understanding I could clarify!

Thanks for any piece of advice.