Search list of errors present in a table and aggregate that from another table

[Ravi_Shanker_Reddy]

I have a index named errors and different types like network, system, user etc.. I have another index which is maintaining all the days data EX: smsc-2017.03.23

Can I able to get all system errors from the smsc-2017.03.23 within a hour interval along with its count???

Or any other way to read the errors from falt file and search??? Any suggestions are great. Thanks

[warkolm (Mark Walkom)]

@Ravi_Shanker_Reddy

Can I able to get all system errors from the smsc-2017.03.23 within a hour interval along with its count???

Sure, just run a date aggregation with a filter for errors - Date Histogram Aggregation | Elasticsearch Guide [5.3] | Elastic

@Ravi_Shanker_Reddy

Or any other way to read the errors from falt file and search???

Load them with Logstash?

[Ravi_Shanker_Reddy]

@warkolm

Sure, just run a date aggregation with a filter for errors

@warkolm You are misunderstanding. If you are aggregating you will get all errors. But I need only network errors.

@Ravi_Shanker_Reddy

I have a index named errors and in that index different types(tables) like network, system, user etc..

Get all the errors and filter the network errors which are saved in the another type(table).

mysql> select * from error_list;
+--------------------------------------------+---------------+
| name                                       | type          |
+--------------------------------------------+---------------+
| SMSC_TM_IM_unspecified_error_cause         | network_error |
| SMSC_TM_IM_Unspecified_command_error       | network_error |
| SMSC_TM_IM_Command_cannot_be_actioned      | network_error |
| SMSC_PR_LC_LOCAL_ERROR_MAX_LENGTH_EXCEEDED | system_error  |
+--------------------------------------------+---------------+

mysql> select * from user_data;
+------+--------------------------------------------+--------------+
| name | error                                      | callednumber |
+------+--------------------------------------------+--------------+
| SMSC | SMSC_PR_LC_LOCAL_ERROR_MAX_LENGTH_EXCEEDED | 919716099155 |
| SMSC | SMSC_TM_IM_Unspecified_command_error       | 919716099155 |
| SMSC | SMSC_TM_IM_unspecified_error_cause         | 919716099155 |
| SMSC | SMSC_TM_IM_unspecified_error_cause         | 919716099155 |
| SMSC | SMSC_TM_IM_unspecified_error_cause         | 919716099155 |
| SMSC | SMSC_TM_IM_unspecified_error_cause         | 919716099155 |
| SMSC | SMSC_TM_IM_unspecified_error_cause         | 919716099155 |
| SMSC | SMSC_TM_IM_unspecified_error_cause         | 919716099155 |
| SMSC | SMSC_TM_IM_unspecified_error_cause         | 919716099155 |
| SMSC | SMSC_TM_IM_unspecified_error_cause         | 919716099155 |
| SMSC | SMSC_TM_IM_Command_cannot_be_actioned      | 919716099155 |
| SMSC | SMSC_PR_LC_LOCAL_ERROR_MAX_LENGTH_EXCEEDED | 919716099155 |
| SMSC | SMSC_PR_LC_LOCAL_ERROR_MAX_LENGTH_EXCEEDED | 919716099155 |
| SMSC | SMSC_PR_LC_LOCAL_ERROR_MAX_LENGTH_EXCEEDED | 919716099155 |
+------+--------------------------------------------+--------------+

mysql> select err.name,count(user.error) as error_count from error_list err join user_data user on err.name=user.error where err.type="network_error" group by err.name;

I need similar query in elasticsearch.

[Ravi_Shanker_Reddy]

https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html#_terms_lookup_twitter_example

Solved like this