Guest User

dagb

a guest
Sep 7th, 2015
89
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. mysql> describe varbinds;
  2. +---------+----------------------------------------------+------+-----+---------+-------+
  3. | Field   | Type                                         | Null | Key | Default | Extra |
  4. +---------+----------------------------------------------+------+-----+---------+-------+
  5. | trap_id | int(11) unsigned                             | NO   | PRI | 0       |       |
  6. | msgdate | date                                         | NO   |     | NULL    |       |
  7. | msgtime | time                                         | NO   |     | NULL    |       |
  8. | oid     | varchar(64)                                  | NO   | PRI | NULL    |       |
  9. | type    | enum('boolean',[....],'counter64','unused2') | NO   |     | NULL    |       |
  10. | value   | blob                                         | NO   |     | NULL    |       |
  11. +---------+----------------------------------------------+------+-----+---------+-------+
  12.  
  13.  
  14.  
  15. mysql> show create table varbinds;
  16. +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  17. | Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                   |
  18. +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  19. | varbinds | CREATE TABLE `varbinds` (
  20.   `trap_id` int(11) unsigned NOT NULL DEFAULT '0',
  21.   `msgdate` date NOT NULL,
  22.   `msgtime` time NOT NULL,
  23.   `oid` varchar(64) NOT NULL,
  24.   `type` enum('boolean','integer','bit','octet','null','oid','ipaddress','counter','unsigned','timeticks','opaque','unused1','counter64','unused2') NOT NULL,
  25.   `value` blob NOT NULL,
  26.   PRIMARY KEY (`trap_id`,`oid`)
  27. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  28.  
  29.  
  30.  
  31. mysql> select * from varbinds where trap_id = 9984589;
  32. +---------+------------+----------+-------------------------------------------------------+-----------+----------------------------------------------+
  33. | trap_id | msgdate    | msgtime  | oid                                                   | type      | value                                        |
  34. +---------+------------+----------+-------------------------------------------------------+-----------+----------------------------------------------+
  35. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.2.1.1.3.0                                    | timeticks | Timeticks: (147490400) 17 days, 1:41:44.00   |
  36. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.513.1.1.1.1.5.x.x.x.x.x   | octet     | STRING: "apname"                      |
  37. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.513.1.2.1.1.1.0                      | unsigned  | Gauge32: 1                                   |
  38. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.27.x.x.x.x.x | octet     | STRING: "username"                            |
  39. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.28.x.x.x.x.x | octet     | STRING: "ssid"                          |
  40. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.38.x.x.x.x.x | octet     | STRING: "55ed2de2/ac:29:3a:b7:91:73/1133797" |
  41. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.8.x.x.x.x.x  | octet     | Hex-STRING: 70 10 5C DA 16 B0                |
  42. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.2.1.2.0                      | integer   | INTEGER: 1                                   |
  43. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.2.1.3.0                      | octet     | Hex-STRING: 0A 74 10 85                      |
  44. | 9984589 | 2015-09-07 | 09:11:54 | .1.3.6.1.6.3.1.1.4.1.0                                | oid       | OID: .1.3.6.1.4.1.9.9.599.0.8                |
  45. +---------+------------+----------+-------------------------------------------------------+-----------+----------------------------------------------+
  46. 10 rows in set (0.03 sec)
  47.  
  48. mysql> select * from varbinds where trap_id = 9984588;
  49. +---------+------------+----------+-----------------------------------+-----------+--------------------------------------------+
  50. | trap_id | msgdate    | msgtime  | oid                               | type      | value                                      |
  51. +---------+------------+----------+-----------------------------------+-----------+--------------------------------------------+
  52. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.2.1.1.3.0                | timeticks | Timeticks: (147490400) 17 days, 1:41:44.00 |
  53. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.513.1.1.1.1.5.0  | octet     | STRING: "apname"                    |
  54. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.513.1.2.1.1.1.0  | unsigned  | Gauge32: 1                                 |
  55. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.1.0  | octet     | Hex-STRING: AC 29 3A B7 91 73              |
  56. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.10.0 | ipaddress | IpAddress: 10.116.16.133                   |
  57. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.27.0 | octet     | STRING: "username"                          |
  58. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.28.0 | octet     | STRING: "ssid"                        |
  59. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.4.1.9.9.599.1.3.1.1.8.0  | octet     | Hex-STRING: 70 10 5C DA 16 B0              |
  60. | 9984588 | 2015-09-07 | 09:11:54 | .1.3.6.1.6.3.1.1.4.1.0            | oid       | OID: .1.3.6.1.4.1.9.9.599.0.4              |
  61. +---------+------------+----------+-----------------------------------+-----------+--------------------------------------------+
  62. 9 rows in set (0.00 sec)
  63.  
  64.  
  65. Notice how the table contains traps of various lengths. And with various OID: .x.x.x.x.x in the value column. (Last row above)
  66.  
  67. I care for traps with the current date, having a varbind containing (value = «OID: .1.3.6.1.4.1.9.9.599.0.4») and (matching substring from the value colum where the oid column contains .1.3.6.1.4.1.9.9.599.1.3.1.1.27.0)
  68.  
  69.  
  70.  
  71. Query:
  72.  
  73. select
  74. trap.trap_id as trap_id,
  75. trap.msgdate as msgdate,
  76. trap.msgtime as msgtime,
  77. user.value as username,
  78. agent_mac.value as agent_mac,
  79. agent_ip.value as agent_ip,
  80. ap_name.value as ap_name,
  81. ssid.value as ssid
  82.  
  83. from varbinds as trap
  84.  
  85. join varbinds as user
  86. on user.trap_id = trap.trap_id and user.oid = '.1.3.6.1.4.1.9.9.599.1.3.1.1.27.0'
  87. and trap.value = 'OID: .1.3.6.1.4.1.9.9.599.0.4'
  88.  
  89. join varbinds as agent_ip
  90. on agent_ip.trap_id = trap.trap_id and agent_ip.oid = '.1.3.6.1.4.1.9.9.599.1.3.1.1.10.0'
  91.  
  92. join varbinds as agent_mac
  93. on agent_mac.trap_id = trap.trap_id and agent_mac.oid = '.1.3.6.1.4.1.9.9.599.1.3.1.1.1.0'
  94.  
  95. join varbinds as ap_name
  96. on ap_name.trap_id = trap.trap_id and ap_name.oid = '.1.3.6.1.4.1.9.9.513.1.1.1.1.5.0'
  97.  
  98. join varbinds as ssid
  99. on ssid.trap_id = trap.trap_id and ssid.oid = '.1.3.6.1.4.1.9.9.599.1.3.1.1.28.0'
  100.  
  101. where user.value like '%$USERINPUT%' and trap.msgdate = curdate() order by username, agent_mac, msgtime;
  102.  
  103.  
  104.  
  105. Can I optimize the primary key in the table definition any further?
RAW Paste Data