API tools faq
paste
View difference between Paste ID: WuZNB9bc and Tt3PmeMg
SHOW: | | - or go back to the newest paste.
1
mysql> describe varbinds;
2
+---------+----------------------------------------------+------+-----+---------+-------+
3-
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
3+
| Field   | Type                                         | Null | Key | Default | Extra |
4-
| Field   | Type                                                                                                                                       | Null | Key | Default | Extra |
4+
+---------+----------------------------------------------+------+-----+---------+-------+
5-
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
5+
| trap_id | int(11) unsigned                             | NO   | PRI | 0       |       |
6-
| trap_id | int(11) unsigned                                                                                                                           | NO   | PRI | 0       |       |
6+
| msgdate | date                                         | NO   |     | NULL    |       |
7-
| msgdate | date                                                                                                                                       | NO   |     | NULL    |       |
7+
| msgtime | time                                         | NO   |     | NULL    |       |
8-
| msgtime | time                                                                                                                                       | NO   |     | NULL    |       |
8+
| oid     | varchar(64)                                  | NO   | PRI | NULL    |       |
9-
| oid     | varchar(64)                                                                                                                                | NO   | PRI | NULL    |       |
9+
| type    | enum('boolean',[....],'counter64','unused2') | NO   |     | NULL    |       |
10-
| type    | enum('boolean','integer','bit','octet','null','oid','ipaddress','counter','unsigned','timeticks','opaque','unused1','counter64','unused2') | NO   |     | NULL    |       |
10+
| value   | blob                                         | NO   |     | NULL    |       |
11-
| value   | blob                                                                                                                                       | NO   |     | NULL    |       |
11+
+---------+----------------------------------------------+------+-----+---------+-------+
12-
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
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.1.1.1.5.112.16.92.218.22.176   | octet     | STRING: "H-OS-OSV-AP01"                      |

37+
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.27.172.41.58.183.145.115 | octet     | STRING: "camboe7"                            |

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.28.172.41.58.183.145.115 | octet     | STRING: "HFK-Skole"                          |

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.38.172.41.58.183.145.115 | 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.1.1.8.172.41.58.183.145.115  | octet     | Hex-STRING: 70 10 5C DA 16 B0                |

42+
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.1.1.1.5.0  | octet     | STRING: "H-OS-OSV-AP01"                    |

54+
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.27.0 | octet     | STRING: "camboe7"                          |

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.28.0 | octet     | STRING: "HFK-Skole"                        |

59+
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-
Notice how the table contains traps of various lengths. And with various OID: .x.x.x.x.x in the value column.

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-
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?
        

    


            


                                


        

            



                
    

        Public Pastes
    

    
            

    

                    

        

    





    


    
    
    
    
    
    
    
    




    


    



                

            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!