Filter results using group by and then aggregate into different buckets

Elastic Stack Kibana
1

I am logging Start and End events of each DB upgrade into ELK.

  • Start Event Status = Begin
  • End Event Status = Complete, Error

As various DBs start getting upgraded, we log their Start Event with Status = Begin. And as their upgrade ends, we log status as either Complete or Error depending on outcome of the upgrade. Here Status as Complete meaning it's a successful upgrade.

Now I have 2 requirements:

  1. At a given time, get Count of

  2. Total # DBs that are Successful (Total # Events where Status = Complete)

  3. Total# DBs that have failed (Total # Events where Status = Error)

  4. Total# DBs that IN PROGRESS (Total # Events which has Status = Begin, but doesn’t have corresponding End Event with Status as either Complete or Error)

  5. For IN PROGRESS DBs, build various visualizers.

Problem :

  • I’m unable to get Total # DBs that are IN PROGRESS . (Total # Events which has Status = Begin, but doesn’t have corresponding End Event with Status as either Complete or Error)
  • I’m unable to filter DBs that are exclusively IN PROGRESS.
2

Sample:

With this sample, I should get

of DBs with Success = 2

of DBs with Error = 1

of DBs IN PROGRESS = 1

{"epoch":"1554935120","@timestamp":"2019-04-10T12:19:47.182Z","_id":"ddb3ddd0-70f1-45df-b650-9f96dc48530c-2d323031392e30342e3130888","sMessage":"[MigrateControl] for [ 88888 ]","sAppVersion":"2019.1.0.61","sLoggerName":"mig-log","sDcId":"001","sMolecule":"F","logParameterFields":{"ints":{"START_SIZE_GB":4168,"DB_NKEY": 88888 },"strings":{"CTLFILE":"/opt/deploy/netledger-all/xxxx/WEB-INF/sql/migrates/2019_1_0/migrate_control.xml","DB_HOST":"qa-db020.xx.xxxx.com","START_TIME":"Wed Apr 10 05:19:46 PDT 2019","TYPE":"NLMigrateControlThread","VERSION":"2019.1.0","STATUS":" Begin "}}}

{"epoch":"1554935158","@timestamp":"2019-04-10T17:55:10.883Z","_id":"eb457bb8-0411-4d17-8bca-bfc703c43cb8-2d323031392e30342e3130888","sMessage":"[MigrateControl] for [ 88888 ]","sAppVersion":"2019.1.0.61","sLoggerName":"mig-log","sDcId":"001","sMolecule":"F","logParameterFields":{"ints":{"DURATION_SECS":20123,"END_SIZE_GB":4168,"START_SIZE_GB":4168,"DB_NKEY":88888},"strings":{"CTLFILE":"/opt/deploy/netledger-all/netledger/WEB-INF/sql/migrates/2019_1_0/migrate_control.xml","DB_HOST":"qa-db020.xx.xxxx.com","END_TIME":"Wed Apr 10 10:55:10 PDT 2019","START_TIME":"Wed Apr 10 05:19:46 PDT 2019","TYPE":"NLMigrateControlThread","VERSION":"2019.1.0","STATUS":" Complete "}}

{"epoch":"1554935220","@timestamp":"2019-04-10T13:19:47.182Z","_id":"ddb3ddd0-70f1-45df-b650-9f96dc48530c-2d323031392e30342e3130777","sMessage":"[MigrateControl] for [ 77777 ]","sAppVersion":"2019.1.0.61","sLoggerName":"mig-log","sDcId":"001","sMolecule":"F","logParameterFields":{"ints":{"START_SIZE_GB":4168,"DB_NKEY": 77777 },"strings":{"CTLFILE":"/opt/deploy/netledger-all/xxxx/WEB-INF/sql/migrates/2019_1_0/migrate_control.xml","DB_HOST":"qa-db021.xx.xxxx.com","START_TIME":"Wed Apr 10 06:19:46 PDT 2019","TYPE":"NLMigrateControlThread","VERSION":"2019.1.0","STATUS":" Begin "}}}

{"epoch":"1554935258","@timestamp":"2019-04-10T18:55:10.883Z","_id":"eb457bb8-0411-4d17-8bca-bfc703c43cb8-2d323031392e30342e3130777","sMessage":"[MigrateControl] for [ 77777 ]","sAppVersion":"2019.1.0.61","sLoggerName":"mig-log","sDcId":"001","sMolecule":"F","logParameterFields":{"ints":{"DURATION_SECS":20123,"END_SIZE_GB":4168,"START_SIZE_GB":4168,"DB_NKEY": 77777 },"strings":{"CTLFILE":"/opt/deploy/netledger-all/netledger/WEB-INF/sql/migrates/2019_1_0/migrate_control.xml","DB_HOST":"qa-db021.xx.xxxx.com","END_TIME":"Wed Apr 10 11:55:10 PDT 2019","START_TIME":"Wed Apr 10 06:19:46 PDT 2019","TYPE":"NLMigrateControlThread","VERSION":"2019.1.0","STATUS":" Complete "}}

{"epoch":"1554935320","@timestamp":"2019-04-10T14:19:47.182Z","_id":"ddb3ddd0-70f1-45df-b650-9f96dc48530c-2d323031392e30342e3130666","sMessage":"[MigrateControl] for [ 66666 ]","sAppVersion":"2019.1.0.61","sLoggerName":"mig-log","sDcId":"001","sMolecule":"F","logParameterFields":{"ints":{"START_SIZE_GB":4168,"DB_NKEY": 66666 },"strings":{"CTLFILE":"/opt/deploy/netledger-all/xxxx/WEB-INF/sql/migrates/2019_1_0/migrate_control.xml","DB_HOST":"qa-db022.xx.xxxx.com","START_TIME":"Wed Apr 10 07:19:46 PDT 2019","TYPE":"NLMigrateControlThread","VERSION":"2019.1.0","STATUS":" Begin "}}}

{"epoch":"1554935358","@timestamp":"2019-04-10T19:55:10.883Z","_id":"eb457bb8-0411-4d17-8bca-bfc703c43cb8-2d323031392e30342e3130666","sMessage":"[MigrateControl] for [ 66666 ]","sAppVersion":"2019.1.0.61","sLoggerName":"mig-log","sDcId":"001","sMolecule":"F","logParameterFields":{"ints":{"DURATION_SECS":20123,"END_SIZE_GB":4168,"START_SIZE_GB":4168,"DB_NKEY": 66666 },"strings":{"CTLFILE":"/opt/deploy/netledger-all/netledger/WEB-INF/sql/migrates/2019_1_0/migrate_control.xml","DB_HOST":"qa-db022.xx.xxxx.com","END_TIME":"Wed Apr 10 12:55:10 PDT 2019","START_TIME":"Wed Apr 10 07:19:46 PDT 2019","TYPE":"NLMigrateControlThread","VERSION":"2019.1.0","STATUS":" Error "}}

{"epoch":"1554935420","@timestamp":"2019-04-10T14:19:47.182Z","_id":"ddb3ddd0-70f1-45df-b650-9f96dc48530c-2d323031392e30342e3130555","sMessage":"[MigrateControl] for [ 55555 ]","sAppVersion":"2019.1.0.61","sLoggerName":"mig-log","sDcId":"001","sMolecule":"F","logParameterFields":{"ints":{"START_SIZE_GB":4168,"DB_NKEY": 55555 },"strings":{"CTLFILE":"/opt/deploy/netledger-all/xxxx/WEB-INF/sql/migrates/2019_1_0/migrate_control.xml","DB_HOST":"qa-db023.xx.xxxx.com","START_TIME":"Wed Apr 10 08:19:46 PDT 2019","TYPE":"NLMigrateControlThread","VERSION":"2019.1.0","STATUS":" Begin "}}}

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.