API tools faq
paste
Advertisement
Guest User

dagb

a guest
Sep 7th, 2015
159
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 7.86 KB | None | 0 0
raw download clone embed print report diff
  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?
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!