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


(Ravi Shanker Reddy) #1

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


(Mark Walkom) #2

Sure, just run a date aggregation with a filter for errors - https://www.elastic.co/guide/en/elasticsearch/reference/5.3/search-aggregations-bucket-datehistogram-aggregation.html

Load them with Logstash?


(Ravi Shanker Reddy) #3

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

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) #4

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

Solved like this


(system) #5

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