KR100835654B1 - Query processing system and methods for a database with packet information by dividing a table and query - Google Patents

Abstract

An apparatus and a method for searching packet information through DB division and query division for packet information are provided to mirror all packets in packet collecting devices, divide the packet information based on a mirroring time, make a packet information DB, and perform separately queries for requesting the packet information, thereby processing the call flow condition check and performance analysis of the VoIP network in realtime. An apparatus for searching packet information through DB division and query division in realtime comprises a packet information DB(Database)(220), plural packet collecting devices(201), and plural call flow searching terminals(250). The packet information DB is comprised of plural packet information tables with the same structure as a table capable of storing the packet information. Each of the plural packet collecting devices include a packet collecting unit(210) and a packet information generating unit(230). The packet collecting unit are connected to the backbone switch(132) of a VoIP(Voice over Internet Protocol) network(130) to mirror all packets transmitted to the VoIP network and store a generation time of a mirroring time with the mirrored packets. The packet information generating unit extracts information from the collected packets, configures packet information with the generation time of the packets, divides the configured packet information, and stores the divided packet information in each of the packet information tables. The plural call flow searching terminals divide search queries for searching the packet information by packet information table, make the search queries, and request the packet information DB server to respond to the search queries.

Description

Query processing system and methods for a database with packet information by dividing a table and query}

The present invention captures all packets transmitted from an Internet telephone network (VoIP) in real time, divides packet information into a packet information DB, and manages the packet information DB so that packet information can be immediately answered in real time. An apparatus and method for call flow retrieval through DB partitioning and query partitioning are provided.

VoIP is a communication service technology that converts voice data into IP data packets and transmits them through a packet network for data communication (the Internet is a typical network). By using a terminal such as a telephone at the end of the VoIP network, the user can receive the same service as using a regular telephone. The configuration of the VoIP network will be described in more detail with reference to FIG. 1.

In order to provide a service such as communication of a general telephone through a VoIP network, an IP PBX (IP switching device) 110 is usually required. The IP switching device 110 is a device for finding and connecting a call requested by the Internet telephone terminal 135 to the receiving telephone terminal 135. It acts like a PBX in a public switched telephone network (PSTN). In order to perform commercially professional services, a professional operator is provided with a large-capacity IP switching device 110, and connects the IP switching device 110 to the Internet telephone network (VoIP) 130 through the backbone switch 132. When the voice packet of the sender's Internet telephone terminal 135 enters through the VoIP network 130, the switch 110 finds a target receiving Internet telephone terminal 135 and sends the packet. In the case of the general telephone terminal 125, not the terminal 135, the trunk gateway 122 is connected. At the same time, it receives the call from the general telephone terminal coming through the public switched telephone network (PSTN) 120 and connects to the Internet telephone terminal 135.

VoIP converts voice data into packets and transmits them through a data communication packet network. As shown in FIG. 2, packets are relayed by VoIP equipments 150. VoIP equipment 150 typically includes proxy server 115, IP-PBX 110, trunk gateway 122, and the like. The VoIP protocol, which is a protocol that regulates voice data communication of VoIP, has been used in the past for H.323, but recently, its structure is simple. 3 shows a protocol stack of a VoIP system using the SIP protocol. In other words, the VoIP protocol handled by the SIP server is mainly used for the purpose of call setup and the like, and is implemented on UDP, and is implemented on the RTP / RTCP protocol for actual voice data transmission. That is, the former packet consists of <IP header + UDP header + SIP protocol>, and the latter packet consists of <IP header + UDP header + RTP / RTCP header + voice data transmission>. Therefore, by capturing and analyzing the packet, the call related information such as <Call ID>, <Method>, <Transmission Time>, <Telephone Terminal Information>, <Calling Number> It can find out the <source IP / Port> and <destination IP / Port> of devices when they are transmitted between devices. At this time, since the source IP / Port and the destination IP / Port means a port number for the IP and the session of the device is set, it changes every time it is relayed through the VoIP device (150). Transmitting and receiving phone number means a phone number of the corresponding phone terminal 135. In general, Internet phone numbers are represented as “<phone number> @ <domain>”. The telephone terminal information has information such as the product name of the telephone terminal. In addition, the method indicates the types of call setup-related messages by the SIP protocol, and the packet flow for each method shows how the call setup is progressing.

In order to observe whether the packets are well transmitted and received in the VoIP network or whether the call flow is well connected from the terminal to the terminal, the packet analyzer 100 or the packet analyzer is conventionally used. Or is being tried. Usually, in order to temporarily observe the performance or flow of the VoIP network, as shown in FIG. 1, the packet analyzer 100 is connected to the backbone switch 132 to detect packet information. That is, the packet analyzer 100 captures and analyzes all packets passing through the connected backbone switch. However, since the amount of packets to be processed is so large, it is usually used only temporarily to grasp the situation of the VoIP network and cannot be monitored in real time. For example, the current VoIP network of one operator is about 150 million packets per day that need to be captured and analyzed by one backbone switch. As Internet phone service becomes more common, more packet information needs to be processed. In order to solve this problem, as shown in FIG. 4, an attempt has been made to measure all packets in real time by reducing the amount of packets measured by one packet measurer 105 by installing multiple packet measurers (Probe) 105. In general, the packet meter 105 is installed for each unit network connected to the backbone switch, and the packet meter captures and analyzes only packets transmitted from the unit network. However, the problem with this method is that if the amount of packets transmitted in the unit network is considerably large, bottlenecks occur in the packet measurer 105 installed in the network, which may cause a delay in determining the packet transmission status of the entire VoIP network. The installation cost and the management cost are increased drastically because several measuring devices 105 must be installed. The biggest problem is that it is difficult to grasp the situation of the entire VoIP network by merging the data measured by the packet measurer 105.

The present invention has been made to solve the technical difficulties that can not be handled in real time to the current status of the call flow analysis and performance analysis of the Internet phone network (VoIP), the packet collecting device is installed in the Internet phone network (VoIP) all packets The purpose of the present invention is to provide a VoIP packet information retrieval apparatus and method capable of real-time network management by dividing the packet information into a DB by capturing the packet information based on a capture time and dividing a search query for the packet information.

In addition, in order to deal with a large amount of packets generated in real time as described above, in order to overcome the complexity of merging a lot of installation cost and result by installing several packet probes (Probe) in one backbone switch, It is an object of the present invention to provide a VoIP packet information retrieval apparatus and method for reducing installation cost and simplifying operation by installing only a small number of packet collecting devices in a backbone switch and processing them collectively.

In addition, as described above, although it takes a lot of time to process a large packet, even in real time, the information is generated later than the time of occurrence, but the information is shown, but VoIP which shows the call flow and performance situation of the near-real-time VoIP network is overcome. An object of the present invention is to provide a packet information retrieval apparatus and method.

In addition, in the prior art, only the packet information required or specified in advance or packet information for a very short time is dealt with, and the form that the VoIP network manager can search is overcome, thereby preserving all the packet information for a predetermined time. It is an object of the present invention to provide a VoIP packet information retrieval apparatus and method for retrieving almost any type of packet information desired by an administrator.

Call flow retrieval apparatus through DB partitioning and query splitting for packet information, which is the present invention for achieving the above object, is connected to a backbone switch connected to an Internet telephone network (VoIP) to capture all the packets transmitted to the VoIP network (Mirroring) The packet collecting unit and the packet information DB, which constitute a plurality of packet information tables in the same structure as a table for storing the packet information, and convert the collected packets into the packet information table of the packet information DB according to the structure and store them in the table. Packet information generation unit, and a call flow search terminal for querying the packet information DB after optimizing the query by dividing a query for searching packet information related to a call flow according to the structure of a packet information table It is done.

As described above, the present invention installs a packet collecting device in a VoIP network, captures all packets, divides packet information based on a capture time, makes a DB, and splits a request query for packet information. By doing so, it is possible to process the call flow status and performance analysis of VoIP in real time. In the past, due to the limitations of the processing of the vast packet information of the packet analysis device and the limitations of the technical configuration, the real-time processing could not be provided. In particular, if the VoIP network becomes universal in the future and the amount of packets increases more rapidly, the solution proposed by the present invention may overcome this problem.

In particular, if the entire packet is processed, the size of the DB table is enormous, and if the size is increased to some extent, the processing speed of the DBMS is drastically slowed. The present invention solves the above problems by dividing and processing a table, and proposes an apparatus and method capable of retrieving a large amount of total packet information.

In addition, in partitioning a table, an apparatus and a method for optimizing a query that can be searched more effectively by dividing based on a time when a packet is generated are proposed.

In addition, since all packet information is kept for a certain period of time, and the search speed is improved, a search that takes a long time can be executed immediately. Thus, almost any type of packet information desired by the VoIP network manager can be retrieved. In general, if you store 30 days of communication data, you can identify most situations and problems that occur in VoIP communication and store all the necessary data in packet information. It is not too difficult. In addition, a search that matches only a part of the data generally takes a lot of searching time. Due to the large packet information, such a search cannot be searched in a short time in the related art, and thus there is no limit to the search. However, the present invention can process such a search in a very short time, so that the VoIP network manager can provide virtually all the necessary searches.

In addition, the present invention is to collect a large amount of packets from a plurality of packet collecting devices and extract only the necessary packet information, significantly reduced the amount of DB to the database, the DB server for the most basic packet information The terminal manages the related search query and the like, and divides each function in order to process a large amount, thereby using resources more efficiently.

According to the present invention, since the VoIP network operator can grasp the call flow in near real time, when a failure occurs or a user needs a discomfort, the VoIP network service provider can quickly find the problem and provide a solution. Will be. Therefore, the VoIP network operator can maintain and provide a certain service level to the user through the apparatus and method of the present invention.

In addition, through another embodiment of the present invention, it is possible to process the internally without exposing the internal structure of the complex partitioned table to the client, simplifying the creation of a program related to the packet information search implemented in the client, the server It is free from modification of the internal table structure, which makes maintenance easy.

Hereinafter, an apparatus and method for searching call flows through DB partitioning and query partitioning for packet information will be described in detail with reference to the accompanying drawings.

5 is a block diagram showing a preferred embodiment of the packet information retrieval apparatus of the present invention.

As shown in FIG. 5, the packet information retrieval apparatus of the present invention includes a packet information DB 220, a plurality of packet collecting apparatuses 201, and a plurality of packet collecting apparatuses 201 including a packet collecting unit 210 and a packet information generating unit 230. Call flow terminal 250 is configured.

The packet collecting device 201 is connected to the backbone switch 132 of the Internet telephone network (VoIP) 130, collects packets, extracts packet information, and periodically stores the packet information in the packet information DB 220. The packet collecting device 201 may bite a plurality of backbone switches 132. In other words, the packet collecting device 201 is installed in the backbone switch 132 so that the entire packet can be mirrored by placing a plurality of places to capture the packet, and collects only the necessary packet information in the packet information DB (220) Device.

The packet collector 210 is connected to the backbone switch 132 of the VoIP 130 to capture all packets transmitted from the backbone switch 132. Normally, the unit VoIP networks are connected to the backbone switch 132 and switched, and the backbone switch 132 is also connected to another backbone switch 132. At this time, the packet collector 210 may be connected to one backbone switch 210 to collect packets. All packets are mirrored to the packet collector 210 and stored in a temporary file along with the time at which the packets were captured. The time at which the packet is captured is a time value to be treated as the generation time of the packet, which is useful for packet information retrieval. Temporary files are kept for only a short time, but they are kept longer than the packet information can be DBized. This is because an infinitely large temporary storage space cannot be secured in order to continuously capture and store continuously transmitted packets.

The packet information DB 220 is a table capable of storing packet information and constitutes a plurality of packet information tables with the same structure.

The structure of the packet information DB 220 will be described in more detail with reference to FIG. 6. FIG. 6A is a preferred embodiment of the structure of the packet information DB 220, and FIG. 6B is a preferred embodiment of the structure of the packet information table. The packet information DB 220 is composed of M packet information tables 221, and these packet information tables 221 all have the same structure. That is, not only the structure of the field name and the size of the field is the same, but also the size of the table, that is, the number of records is the same. The number and size of the packet information table 221 (the number of records per table) are determined by the number of packets to be collected, the processing speed when a DB query is made, and the period during which the packet information should be stored. For example, consider collecting 150 million packets per day. As the number of records in a table increases, the query speed for this table slows down. The number of records is determined by the amount of time the query must be processed for the user. In this example, the number of records in the packet information table 221 is about 1 million. If the policy of storing the packet information up to 30 days ago is determined, the number of the packet information table 221 is determined to be about 5500 (= 30 days * 150 million / 1 million).

As shown in FIG. 6B, the packet information table 221 has a structure of <Call ID>, <Calling ID>, <Incoming Call Number>, <Source IP / Port>, <Destination IP / Port>, and <Method>. , <Generation time>, and <telephone information>. Call related information such as <Call ID>, <Method>, <Phone Terminal Information>, <Outgoing Call Number>, and <Incoming Call Number> are information related to calls of VoIP protocol, and <Source IP / Port > And <Destination IP / Port> are information about devices when they are transmitted between devices. <Generation time> is the time saved when capturing the packet.

Each packet information table 221 has a storage target range determined on a generation time basis, and a plurality of packet information tables 221 are ordered according to a time reference storage range. For example, suppose that the first packet capture is performed from 12:00 and the time reference storage range of the packet information table 221 is set in units of 10 minutes. Then, the first packet information table 221 stores only packet information captured from 12:00 to 12:10, and the time reference storage range of this table is from 12:00 to 12:10. In the second table, only packet information captured from 12:11 to 12:20 is stored, and the time base storage range is from 12:11 to 12:20. This also means that the generation time value of the packet information is included in the time base storage range of the table in which the packet information is stored. Each packet information table has a time-based storage range. If the storage range unit is constant, the storage range can be known even with the initial time. The storage range unit may be arbitrarily adjusted according to the number of packets to be captured. In other words, if the number of packets per storage unit time exceeds the number of records in the table, the storage unit must be increased.

The packet information generation unit 230 converts the information required from the packets collected by the packet collection unit 210 and the generation time of the packet according to the structure of the packet information table 221 and stores them in the table. The order of the tables stored in the packet information table 221 from the 1st to the Mth is determined according to the time of the storage range of the table. In each packet information table 221, a time-based storage range is determined. As described above, if the packet information table 221 is divided into units of 10 minutes, the packet information table 221 is stored in the first table from 10 minutes after the first time to the second table after 10 minutes to 20 minutes. However, as another embodiment, the first incoming packet information is stored in the first packet information table 221, and when this table is full, it is stored in the next table, that is, the second packet information table 221. The former method has the disadvantage that it cannot be used for all the tables, but the advantage of simple calculation of the generation time range of each table is the advantage. The latter method has the opposite advantages and disadvantages. In both cases, if up to the M th packet information table 221 is stored, it is stored from the first packet information table 221 again. In this case, the initially stored packet information is lost. That is, the packet information is kept only for a certain time. The process of storing the packets collected by the packet information generator 230 is repeated at regular intervals. That is, when the packet collecting unit 210 collects a packet for a predetermined time and stores the packet in a temporary file, the packet information generating unit 230 periodically stores the packet information in the packet information table 221.

The call flow terminal 250 is a relationship between the packet information DB 220 and the client with respect to the server. That is, when the call flow terminal 250 requests a packet information request to the packet information DB 220 as a client, the packet information DB 220 processes the query. So the call flow terminal 250 obtains the necessary retrieved packet information. Calls are determined by <calling phone number> and <receiving phone number> of the packet information, and VoIP equipments 150 passing through each call are determined by <source IP / Port> and <destination IP / Port>. The call flow can retrieve the call flow, such as which VoIP equipment is passed through the call flow, by searching for all packet information having the same <outgoing call number> and <received call number>.

The query requesting the packet information DB 220 from the call flow terminal 250 will be described in more detail with reference to FIG. 7. 7 is an exemplary diagram of partition optimization of a query requested by a user in the call flow terminal 250.

 The query requesting the packet information DB 220 is divided into the original search query 310 and the divided query 320.

The original search query 310 creates a query considering the divided packet information tables 221 of the packet information DB 220 as one table. That is, as shown in FIG. 7, in the FROM <table name> which is a query statement of the source search query 310, <table name> is described as <DayRegi> as the full table name.

The divided search query 320 is a search query created by dividing a query according to the structure of the plurality of divided packet information tables 221 using the original search query 310. The source search query 310 is analyzed to determine which packet information table 221 is referenced in the query, and a query is prepared for each determined table. Which packet information table 221 is referenced in the source search query 310 is determined by the <creation time> condition of the WHERE clause which is a conditional statement of the query. That is, since the packet information table 221 is divided into a time reference storage range, tables for storing packet information in the generation time condition of the source search query 310 correspond to this. For example, in the example of FIG. 7, if the condition of the query is 3:10 to 4:30, all tables in which the time reference storage range of the packet information table 221 overlaps with each other are applicable.

A query for each overlapping packet information table 221 is as follows. The contents of the query are the same except for the <table name> and <create time> items. <Table name> is replaced with the name of the overlapping packet information table 221. The condition of <creation time> is narrowed within the time reference storage range of the overlapping packet information table 221. This is because each table stores only packets whose packet information generation time falls within the time base storage range. For example, as shown in FIG. 7, if the table <DayRegi-L> has packet information generated from 4:20 to 4:30, the query condition for this table is <4:20 to 4 You can set> to 25 minutes. In addition, the query optimization is also performed by first writing the creation time in the conditional statement of the query for each table 321. The reason is that the creation time item can serve as an index in the search, so the search can be performed faster in the DB. As described above, when a table-specific query 321 is generated for each table, the entire results are bundled using a UNION command that combines them as a whole. In conclusion, the partitioning of the raw search query 310 into the partitioned query 320 is not simply partitioned for all the packet information tables 221, but the query for the query condition for determining whether or not the table and the generation time. Performs optimization.

As mentioned above, recomposing a query using the UNION command by dividing the query greatly speeds up the query processing. As the size of the table, i.e. the number of records in the table, increases, the query processing speed slows down. However, if the size increases to some extent, the query processing speed may be drastically slowed down. This phenomenon is attributable to how the CPU processes and manages memory. In other words, as the number of records increases, more process context switches and memory swaps occur than can be handled by the CPU. Therefore, if the size of the table is limited to a certain size so as not to be suddenly delayed, and the processing is performed sequentially, a sudden drop in processing speed can be prevented.

When the split search query is generated, the call flow terminal 250 requests the query from the packet information DB and receives the result, and reconstructs the information about the call flow. Since the packet information DB contains almost all packet information until a certain period, the desired information can be extracted and viewed in any form. In addition, because the query processing is fast, even time-consuming queries such as a search where only a part of a word of data is matched can be seen without the user feeling too slow. In other words, almost any form desired by an administrator can be composed of a query.

The call flow terminal 250 noted above is for searching packet information mainly for the purpose of analyzing and monitoring call flow, but this is one preferred embodiment, and the terminal for retrieving packet information for other purposes is also described in this technique. It is considered to be included in the range.

8 is a flowchart illustrating a preferred embodiment of a DB partitioning and query partitioning method for packet information.

The method of DB partitioning and query partitioning for packet information includes packet information DB partitioning step S410, real time packet capturing step S420, packet information table storing step S430, and raw search query generation step S440. It consists of a divided search query generation step (S450), query request and result generation step (S460).

The packet information DB dividing step S410 refers to a step of configuring a table for storing packet information into a plurality of identical packet information tables. The DB table not only has the same structure such as field name and field size, but also has the same size of the table, that is, the number of records. As described above, the number and size of the packet information table are determined by the number of packets to be collected, the processing speed when the DB query is performed, and the period of storing the packet information. In addition, the packet information table consists of <Call ID>, <Outgoing Call Number>, <Incoming Call Number>, <Source IP / Port>, <Destination IP / Port>, <Method>, <Time>, <Phone Terminal Information> and the like.

Real time packet capturing (Mirroring) step (S420) is connected to the backbone switch of the Internet telephone network (VoIP) captures all the packets transmitted from the backbone switch 132 (Mirroring) and stores the packet with the time to capture the packet in a temporary file . The time at which the packet is captured is a time value to be treated as the generation time of the packet, which is useful for packet information retrieval. Temporary files are kept for only a short time, but they are kept longer than the packet information can be DBized.

In the packet information table storing step (S430), the collected packets and the captured time (generation time) are converted into the packet information table of the packet information DB according to its structure and stored, and the input order is input to a plurality of identical packet information tables. Stores sequentially in time units. That is, each packet information table 221 has a storage target range determined on a generation time basis, and a plurality of packet information tables are ordered on a time basis storage range.

In more detail, when there are M packet information tables, the order of the tables stored in the 1st to Mth packet information tables is determined according to the time of the storage range of the table. In each packet information table 221, a time-based storage range is determined. If the packet information table is divided into 10-minute units, the packet information table is stored in the first table from the first time to 10 minutes later and in the second table from 10 minutes to 20 minutes later. However, as another embodiment, the first incoming packet information is stored in the first packet information table 221, and when this table is full, it is stored in the next table, that is, the second packet information table 221. In both cases, if up to the M th packet information table 221 is stored, it is stored from the first packet information table 221 again. In this case, the initially stored packet information is lost. That is, the packet information is kept only for a certain time. The packet information table storing step S430 is periodically repeated at regular time intervals. That is, in the real time packet capture step (S420), if the packet is collected for a predetermined time and stored in a temporary file, the packet information is periodically stored in the packet information table.

The raw search query generation step (S440) is a step of writing a query for the packet information required by considering that the packet information table of the packet information DB is one table. The packet information table is divided into a plurality, but since the structure is the same, it can be conceptually regarded as one table. In particular, the client has the advantage of being able to write conceptually necessary queries without having to know which table according to the creation time. In other words, in FROM <table name> which is a query statement of a source search query, <table name> is written as the full table name.

The split search query generation step (S450) divides the created raw search query into each table and converts it into a search query, and adjusts the value according to the time-based storage range of each table.

More specifically, the divided search query generation step (S450) is a step (S451) of obtaining information on the time-based storage range for each table of the packet information DB, and the time-based storage range is the generation time of the raw search query Determining the packet information tables overlapping with the condition (S452), creating a query for each table referencing only the table with respect to the determined tables (S453), the generation time condition in the conditional statement in the query for each table, the time base of the table Adjusting to be included in the storage range (S454), the step of writing the creation time condition in the first condition of the query in the query for each table (S455), merged all the created query for each table by the UNION command partitioned Generating a search query (S456).

Since the packet information table is stored sequentially according to the packet generation time, the creation time information of each DB table is an important item for partitioning and optimizing a query. In other words, which table the source search query refers to is because only the table corresponding to the creation time condition can be found. Also, since the packet information table is stored sequentially according to the creation time, if the raw search query refers to several tables, it refers to a series of tables. Compared to the raw search query, the split query for each table is created with all other contents being the same and the name of the reference table is converted from only the table name to the corresponding table name. The <creation time> condition in the table-specific query for each table is reset to optimize the query. That is, since the packet generation time in each table is within the time base storage range, the generation time condition of a query for each table is determined within the time base storage range of the table. It also brings the creation time condition to the front of the query condition. Finally, when a table-by-table query is made for each table, a partitioned search query is made by combining the whole results by using the UNION command that combines them as a whole.

In the query request and result generation step (S460), when the above query is generated, the query is requested to the packet information DB, and the result is received, and the information is reconstructed into the information on the call flow. Since the packet information DB contains almost all packet information until a certain period, the desired call flow information can be extracted and viewed in any form.

9 is a block diagram of an embodiment of a packet information search server for call flow search of the present invention.

As shown in FIG. 9, the packet information retrieval apparatus of the present invention includes a packet collecting unit 501 including a packet collecting unit 510 and a packet information generating unit 530, a packet information DB 520, and a search query processing unit ( It comprises a packet information DB server 502 composed of 540.

The packet collecting device 501 is connected to the backbone switch 132 of the Internet telephone network (VoIP) 130, collects packets, extracts packet information, and periodically stores the packet information in the packet information DB 220. The packet collecting device 201 may bite a plurality of backbone switches 132. In other words, the packet collecting device 201 is installed in the backbone switch 132 so that the entire packet can be mirrored by placing a plurality of places to capture the packet, and collects only the necessary packet information in the packet information DB (220) Device.

The packet information DB server 502 divides and manages a plurality of packet information tables 221 having packet information to enable real-time retrieval, but recognizes the internal division of the external user, that is, the call flow search terminal 550. If you request a query considering it as a table without any need, it converts it to fit the internal table structure and gives the answer to the search query.

In more detail, the packet collector 510 is connected to the backbone switch 132 of the VoIP 130 to capture all packets transmitted from the backbone switch 132. Normally, the unit VoIP networks are connected to the backbone switch 132 and switched, and the backbone switch 132 is also connected to another backbone switch 132. At this time, the packet collector 510 may be connected to one backbone switch 210 to collect packets. All packets are mirrored to the packet collector 510 and stored in a temporary file along with the time at which the packets were captured. Temporary files are kept for only a short time, but they are kept longer than the packet information can be DBized.

The packet information DB 520 is a table capable of storing packet information and constitutes a plurality of packet information tables with the same structure. The structure of the packet information table 221 includes <Call ID>, <Calling phone number>, <Incoming phone number>, <Source IP / Port>, <Destination IP / Port>, <Method>, <Creation time>, < It consists of phone terminal information>. Call related information such as <Call ID>, <Method>, <Phone Terminal Information>, <Outgoing Call Number>, and <Incoming Call Number> are information related to calls of VoIP protocol, and <Source IP / Port > And <Destination IP / Port> are information about devices when they are transmitted between devices. <Generation time> is the time saved when capturing the packet. Each packet information table 221 has a storage target range determined on a generation time basis, and a plurality of packet information tables 221 are ordered according to a time base storage range. Each packet information table has a time-based storage range. If the storage range unit is constant, the storage range can be known even with the initial time. The storage range unit may be arbitrarily adjusted according to the number of packets to be captured.

The packet information generation unit 530 converts the information required from the packets collected by the packet collection unit 510 and the generation time of the packet according to the structure of the packet information table 221 and stores them in the table. The order of the tables stored in the packet information table 221 from the 1st to the Mth is determined according to the time of the storage range of the table. In each packet information table 221, a time-based storage range is determined in advance. That is, the packet information generation unit 530 sequentially stores the generated packet information in the plurality of packet information tables 221 according to the generation time reference, and when all the final tables are stored, the packet information generation unit 530 cyclically stores the first packet again. do.

The search query processing unit 540 receives the raw search query 310 of the VoIP call flow terminal 550 prepared by considering the plurality of packet information tables 221 as one table and queries the data according to the structure of the plurality of divided tables. Is created by dividing, and the divided search query 320 is requested to the packet information DB 520 for processing, and the result is returned.

More specifically, the packet information retrieval apparatus 500 internally divides and processes the packet information DB into a plurality of packet information tables 221, but does not show such a partitioning structure externally. Expose only its structure as a packet information table. This is possible because the plurality of packet information tables 221 have the same structure. Therefore, the VoIP call flow terminal 550, which is a client, regards the packet information table as one packet information table, creates a search query, and requests the packet information retrieval apparatus 500 of the present invention to process the query. The requested search query is the same as the original search query 310 described above. That is, the original search query 310 creates a query considering the divided plurality of packet information tables 221 of the packet information DB 220 as one table. <Table name> which is a query statement of the source search query 310 is described as the full table name.

The search query processing unit 540 of the packet information DB server 502 splits the query according to the structure of the plurality of packet information tables 221 which are divided from the original search query 310 requested from the VoIP call flow terminal 550. A partitioned search query 320 is created. The source search query 310 is analyzed to determine which packet information DB table 221 is referenced in the query, and then a query is prepared for each determined table. Which packet information DB table 221 is referenced in the source search query 310 is determined by the <creation time> condition of the WHERE clause which is a conditional statement of the query. That is, since the packet information DB table 221 is divided into a time base storage range, tables for storing packet information in the generation time condition of the source search query 310 correspond to this.

A query for each overlapping packet information table 221 is as follows. The contents of the query are the same except for the <table name> and <create time> items. <Table name> is replaced with the name of the overlapping packet information table 221. The condition of <creation time> is narrowed within the time reference storage range of the overlapping packet information table 221. This is because each table stores only packets whose packet information generation time falls within the time base storage range. In addition, the query optimization is also performed by first writing the creation time in the conditional statement of the query for each table 321. The reason is that the creation time item can serve as an index in the search, so the search can be performed faster in the DB.

As described above, when a table-specific query 321 is generated for each table, the entire results are bundled using a UNION command that combines them as a whole. In conclusion, the partitioning of the raw search query 310 into the partitioned query 320 is not simply partitioned for all the packet information tables 221, but the query for the query condition for determining whether or not the table and the generation time. Performs optimization.

The search query processing unit 540 requests the packet information DB 520 for query processing from the divided search query 320 and receives the result and returns the result to the VoIP call flow terminal 550. At this time, since all packet information tables 221 have the same structure and have unioned queries for each table, the packet information table 221 may be sent to the terminal without any processing.

In the detailed description of the present invention described by FIG. 9, the incomplete parts may be inferred and explained by an embodiment of the present invention by FIG. 5 described above.

If this is handled internally without exposing the complicated internal structure to the client as described above, the program related to retrieving the packet information implemented in the client is simpler to write and free from the internal table structure change of the server, thereby making maintenance easier. . For example, in the policy of fixing the time-based storage range of the packet information table in a predetermined unit as described above, when changing the policy to store the packet information in the table full and the storage range is fluid, the time-based storage range of the table is calculated. The method will change. In this case, maintenance is easy because only the relevant program needs to be changed on the server without changing the program of the client.

10 is a flowchart illustrating still another preferred embodiment of a DB partitioning and query partitioning method for packet information.

DB partitioning and query partitioning method for packet information includes packet information DB partitioning step (S610), real time packet capturing (Mirroring) step (S620), packet information table storage step (S630), raw search query reception step (S640). It consists of a divided search query generation step (S650), query request and result transmission step (S660).

The packet information DB dividing step S610 refers to a step of configuring a table for storing packet information into a plurality of identical packet information tables. The DB table not only has the same structure such as field name and field size, but also has the same size of the table, that is, the number of records. As described above, the number and size of the packet information table are determined by the number of packets to be collected, the processing speed when the DB query is performed, and the period of storing the packet information. In addition, the packet information table consists of <Call ID>, <Outgoing Call Number>, <Incoming Call Number>, <Source IP / Port>, <Destination IP / Port>, <Method>, <Time>, <Phone Terminal Information> and the like.

Real time packet capturing (Mirroring) step (S620) is connected to the backbone switch of the Internet telephone network (VoIP) captures all the packets transmitted from the backbone switch 132 (Mirroring) and stores the packet in the temporary file with the time captured . The time that a packet is captured is a time value to be treated as a packet generation time, which is useful for packet information retrieval. Temporary files are kept for only a short time, but they are kept longer than the packet information can be DBized.

In the packet information table storing step (S630), the collected packets and the captured time (generation time) are converted and stored in the packet information table of the packet information DB according to the structure thereof, and the input order is input to a plurality of identical packet information tables. Stores sequentially in time units. That is, each packet information table 221 has a storage target range determined on a generation time basis, and a plurality of packet information tables are ordered on a time basis storage range.

In more detail, when there are M packet information tables, the order of the tables stored in the 1st to Mth packet information tables is determined according to the time of the storage range of the table. In each packet information table 221, a time-based storage range is determined. If the packet information table is divided into 10-minute units, the packet information table is stored in the first table from the first time to 10 minutes later and in the second table from 10 minutes to 20 minutes later. However, as another embodiment, the first incoming packet information is stored in the first packet information table 221, and when this table is full, it is stored in the next table, that is, the second packet information table 221. In both cases, if up to the M th packet information table 221 is stored, it is stored from the first packet information table 221 again. In this case, the initially stored packet information is lost. That is, the packet information is kept only for a certain time. The packet information table storing step (S630) is repeated at regular intervals. That is, in the real-time packet capture step (S620), if the packet is collected for a predetermined time and stored in a temporary file, the packet information is periodically stored in the packet information table.

The raw search query accepting step (S640) is a step for receiving a search query for the packet information created by considering the packet information table of the packet information DB as one table. In other words, the original query, the requested query, is written with <table name> as the full table name.

The split search query generation step (S650) divides the requested raw search query into each table and converts it into a search query, and adjusts the value according to the time reference storage range of each table.

More specifically, the divided search query generation step (S650) is a step (S651) of obtaining information on the time-based storage range for each table of the packet information DB, and the time-based storage range is the generation time of the raw search query Determining the packet information tables overlapping with the condition (S652), creating a query for each table referencing only the table with respect to the determined tables (S653), the generation time condition in the conditional statement in the query for each table, the time reference of the table Adjusting to be included in the storage range (S654), step (S655) to write the generation time condition in the first condition of the query in the query for each table, merged all the created query for each table by UNION command Generating a search query (S656).

In the query request and the result transmission step (S660), when the above query is generated, the query is requested to the packet information DB, the result is received, and the raw search query is transmitted to the terminal (client).

FIG. 11 is a screen of an embodiment in which a call flow is reconfigured and displayed as a screen through the retrieved packet information. The screen retrieves packet information based on <generation time>, <outgoing call number>, and <incoming call number> to display the flow of packets between each VoIP device to view requests and responses for each VoIP device at a glance. To be visualized. This visualization makes it possible to navigate through each call flow and easily observe whether there are any abnormalities.

1 is a configuration of an Internet telephone network (VoIP),

2 is an exemplary packet flow diagram of an Internet telephone network (VoIP).

3 is a stack structure of a VoIP protocol,

4 is a configuration diagram of a conventional apparatus for measuring the performance of an Internet telephone network (VoIP) through packet analysis;

5 is a block diagram of a preferred embodiment of a packet information retrieval apparatus of the present invention;

6a shows packet information DB Desirable for the structure of One embodiment  Diagram,

Figure 6b is a preferred structure of the packet information table Implementation  Example,

7 is an exemplary diagram of partition optimization of a query requested by a user in a call flow terminal;

8 is a flowchart illustrating a preferred embodiment of a DB partitioning and query partitioning method for packet information;

9 is a configuration diagram of yet another preferred embodiment of the packet information retrieval server apparatus of the present invention;

10 is a flowchart illustrating still another preferred embodiment of a DB partitioning and query partitioning method for packet information;

11 is a screen of an embodiment shown in a screen by reconfiguring the call flow through the retrieved packet information.

Explanation of symbols on the main parts of the drawings

130: VoIP network 132: backbone switch

135: Internet telephone terminal 150: VoIP equipment

210: packet collecting unit 220: packet information DB

221: packet information table 250: VoIP call flow terminal

310: raw search query 320: partitioned search query

Claims (32)

In the real-time packet information retrieval apparatus through DB partitioning and query partitioning to monitor the VoIP network status by capturing packets connected to the backbone switch of the Internet telephone network (VoIP), A packet information DB 220 having a plurality of packet information tables 221 having the same structure as a table capable of storing packet information; Packet collecting unit 210 is connected to the backbone switch of the VoIP network and captures all packets transmitted to the VoIP network and stores them together with the generated time, which is the captured time, and periodically, A plurality of packet collecting apparatuses 201 including a packet information generating unit 230 for extracting information and configuring the packet information together with a packet generation time and dividing and storing the packet information into the packet information tables 221; A plurality of call flow search terminals 250 for creating a search query by dividing a search query for searching packet information by each packet information table 221 and requesting the search query to the packet information DB server 220; Real time packet information retrieval apparatus through DB partitioning and query partitioning comprising a. The method of claim 1, wherein the packet collecting unit 210, Temporary storage of the captured packet and the generation time, and the temporary storage is a real-time packet information retrieval device through DB partition and query partition, characterized in that stored for a longer time than the period of generating and storing the packet information in the packet information table 221 . The method of claim 1, wherein the plurality of packet information table 221, The size of each table is the same, and the number and size of the tables are determined by the number of packets collected, the processing speed of the DB query, and the period to store the packet information. Search device. The method of claim 1, The packet collecting unit 210 extracts information from a packet as <Call ID>, <Calling ID>, <Incoming Call Number>, <Method>, <Calling Terminal Information>, <Source IP / Port>, < Includes the destination IP / Port> The packet information table (221) is a real-time packet information retrieval device through a DB partition and a query partition, characterized in that the configuration including the extracted information and <generation time>. The method of claim 1, In the plurality of packet information tables 221, a range for storing packet information is determined based on a generation time of packet information, and an order between tables is determined according to a time criterion of the storage range. The packet information generating unit 210 sequentially stores the generated packet information in the plurality of packet information tables 221 according to the generation time reference, and when all the tables are stored up to the last table, the packet information generation unit cyclically stores the first packet again. Real time packet information retrieval apparatus through DB partitioning and query partitioning. According to claim 1, wherein the call flow search terminal 250, The plurality of packet information tables 221 are regarded as one table, and the prepared raw search query 310 is written for each packet information table 221 for each packet information table 221. Real-time packet information retrieval apparatus through DB partitioning and query partitioning, characterized in that to form a divided search query 320 by combining a query. The method of claim 6, wherein the call flow search terminal 250, When the generation time reference storage range of each packet information table 221 is obtained from the packet information DB 220, and the generation time range condition in the source search query 310 overlaps with the generation time reference storage range of the table. A device for retrieving real-time packet information through DB partitioning and query partitioning, wherein the table-specific query 321 is generated only for the corresponding packet information table 221. The method of claim 7, wherein the call flow search terminal 250, It is set within the generation time reference storage range of the packet information table 221 referring to the generation time condition of the query for each table 321, and when the condition of the query for each table is described, the generation time condition is placed first. Real time packet information retrieval device through DB partitioning and query partitioning. In the real-time packet information retrieval method through DB partitioning and query partitioning to monitor the VoIP network status by capturing packets connected to the backbone switch of an Internet telephone network (VoIP), A packet information DB dividing step (S410) of constructing a table capable of storing packet information into a plurality of identical packet information tables; A real time packet capturing step (S420) of connecting to a backbone switch of an Internet telephony network (VoIP) to capture all packets transmitted to the network and store them together with a generation time which is a captured time; A packet information DB table storing step (S430) of periodically extracting information from the collected packets, configuring the packet information together with the packet generation time, and dividing the packet information into the packet information tables; Generating a source search query by considering a plurality of packet information tables as one table in order to retrieve the packet information (S440); Generating a search query by dividing the source search query by each packet information table (S450); A query request and result generation step (S460) of requesting the written query to the packet information DB to obtain a result thereof; Real time packet information retrieval method through a DB partition and a query partition comprising a. 10. The method of claim 9, wherein the packet information DB dividing step (S410), The size of the table is the same, the number and size of the packet information table is determined by the number of packets to be collected, the processing speed of the DB query, the period to store the packet information, real-time through DB partitioning and query partitioning Packet information retrieval method. The method of claim 9, In the packet information DB table storing step (S430), the information extracted from the packet includes <Call ID>, <Calling ID>, <Incoming Call Number>, <Method>, <Phone Terminal Information>, and <Source IP / Port>. , Including <destination IP / Port>, The packet information table has a structure including the extracted information and <generation time> characterized in that the real-time packet information search method through the DB partition and query partition. The method of claim 9, wherein the real-time packet capturing (Mirroring) step (S420), Temporarily store the captured packet and the generation time, and the temporary storage is a real-time packet through the DB partition and query partition, characterized in that the packet information is stored for a longer time than the period of generating and storing the packet information in the DB table storage step (S430) How to retrieve information. The method of claim 9, In the plurality of packet information tables 221, a range for storing packet information is determined based on a generation time of packet information, and an order between tables is determined according to a time criterion of the storage range. The packet information DB table storing step (S430) sequentially stores the generated packet information in the plurality of packet information tables according to a generation time criterion, and when all the tables are stored up to the last table, the packet information is cyclically stored again from the first table. Real time packet information retrieval method using DB partitioning and query partitioning. 10. The method of claim 9, wherein the divided search query generation step (S450), Real-time packet information retrieval through DB partitioning and query partitioning, after creating a table-specific query referencing only the corresponding table for each packet information table and then combining each query into a UNION command to create a partitioned search query Way. 15. The method of claim 13 and 14, wherein the divided search query generation step (S450), Obtaining a generation time reference storage range of the packet information table from the packet information DB; Creating a table-specific query only for the packet information table corresponding to the case where the generation time range condition in the source search query and the generation time reference storage range of the table overlap; Grouping the created queries for each table into a UNION command; Real time packet information retrieval method through a DB partition and a query partition comprising a. The method of claim 15, wherein the divided search query generation step (S450), DB partition, characterized in that within the storage time generation time reference storage range of the packet information table referring to the generation time conditions of the query for each table, and when the condition of the query for each table is described, the generation time condition is placed to the front And real time packet information retrieval method through the query partition. It captures the packet that is connected to the backbone switch of the Internet telephone network (VoIP) and stores the packet information in a plurality of divided packet information tables, and exposes the structure of only one packet information table to the outside and regards it as one table. In the real time packet information retrieval server apparatus for receiving and processing a search query, DB partitioning and query partitioning, A packet information DB 520 comprising a plurality of packet information tables 221 having the same structure as a table capable of storing packet information, and a raw search query for retrieving packet information are requested. A packet information DB server 501 including a search query processing unit 540 which divides each packet information table 221 and processes a search query and returns a result thereof; A packet collecting unit 510 which is connected to a backbone switch of a VoIP network and captures all packets transmitted to the VoIP network and stores them together with a generation time which is a captured time, and periodically, A plurality of packet collecting apparatuses 502 including packet information generating unit 530 which extracts information and configures packet information together with a packet generation time, and divides and stores the information into packet information tables 221; Real-time packet information retrieval server device through the DB partition and query partition comprising a. The method of claim 17, wherein the packet collecting unit 510, Temporarily store the captured packet and the generation time, and the temporary storage is a real-time packet information search through the DB partition and query partition, characterized in that the packet information generation unit 530 stores the packet information for a longer period of time to generate and store the packet information Server device. The method of claim 17, wherein the plurality of packet information table 221, The size of each table is the same, and the number and size of the tables are determined by the number of packets collected, the processing speed of the DB query, and the period to store the packet information. Search server device. The method of claim 17, The information extracted from the packet by the packet information generating unit 530 includes <Call ID>, <Calling ID>, <Incoming Call Number>, <Method>, <Phone Terminal Information>, <Source IP / Port>, Including <destination IP / Port>, The packet information table (221) is a real time packet information retrieval server apparatus through DB partitioning and query partitioning, characterized in that the configuration including the extracted information and <generation time>. The method of claim 17, In the plurality of packet information tables 221, a range for storing packet information is determined based on a generation time of packet information, and an order between tables is determined according to a time criterion of the storage range. The packet information generation unit 530 sequentially stores the generated packet information in the plurality of packet information tables 221 according to the generation time reference, and if all the tables are stored up to the last table, the packet information generation unit 530 cycles from the first table again. Real time packet information retrieval server device through the DB partition and query partition. The method of claim 17, wherein the search query processing unit 540, Consider the plurality of packet information tables 221 as one table, and create a table-specific query 321 referencing only the corresponding table for each packet information table 221 for the raw search query 310 that is created, and then use the UNION command for each table. Real-time packet information retrieval server device through DB partitioning and query partitioning characterized in that to form a divided query query 320 by combining the query. The search query processing unit 540 of claim 22, When the generation time reference storage range of each packet information table 221 is obtained from the packet information DB 520 and overlaps the generation time range condition in the source search query 310 with the generation time reference storage range of the table. The real-time packet information retrieval server apparatus according to DB partitioning and query partitioning, wherein the table-specific query 321 is generated only for the corresponding packet information table 221. The search query processing unit 540 of claim 23, It is set within the generation time reference storage range of the packet information table 221 referring to the generation time condition of the query for each table 321, and when the condition of the query for each table is described, the generation time condition is placed first. Real time packet information retrieval server device through DB partition and query partition. It captures the packet that is connected to the backbone switch of the Internet telephone network (VoIP) and stores the packet information in a plurality of divided packet information tables, and exposes the structure of only one packet information table to the outside and regards it as one table. In the real time packet information retrieval processing method through DB partitioning and query partitioning to receive and process a search query, A packet information DB dividing step (S610) of constructing a table capable of storing packet information into a plurality of identical packet information tables; A real-time packet capturing step (S620) connected to a backbone switch of an Internet telephone network (VoIP) to capture all packets transmitted to the network and store them together with a generated time, which is captured time (S620); A packet information DB table storing step (S630) of periodically extracting information from the collected packets, configuring the packet information together with a packet generation time, and dividing and storing the packet information into the packet information tables; Generating a source search query by considering a plurality of packet information tables as one table in order to retrieve the packet information (S640); Generating a search query by dividing the source search query by each packet information table (S650); A query request and result generation step of requesting the written query to the packet information DB to obtain the result (S660); Real-time packet information retrieval processing method through DB partitioning and query partitioning comprising a. 26. The method of claim 25, wherein the packet information DB dividing step (S610), The size of the table is the same, the number and size of the packet information table is determined by the number of packets to be collected, the processing speed of the DB query, the period to store the packet information, real-time through DB partitioning and query partitioning Packet information retrieval processing method. The method of claim 25, In the packet information DB table storing step (S630), the information extracted from the packet includes <Call ID>, <Calling ID>, <Incoming Call Number>, <Method>, <Phone Terminal Information>, and <Source IP / Port>. , Including <destination IP / Port>, The packet information table has a structure including the extracted information and <generation time>, real-time packet information retrieval processing method through DB partitioning and query partitioning. The method of claim 25, wherein the real-time packet capture step (S620), Temporarily stores the captured packet and the generation time, and the temporary storage is a real time packet through DB partitioning and query partitioning, characterized in that the packet information is stored for a longer time than the period of generating and storing the packet information in the packet information DB table storage step (S630). How to retrieve information. The method of claim 25, In the plurality of packet information tables 221, a range for storing packet information is determined based on a generation time of packet information, and an order between tables is determined according to a time criterion of the storage range. The packet information DB table storing step (S630) sequentially stores the generated packet information in the plurality of packet information tables according to a generation time criterion, and when all the tables are stored up to the last table, the packet information DB table is cyclically stored again from the first table. Real time packet information retrieval processing method through DB partitioning and query partitioning. The method of claim 25, wherein the divided search query generation step (S650), Real-time packet information retrieval through DB partitioning and query partitioning, after creating a table-specific query referencing only the corresponding table for each packet information table and then combining each query into a UNION command to create a partitioned search query Treatment method. 31. The method of claim 29 and 30, wherein the divided search query generation step (S650), Obtaining a generation time reference storage range of the packet information table from the packet information DB; Creating a table-specific query only for the packet information table corresponding to the case where the generation time range condition in the source search query and the generation time reference storage range of the table overlap; Grouping the created queries for each table into a UNION command; Real-time packet information retrieval processing method through DB partitioning and query partitioning comprising a. The method of claim 31, wherein the divided search query generation step (S650), DB partition, characterized in that within the storage time generation time reference storage range of the packet information table referring to the generation time conditions of the query for each table, and when the condition of the query for each table is described, the generation time condition is placed to the front And real time packet information retrieval processing method through query partitioning.

Images