# Filter results using group by and then aggregate into different buckets **URL:** \ **Category:** Kibana\ **Created:** [April 16, 2019, 5:50pm UTC](https://discuss.elastic.co/t/filter-results-using-group-by-and-then-aggregate-into-different-buckets/177133 "2019-04-16T17:50:19Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![jaimin161](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@jaimin161](https://discuss.elastic.co/u/jaimin161)\ **Post date:** [April 16, 2019, 5:50pm UTC](https://discuss.elastic.co/t/filter-results-using-group-by-and-then-aggregate-into-different-buckets/177133/1 "2019-04-16T17:50:19Z")
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. ---
**Author:** ![jaimin161](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@jaimin161](https://discuss.elastic.co/u/jaimin161)\ **Post date:** [April 16, 2019, 5:57pm UTC](https://discuss.elastic.co/t/filter-results-using-group-by-and-then-aggregate-into-different-buckets/177133/2 "2019-04-16T17:57:17Z")
**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](http://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](http://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](http://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](http://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](http://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](http://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](http://qa-db023.xx.xxxx.com)","START\_TIME":"Wed Apr 10 08:19:46 PDT 2019","TYPE":"NLMigrateControlThread","VERSION":"2019.1.0","STATUS":" **Begin**"}}} ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 14, 2019, 5:57pm UTC](https://discuss.elastic.co/t/filter-results-using-group-by-and-then-aggregate-into-different-buckets/177133/3 "2019-05-14T17:57:41Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.