CN115599656A - Monitoring alarm realization method based on Java self-defined SQL - Google Patents
Monitoring alarm realization method based on Java self-defined SQL Download PDFInfo
- Publication number
- CN115599656A CN115599656A CN202211587586.7A CN202211587586A CN115599656A CN 115599656 A CN115599656 A CN 115599656A CN 202211587586 A CN202211587586 A CN 202211587586A CN 115599656 A CN115599656 A CN 115599656A
- Authority
- CN
- China
- Prior art keywords
- sql
- alarm
- execution
- configuration item
- list
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 47
- 238000012544 monitoring process Methods 0.000 title claims abstract description 27
- 230000002159 abnormal effect Effects 0.000 claims abstract description 12
- 230000014509 gene expression Effects 0.000 claims description 8
- 238000012423 maintenance Methods 0.000 abstract description 10
- 238000010586 diagram Methods 0.000 description 2
- 230000000903 blocking effect Effects 0.000 description 1
- 238000004140 cleaning Methods 0.000 description 1
- 230000002354 daily effect Effects 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 230000003203 everyday effect Effects 0.000 description 1
- 238000000605 extraction Methods 0.000 description 1
- 238000007429 general method Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 239000010453 quartz Substances 0.000 description 1
- VYPSYNLAJGMNEJ-UHFFFAOYSA-N silicon dioxide Inorganic materials O=[Si]=O VYPSYNLAJGMNEJ-UHFFFAOYSA-N 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 230000003442 weekly effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/34—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment
- G06F11/3466—Performance evaluation by tracing or monitoring
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/32—Monitoring with visual or acoustical indication of the functioning of the machine
- G06F11/324—Display of status information
- G06F11/327—Alarm or error message display
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Quality & Reliability (AREA)
- Computer Hardware Design (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Debugging And Monitoring (AREA)
Abstract
The invention provides a method for realizing monitoring alarm based on Java self-defined SQL, which comprises the following steps: s1, a user side creates different projects according to different tenants to generate tenant IDs, creates one or more service database connection string configurations according to the different projects, and connects service databases through jdbc; creating one or more SQL configuration items, taking one created service database connection string as a service database connection address, and selecting an available database name in the service database connection string configuration to form a jdbc connection query minimum unit; by realizing the automatic operation and maintenance of the service system, the abnormal condition of the service data is alarmed in time, the labor input is reduced, the operation and maintenance cost is saved, meanwhile, the response can be carried out in time, and the expansion of the fault influence is prevented.
Description
Technical Field
The invention relates to the technical field of service system computer maintenance, in particular to a monitoring alarm realization method based on Java self-defined SQL.
Background
In the process of using the service system, operation and maintenance personnel often need to query and compare service data so as to monitor whether the service system normally operates and judge whether the generated service data is correct.
The general method is that operation and maintenance personnel manually inquire the service data regularly through a database client tool and manually judge the accuracy of the service data. The operation and maintenance method is labor-wasting, low in efficiency, and the people need to be on duty at night, and abnormal conditions cannot be found in time.
Disclosure of Invention
The technical problem to be solved by the invention is to provide a method for realizing monitoring alarm based on Java self-defined SQL, which can solve the problem of high cost of operation and maintenance of the traditional business system, aiming at the defects of the technical scheme.
The invention provides a method for realizing monitoring alarm based on Java self-defined SQL, which comprises the following steps:
s1, a user side creates different projects according to different tenants to generate tenant IDs, creates one or more service database connection string configurations according to the different projects, and connects service databases through jdbc; creating one or more SQL configuration items, taking one of the created business database connection strings as a business database connection address, and selecting an available database name in the business database connection string configuration to form a jdbc connection query minimum unit;
s2, querying the tenant ID, the service database connection string and the SQL configuration which are created in the step S1 through a micro-service API (application program interface), obtaining an SQL configuration item list and caching the SQL configuration item list in the local; when the tenant ID, the service database connection string and the SQL configuration in the step S1 are newly added, modified and deleted, the Job task is correspondingly updated to obtain a latest SQL configuration item list, when a timing task is started, a request micro-service API interface is executed once every n minutes to inquire all currently configured SQL configuration item lists in a starting state, and the locally cached SQL configuration item list and the latest SQL configuration item list are compared and updated;
s3, the Job task executes a user-defined SQL statement or a storage process according to the configured cron time expression frequency and simultaneously queries a business database, subtracts SQL execution starting time from SQL execution ending time, calculates SQL execution time consumption, judges whether SQL is successfully executed according to whether SQL execution is abnormal or not, records an execution log if the SQL execution is failed, and stores the execution log to a MySQL database;
s4, receiving an SQL execution result sent by the Job task, writing the SQL execution result into an alarm queue, and storing the SQL execution result into a server;
s5, judging whether the SQL execution result written into the alarm queue exceeds a threshold value or not, and if so, sending an SQL execution result alarm;
s6, reading an SQL execution result object from the alarm queue, inquiring an alarm medium list bound by the SQL configuration item according to the SQL configuration item ID in the SQL execution result alarm object in the alarm queue, generating alarm information, sequentially sending the alarm information to media in the alarm medium list, writing the SQL execution result into an excel table, wherein each SQL execution result is used as a tab, and compressing the excel document into a zip file to be sent as a mail attachment.
In the method for realizing monitoring alarm based on Java self-defined SQL, each SQL configuration item in the step S1 comprises a main SQL, and no more than 10 accessory SQL can be configured.
In the method for realizing monitoring alarm based on Java self-defined SQL, in the step S1, the SQL configuration items comprise database connection string addresses, user names, passwords, available database names, enabling/disabling states, execution time expressions, fusing times, fusing duration, alarm media, sending modes and mail texts, the service database connection string addresses support Mysql, oracle and sqlsever, and the jdbc connection query minimum unit comprises the database connection string addresses, the user names, the passwords, the available database names, SQL sentences or storage processes.
In the method for implementing monitoring alarm based on Java custom SQL, the step S2 further includes the following substeps:
s21, comparing and updating the SQL configuration item list and the latest SQL configuration item list, and deleting the configuration item from the cache for the configuration item which does not exist in the latest list;
s22, comparing and updating the SQL configuration item list with the latest SQL configuration item list, and adding the configuration item in the cache for the configuration item existing in the latest list but not existing in the cache;
s23, comparing and updating the SQL configuration item list with the latest SQL configuration item list, caching and updating the configuration item when the updating time in the latest list is greater than that of the configuration item in the cache, and setting the updating time in the cache as the current time after the updating is finished;
and S24, comparing and updating the SQL configuration item list with the latest SQL configuration item list, wherein when the updating time in the latest list is the same as the updating time in the cache, the cache is unchanged.
In the method for implementing monitoring alarm based on Java custom SQL described in the present invention, in step S3, the STATUS field is agreed in the SQL statement as the basis for determining alarm, the user may compare the threshold of any table field in the SQL statement, then represent whether alarm is present in the STATUS field, and set the alarm threshold of custom SQL, when the SQL execution result is converted into List < Map < String, object > Object storage cache, traverse List < Map < String, object >, if Map < String, object > has STATUS field, and STATUS field in any row is not 0, where 0 represents that the threshold is not exceeded, 1 represents that the threshold is exceeded, and 2 represents that the threshold is exceeded and the alarm is severe, when the WARN field is assigned as 1, 0 represents that no alarm is present, 1 represents that there is alarm, and 1 represents that there is alarm in the SQL execution result.
In the method for realizing the monitoring alarm based on the Java self-defined SQL, in the step S3, when the connection of the business database fails due to account password errors and the like or the SQL execution result is abnormal due to self-defined SQL statement syntax errors and the like, the Job task is recorded and fused, when the fusing frequency of the SQL configuration in the step S1 is reached or the consumed time exceeds the fusing time, the Job task is stopped, and the enabled/disabled state of the SQL configuration is updated to be disabled by rules.
In the method for realizing monitoring alarm based on Java self-defined SQL, in the step S3, a UUID generated by executing Job task each time is used as the execution ID of the current execution of the SQL configuration item, after the execution is finished, a micro service API interface is requested, and the execution results of the main SQL and the accessory SQL, the corresponding SQL configuration item ID, the execution ID, whether to alarm the WARN field, the time consumed by executing the SQL, whether to execute the SQL successfully, the execution log, the execution time and the tenant ID are sent and stored to a MySQL database.
In the method for realizing monitoring alarm based on Java self-defined SQL, in the step S4, the execution results of main SQL and accessory SQL are respectively stored in a server, the execution results of the main SQL and the accessory SQL are read from an alarm queue, the SQL execution result List < Map < String, object > is serialized into a JSON data format to generate a txt file name, the txt file name is subjected to MD5 encryption according to the SQL configuration item ID, the execution ID and the ID splicing combination of the accessory SQL to generate a unique code as the txt file name, then the txt file is stored in a server where the micro service is located, a folder directory is generated according to the current date, information such as the file path, the execution ID, whether to alarm the WARN field is required, the SQL is executed in a time-consuming manner or not, whether to execute the execution is successful, an execution log, the execution time, the tenant ID and the like are written into a MySQL database and then stored in a result table, and a user side can display the SQL result of latest execution of each tenant by calling a micro service API interface, and when a certain SQL execution result of the SQL configuration item is selected and the historical execution result of the SQL is checked.
In the method for realizing monitoring alarm based on Java self-defined SQL, a main SQL execution result object needing to SEND alarm IS written into an alarm queue according to whether to SEND the WARN field and how to SEND the IS _ SEND field in the step S5; when WARN =1, the SQL execution result is indicated to have an alarm and is written into an alarm queue; when WARN =0 and IS _ SEND =2, it indicates that the SQL execution result has no alarm, and if it must be sent, it IS written into the alarm queue.
In the method for implementing monitoring alarm based on Java custom SQL, in the step S6, the alarm media include mails, nails, ilinks, and short message messages, wherein mailboxes, short messages, nails, and ilinks can be freely combined and configured according to business needs, and media groups can be added, and the management of the media groups can also freely combine mailboxes, short messages, nails, and ilinks.
The method for realizing monitoring alarm based on Java self-defined SQL realizes automatic operation and maintenance of the service system, alarms abnormal conditions of service data in time, reduces labor input, saves operation and maintenance cost, can respond in time and prevents fault influence from expanding.
Drawings
FIG. 1 is a flow diagram of an embodiment of a method for monitoring alarm implementation based on Java custom SQL.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.
It should be noted that the terms "first," "second," and the like in the description and claims of the present invention and in the drawings described above are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used is interchangeable under appropriate circumstances such that the embodiments of the invention described herein are capable of operation in sequences other than those illustrated or described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed, but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.
As shown in fig. 1, it is a schematic flow diagram of an embodiment of the method for monitoring and implementing an alarm based on Java custom SQL in the present invention. The method for realizing monitoring alarm based on Java self-defined SQL comprises the following steps:
in step S1, the user side creates different items according to different tenants, generates tenant IDs, and each tenant is isolated from each other. Establishing one or more service database connection string configurations according to different projects, and connecting the service databases through jdbc; creating one or more SQL configuration items, taking one created service database connection string as a service database connection address, and selecting an available database name in the service database connection string configuration to form a jdbc connection query minimum unit; the tenant information, the database connection string and the SQL configuration are stored in a MySQL database table.
In step S2, querying the tenant ID, the service database connection string and the SQL configuration created in step S1 through a micro service API interface to obtain an SQL configuration item list and caching the SQL configuration item list in the local; when tenant ID, service database connection string and SQL configuration in the step S1 are newly added, modified and deleted, the Job task is correspondingly updated to obtain a latest SQL configuration item list, when a timing task is started, a request micro-service API interface is executed once every n minutes to inquire all SQL configuration item lists in the current configuration starting state, and the SQL configuration item list cached in the local is compared with the latest SQL configuration item list for updating; wherein, the minimum unit is inquired according to the jdbc connection to start a Job task, if a plurality of units start a plurality of quartz Job tasks.
In step S3, the Job task executes a custom SQL statement or a storage process according to the configured cron time expression frequency while querying the business database, subtracts SQL execution start time from SQL execution end time, calculates SQL execution time, determines whether SQL is successfully executed according to whether SQL is abnormal, and records an execution log if the execution fails, and stores the execution log in the MySQL database; resolving the cron time expression causes the Job task to be executed at desired intervals, for example: once per day at 8:0 30 8 * *
Once every monday 8:0 30 8
* Mon, performed once every day at 30 min intervals from 8:0 0/30 8-18 * *
. The cron time expression has strong flexibility to freely configure the interval minutes, hours, days, weeks, months.
In step S4, receiving an SQL execution result sent by the Job task, writing the SQL execution result into an alarm queue, and storing the SQL execution result into a server; and receiving the SQL execution result sent by the Job task by providing a micro service API interface, and writing the SQL execution result into a queue to prevent blocking in order to avoid abnormal reception caused by large concurrency.
In step S5, judging whether the SQL execution result written into the alarm queue exceeds a threshold value, and if so, sending an SQL execution result alarm;
in step S6, an SQL execution result object is read from the alarm queue, an alarm medium list bound by the SQL configuration item is queried according to the SQL configuration item ID in the SQL execution result alarm object in the alarm queue, alarm information is generated, the SQL execution result is sequentially sent to media in the alarm medium list, and the SQL execution result is written into an excel table, where each SQL execution result is used as a tab, and an excel document is compressed into a zip file and sent as a mail attachment. The mail, the nail and the iLink can share the self-defined mail text content as one part of the alarm information, and the short message only sends main alarm information due to length limitation. And after the alarm is sent, updating an alarm result table, and recording a medium list of successful sending and a medium list of failed sending or not sending as an auditing basis. Mail attachments do not exceed 5M at maximum. Wherein the iLink is independently developed IM software similar to nails.
In one embodiment, each SQL configuration item in step S1 contains one main SQL, while no more than 10 attachment SQL may be configured.
In one embodiment, in step S1, the SQL configuration item includes a database connection string address, a user name, a password, an available database name, an enabling/disabling state, an execution time expression, a fusing number, a fusing duration, an alarm medium, a sending method, and a mail body, the service database connection string address supports Mysql, oracle, and sqlsever, and the jdbc connection query minimum unit includes a database connection string address, a user name, a password, an available database name, an SQL statement, or a storage procedure. The sending mode comprises all sending, alarm sending and non-sending.
In an embodiment, step S2 further comprises the following sub-steps:
in step S21, comparing and updating the SQL configuration item list with the latest SQL configuration item list, and deleting a configuration item that does not exist in the latest list from the cache;
in step S22, comparing and updating the SQL configuration item list with the latest SQL configuration item list, and adding a configuration item to the cache for a configuration item existing in the latest list but not existing in the cache;
in step S23, the SQL configuration item list and the latest SQL configuration item list are updated by comparison, when the update time in the latest list is greater than the configuration item of the update time in the cache, the configuration item is cached and updated, and after the update is completed, the update time in the cache is set as the current time;
in step S24, the SQL configuration item list and the latest SQL configuration item list are updated in a comparison manner, and when the update time in the latest list is the same as the update time in the cache, the cache is unchanged.
In an embodiment, in step S3, a STATUS field is agreed in the SQL statement as a basis for determining an alarm, and the user may perform threshold comparison on any table field in the SQL statement, then represent whether the alarm is in the STATUS field, and set an alarm threshold of the custom SQL, when the SQL execution result is converted into List < Map < String, object > Object storage cache, traverse List < Map < String, object > >, if Map < String, object > has the STATUS field, and the STATUS field of any row is not 0, where 0 represents that the threshold is not exceeded, 1 represents that the threshold is exceeded, and 2 represents that the threshold is exceeded and serious, then the alarm field WARN is assigned as 1, 0 represents that the alarm is absent, and 1 represents that the alarm is present in the SQL execution result.
In an embodiment, in step S3, when the connection of the service database fails due to an account password error or the like, or when the SQL execution result is abnormal due to a custom SQL statement syntax error or the like, the Job task is recorded to be fused, and when the number of fusing times of the SQL configuration in step S1 is reached or the consumed time exceeds the fusing time, the Job task is stopped, and the enabled/disabled state of the SQL configuration is updated to be disabled by the rule. In order to ensure the robustness of the Job task, the default value of the fusing times is set to be 3, the configuration of the fusing times is not more than 10 times at most, the Job task is forcibly stopped after the execution fails for more than 10 times, and the SQL configuration item is updated to be in a state of being forbidden by rules.
In an embodiment, in step S3, a UUID is generated for each execution of the Job task as an execution ID of the current execution of the SQL configuration item, and after the execution is completed, a microservice API interface is requested, and the execution results of the main SQL and the accessory SQL, the corresponding SQL configuration item ID, the execution ID, whether to alarm the WARN field, the time consumed for executing the SQL, whether to execute the SQL successfully, the execution log, the execution time, and the tenant ID are sent and stored to the MySQL database.
In one embodiment, in step S4, the execution results of the main SQL and the attached SQL are stored in the server, and the execution results of the main SQL and the attached SQL are read from the alarm queue, the SQL execution result List < Map < String, object > > Object is serialized into the JSON data format to generate the txt file name, wherein the txt file name is generated by MD5 encryption according to the SQL configuration item ID, the execution ID, and the ID concatenation combination of the attached SQL as a unique code and then stored in the server where the micro-service is located, and generating a folder directory according to the current date, writing information such as a file path, an execution ID, whether to alarm WARN field, SQL execution time consumption, whether to execute successfully, an execution log, execution time, tenant ID and the like into a MySQL database, and then storing the information in a result table, wherein a user side can display the latest executed SQL result of each tenant by calling a micro-service API (application program interface), and can check the historical execution result of the SQL configuration item when a certain SQL execution result is selected.
In an embodiment, in step S5, according to whether to alarm the WARN field and how to SEND the IS _ SEND field, writing the main SQL execution result object that needs to SEND an alarm into the alarm queue; when WARN =1, it indicates that the SQL execution result has an alarm and writes the alarm into an alarm queue; when WARN =0 and IS _ SEND =2, it indicates that the SQL execution result has no alarm, and if it must be sent, it IS written into the alarm queue. The method aims to solve the problem that the data volume of the SQL execution result is too large.
In an embodiment, the alarm media in step S6 include mails, nails, ilinks, and short messages, wherein mailboxes, short messages, nails, and ilinks can be freely combined and configured according to the service requirement, and media groups can be added, and the media group management can also freely combine mailboxes, short messages, nails, and ilinks.
Specifically, the address of the MySQL database connection string is jdbc: mySQL://127.0.0.1
}serverTimezone=Asia/Shanghai&useUnicode = true, oracle database connection string address samples are jdbc: oracle: thin: @ 127.0.0.1. And the dbName in the database connection string address is selected by the configured 'available database name'.
The excel table is designed to be a latest record table t _ sys _ db _ exeresults and a history record table t _ sys _ db _ exeresults _ his, the latest record table stores the latest execution record of each configuration main SQL, the history record table stores all the execution records of the main SQL and the attachment SQL, the history record sets the number of reserved days, and the method is automatically cleaned. Customizing SQL query results, converting the serialization into json character strings, writing the jst character strings into txt files, creating folders according to dates, storing the folders in a server served by a monitor, setting the number of reserved days and automatically cleaning.
And analyzing and judging whether an alarm is needed or not through the main SQL execution result, wherein the SQL statement requires to contain a STATUS field, wherein STATUS =0 represents normal, STATUS =1 represents abnormal, STATUS =2 represents serious problems, and abnormal and serious states belong to alarms. When the alarm is needed to send the mail, the main SQL execution result is used as the mail text and can be configured in a user-defined mode, the SQL execution results of all the attachments are used for generating excels which are used as mail attachments, and one attachment SQL result corresponds to one excel tab.
When the SQL configuration item is in a configurable non-sending state, an alarm sending state or a full sending state, no matter whether the SQL execution result has an alarm or not, the SQL configuration item is not sent. When the SQL execution result is sent, the SQL execution result is sent when the alarm exists. When all the SQL messages are sent, no matter whether the SQL execution result has an alarm or not, the SQL message is sent, the state can be used for scenes of daily report, weekly report or monthly report extraction, and business data are fed back to all alarm media regularly.
The alarm media can freely combine and configure the mailbox, the short message, the nail and the iLink according to the service requirement, and simultaneously, a media group can be added, so that the service cross management of operation and maintenance personnel is facilitated, and the mailbox, the short message, the nail and the iLink can also be freely combined by the media group management. Wherein
It should be noted that, for simplicity of description, the above-mentioned method embodiments are described as a series of acts or combination of acts, but those skilled in the art will recognize that the present invention is not limited by the order of acts, as some steps may occur in other orders or concurrently in accordance with the invention. Further, those skilled in the art will appreciate that the embodiments described in this specification are presently preferred and that no acts or modules are required by the invention.
Through the above description of the embodiments, those skilled in the art can clearly understand that the method according to the above embodiments can be implemented by software plus a necessary general hardware platform, and certainly can also be implemented by hardware, but the former is a better implementation mode in many cases. Based on such understanding, the technical solutions of the present invention may be embodied in the form of a software product, which is stored in a storage medium (e.g., ROM/RAM, magnetic disk, optical disk) and includes instructions for enabling a terminal device (e.g., a mobile phone, a computer, a server, or a network device) to execute the method according to the embodiments of the present invention.
Therefore, the above description is only a preferred embodiment of the present invention, and the scope of the present invention is not limited thereto, and any changes or substitutions that can be easily conceived by those skilled in the art within the technical scope of the present invention will be covered within the scope of the present invention, and the scope of the present invention shall be subject to the protection scope of the appended claims.
Claims (10)
1. A monitoring alarm implementation method based on Java self-defined SQL is characterized by comprising the following steps:
s1, a user side creates different projects according to different tenants to generate tenant IDs, creates one or more service database connection string configurations according to the different projects, and connects service databases through jdbc; creating one or more SQL configuration items, taking one created service database connection string as a service database connection address, and selecting an available database name in the service database connection string configuration to form a jdbc connection query minimum unit;
s2, querying the tenant ID, the service database connection string and the SQL configuration which are created in the step S1 through a micro-service API (application program interface), obtaining an SQL configuration item list and caching the SQL configuration item list in the local; when tenant ID, service database connection string and SQL configuration in the step S1 are newly added, modified and deleted, the Job task is correspondingly updated to obtain a latest SQL configuration item list, when a timing task is started, a request micro-service API interface is executed once every n minutes to inquire all SQL configuration item lists in the current configuration starting state, and the SQL configuration item list cached in the local is compared with the latest SQL configuration item list for updating;
s3, the Job task executes a user-defined SQL statement or a storage process according to the configured cron time expression frequency and simultaneously queries a business database, subtracts SQL execution starting time from SQL execution ending time, calculates SQL execution time consumption, judges whether SQL is successfully executed according to whether SQL execution is abnormal or not, records an execution log if the SQL execution is failed, and stores the execution log to a MySQL database;
s4, receiving an SQL execution result sent by the Job task, writing the SQL execution result into an alarm queue, and storing the SQL execution result into a server;
s5, judging whether the SQL execution result written into the alarm queue exceeds a threshold value or not, and if so, sending an SQL execution result alarm;
s6, reading an SQL execution result object from the alarm queue, inquiring an alarm medium list bound by the SQL configuration item according to the SQL configuration item ID in the SQL execution result alarm object in the alarm queue, generating alarm information, sequentially sending the alarm information to media in the alarm medium list, writing the SQL execution result into an excel table, wherein each SQL execution result is used as a tab, and compressing the excel document into a zip file to be sent as a mail attachment.
2. The method for monitoring alarm implementation based on Java custom SQL according to claim 1, wherein in the step S1, each SQL configuration item comprises a main SQL, and no more than 10 accessory SQL can be configured.
3. The method for realizing monitoring alarm based on Java self-defined SQL (structured query language) of claim 2, wherein in the step S1, the SQL configuration items comprise database connection string addresses, user names, passwords, available database names, enabling/disabling states, execution time expressions, fusing times, fusing duration, alarm media, sending modes and mail texts, the service database connection string addresses support Mysql, oracle and sqlsever, and the jdbc connection query minimum unit comprises the database connection string addresses, the user names, the passwords, the available database names, SQL statements or storage procedures.
4. The method for monitoring alarm implementation based on Java custom SQL as claimed in claim 3, wherein the step S2 further comprises the following sub-steps:
s21, comparing and updating the SQL configuration item list with the latest SQL configuration item list, and deleting the configuration item from the cache for the configuration item which does not exist in the latest list;
s22, comparing and updating the SQL configuration item list with the latest SQL configuration item list, and adding the configuration item in the cache for the configuration item existing in the latest list but not existing in the cache;
s23, comparing and updating the SQL configuration item list with the latest SQL configuration item list, caching and updating the configuration item when the updating time in the latest list is greater than that of the configuration item in the cache, and setting the updating time in the cache as the current time after the updating is finished;
and S24, comparing and updating the SQL configuration item list with the latest SQL configuration item list, wherein when the updating time in the latest list is the same as the updating time in the cache, the cache is unchanged.
5. The method for monitoring alarm implementation based on Java custom SQL as claimed in claim 4, wherein in step S3, the STATUS field is agreed in the SQL statement as the basis for determining the alarm, the user can perform threshold comparison on any table field in the SQL statement, then embody whether the alarm is in the STATUS field, and set the alarm threshold of the custom SQL, when the SQL execution result is converted into List < Map < String, object > > Object storage cache, traverse List < Map < String, object > >, if Map < String, object > has the STATUS field, and the STATUS field in any row is not 0, where 0 indicates that the threshold is not exceeded, 1 indicates that the threshold is exceeded, 2 indicates that the threshold is exceeded and is severe, the alarm field WARN value is 1, 0 indicates no alarm, and 1 indicates that there is an alarm in the SQL execution result.
6. The method for implementing monitoring alarm based on Java custom SQL according to claim 5, wherein in the step S3, when a connection failure occurs due to an account password error or the like in connection with a service database or an SQL execution result is abnormal due to a custom SQL statement syntax error or the like, the Job task is recorded and fused, and when the fusing frequency or time consumption of the SQL configuration in the step S1 exceeds the fusing duration, the Job task is stopped, and the enabled/disabled state of the SQL configuration is updated to be disabled by a rule.
7. The method according to claim 6, wherein in step S3, a UUID is generated for each execution of the Job task as an execution ID for the execution of the SQL configuration item, and after the execution is completed, a microservice API interface is requested, and the execution results of the main SQL and the accessory SQL, the corresponding SQL configuration item ID, the execution ID, whether to alarm the WARN field, the SQL consumed time, whether to execute successfully, the execution log, the execution time, and the tenant ID are sent to and stored in the MySQL database.
8. The method for realizing monitoring alarm based on Java custom SQL according to claim 6, wherein in step S4, the main SQL and the accessory SQL execution results are respectively stored in a server, and the main SQL and the accessory SQL execution results are read from an alarm queue, the SQL execution results List < Map < String, object > are serialized into a JSON data format to generate a txt filename, wherein the txt filename is encrypted by MD5 according to the configuration item ID, the execution ID, and the ID of the accessory SQL to generate a unique code as the txt filename, then the txt file is stored in a server where the micro service is located, and a folder directory is generated according to the current date, and information such as the file path, the execution ID, whether to alarm the WARN field, the execution SQL is time consuming, whether to execute successfully, the execution log, the execution time, the tenant ID, and the like is written into a MySQL database and then stored in a result table, and the user can display the SQL execution result executed by each tenant by calling a micro service API.
9. The method for implementing monitoring alarm based on Java self-defined SQL according to claim 8, wherein in the step S5, the main SQL execution result object needing to SEND alarm IS written into the alarm queue according to whether to SEND the WARN field and how to SEND the IS _ SEND field; when WARN =1, the SQL execution result is indicated to have an alarm and is written into an alarm queue; when WARN =0 and IS _ SEND =2, it indicates that there IS no alarm in the SQL execution result, and if it must be sent, it IS written into the alarm queue.
10. The method according to claim 9, wherein the warning media in step S6 include mail, nail, iLink, and sms message, and the mailbox, sms, nail, and iLink can be configured and combined freely according to the service requirement, and media group can be added, and the media group management can also combine the mailbox, sms, nail, and iLink freely.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211587586.7A CN115599656A (en) | 2022-12-12 | 2022-12-12 | Monitoring alarm realization method based on Java self-defined SQL |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211587586.7A CN115599656A (en) | 2022-12-12 | 2022-12-12 | Monitoring alarm realization method based on Java self-defined SQL |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115599656A true CN115599656A (en) | 2023-01-13 |
Family
ID=84852949
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211587586.7A Pending CN115599656A (en) | 2022-12-12 | 2022-12-12 | Monitoring alarm realization method based on Java self-defined SQL |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115599656A (en) |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104301413A (en) * | 2014-10-17 | 2015-01-21 | 国云科技股份有限公司 | Oracle distributed real-time monitoring method orienting cloud databases |
| CN105843904A (en) * | 2016-03-23 | 2016-08-10 | 江苏太湖云计算信息技术股份有限公司 | Monitoring alarm system for database operation performance |
| CN108762900A (en) * | 2018-05-21 | 2018-11-06 | 平安科技（深圳）有限公司 | High frequency method for scheduling task, system, computer equipment and storage medium |
| CN109901889A (en) * | 2019-02-25 | 2019-06-18 | 快乐购有限责任公司 | The full link monitoring method of supporting business system O&M based on J2EE platform |
| WO2019223155A1 (en) * | 2018-05-25 | 2019-11-28 | 平安科技（深圳）有限公司 | Sql performance monitoring method and device, computer apparatus, and storage medium |
| CN110716832A (en) * | 2019-09-24 | 2020-01-21 | 腾讯科技（深圳）有限公司 | Service operation monitoring and alarming method, system, electronic equipment and storage medium |
| WO2022099832A1 (en) * | 2020-11-10 | 2022-05-19 | 北京中电普华信息技术有限公司 | Cloud environment-based sql monitoring method and apparatus |
-
2022
- 2022-12-12 CN CN202211587586.7A patent/CN115599656A/en active Pending
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104301413A (en) * | 2014-10-17 | 2015-01-21 | 国云科技股份有限公司 | Oracle distributed real-time monitoring method orienting cloud databases |
| CN105843904A (en) * | 2016-03-23 | 2016-08-10 | 江苏太湖云计算信息技术股份有限公司 | Monitoring alarm system for database operation performance |
| CN108762900A (en) * | 2018-05-21 | 2018-11-06 | 平安科技（深圳）有限公司 | High frequency method for scheduling task, system, computer equipment and storage medium |
| WO2019223155A1 (en) * | 2018-05-25 | 2019-11-28 | 平安科技（深圳）有限公司 | Sql performance monitoring method and device, computer apparatus, and storage medium |
| CN109901889A (en) * | 2019-02-25 | 2019-06-18 | 快乐购有限责任公司 | The full link monitoring method of supporting business system O&M based on J2EE platform |
| CN110716832A (en) * | 2019-09-24 | 2020-01-21 | 腾讯科技（深圳）有限公司 | Service operation monitoring and alarming method, system, electronic equipment and storage medium |
| WO2022099832A1 (en) * | 2020-11-10 | 2022-05-19 | 北京中电普华信息技术有限公司 | Cloud environment-based sql monitoring method and apparatus |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11082489B2 (en) | Method and system for displaying similar email messages based on message contents | |
| CN107678907B (en) | Database service logic monitoring method, system and storage medium | |
| US6617969B2 (en) | Event notification system | |
| US7363630B2 (en) | System and method of intelligent queuing | |
| US8819266B2 (en) | Dynamic file transfer scheduling and server messaging | |
| US20110029625A1 (en) | Method and system for processing email attachments | |
| US20040088141A1 (en) | Automatically identifying replacement times for limited lifetime components | |
| US20040225525A1 (en) | Automatic contacts replication system and software | |
| US20080010350A1 (en) | Email recovery method and system | |
| KR20060031624A (en) | Method and system for alert delivery architecture | |
| US20050055386A1 (en) | Mail server based application record synchronization | |
| US20040054763A1 (en) | Method for minimizing connection time for data synchronization | |
| US9235641B1 (en) | Method and apparatus for archive processing of electronic messages | |
| US10185631B2 (en) | System and method of performing continuous backup of a data file on a computing device | |
| KR100747466B1 (en) | A device management client and device management method using nodes having additional properties | |
| CN115599656A (en) | Monitoring alarm realization method based on Java self-defined SQL | |
| US20230058475A1 (en) | Transaction based processing using asynchronous microservices | |
| CN110263305B (en) | Configuration file comparison method, device, equipment and storage medium | |
| US10248314B2 (en) | Migrate nickname cache for email systems and devices | |
| JP2007034416A (en) | Information processing system, log data management method and program for managing log data | |
| US20080183768A1 (en) | System, method and program for monitoring local mail replicas and local directory replicas for e-mail | |
| US20040243653A1 (en) | System and method for generating an alarm when an abnormal database backup status occurs | |
| US20030200309A1 (en) | Transmission network system | |
| CN117131114A (en) | Customer group list management method, device, terminal equipment and storage medium | |
| CN116225838A (en) | Enterprise-level real-time monitoring method, equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20230113 |