Questions against zabbix database

[95suhaibahmad]

My company uses Zabbix to monitor a lot of Servers. We'd like to analyze that data in Tableau. I found this Zabbix Schema: https://www.zabbix.org/mw/images/a/ad/Zabbix_db_schema-2.4.3-MySQL.pdf . Does anyone have some pre-written Zabbix queries to look at CPU utilization over time, Alarms, Network usage, etc. I could start to write my own, but I'd rather not re-invent the wheel unless I have to. If someone has a Workbook which connects to a Zabbix DB which does some of these things, would you consider sharing it?


Here is the SQL I wrote to partially answer this question. Please check as to whether or not an inner join should be changed to left join, my zabbix data may be different than yours.

1. SELECT
2. T1.flags AS "Groups.Flags",
3. T1.groupid AS "Groups.Group ID",
4. T1.internal AS "Groups.Internal",
5. T1.name AS "Groups.Name",
6. T2.groupid AS "Hosts Groups.Group ID",
7. T2.hostgroupid AS "Hosts Groups.Host Group ID",
8. T2.hostid AS "Hosts Groups.Host ID",
9. T3.available AS "Hosts.Available",
10. T3.disable_until AS "Hosts.Disable Until",
11. T3.error AS "Hosts.Error",
12. T3.errors_from AS "Hosts.Errors From",
13. T3.flags AS "Hosts.Flags",
14. T3.host AS "Hosts.Host",
15. T3.hostid AS "Hosts.Host ID",
16. T3.ipmi_authtype AS "Hosts.Ipmi Authtype",
17. T3.ipmi_available AS "Hosts.Ipmi Available",
18. T3.ipmi_disable_until AS "Hosts.Ipmi Disable Until",
19. T3.ipmi_error AS "Hosts.Ipmi Error",
20. T3.ipmi_errors_from AS "Hosts.Ipmi Errors From",
21. T3.ipmi_password AS "Hosts.Ipmi Password",
22. T3.ipmi_privilege AS "Hosts.Ipmi Privilege",
23. T3.ipmi_username AS "Hosts.Ipmi_username",
24. T3.jmx_available AS "Hosts.Jmx Available",
25. T3.jmx_disable_until AS "Hosts.Jmx Disable Until",
26. T3.jmx_error AS "Hosts.Jmx Error",
27. T3.jmx_errors_from AS "Hosts.Jmx Errors From",
28. T3.lastaccess AS "Hosts.Last Access",
29. T3.maintenance_from AS "Hosts.Maintenance From",
30. T3.maintenance_status AS "Hosts.Maintenance Status",
31. T3.maintenance_type AS "Hosts.Maintenance Type",
32. T3.maintenanceid AS "Hosts.Maintenance ID",
33. T3.name AS "Hosts.Name",
34. T3.proxy_hostid AS "Hosts.Proxy Host ID",
35. T3.snmp_available AS "Hosts.Snmp Available",
36. T3.snmp_disable_until AS "Hosts.Snmp Disable Until",
37. T3.snmp_error AS "Hosts.Snmp Error",
38. T3.snmp_errors_from AS "Hosts.Snmp Errors From",
39. T3.status AS "Hosts.Status",
40. T3.templateid AS "Hosts.Template ID",
41. T4.authtype AS "Items.Authtype",
42. T4.data_type AS "Items.Data Type",
43. T4.delay AS "Items.Delay",
44. T4.delay_flex AS "Items.Delay Flex",
45. T4.delta AS "Items.Delta",
46. T4.description AS "Items.Description",
47. T4.error AS "Items.Error",
48. T4.filter AS "Items.Filter",
49. T4.flags AS "Items.Flags",
50. T4.formula AS "Items.Formula",
51. T4.history AS "Items.History",
52. T4.hostid AS "Items.Host ID",
53. T4.interfaceid AS "Items.Interface ID",
54. T4.inventory_link AS "Items.Inventory Link",
55. T4.ipmi_sensor AS "Items.Ipmi Sensor",
56. T4.itemid AS "Items.Item ID",
57. T4.key_ AS "Items.Key",
58. T4.lastlogsize AS "Items.Last Log Size",
59. T4.lifetime AS "Items.Lifetime",
60. T4.logtimefmt AS "Items.Logtime Fmt",
61. T4.mtime AS "Items.Mtime",
62. T4.multiplier AS "Items.Multiplier",
63. T4.name AS "Items.Name",
64. T4.params AS "Items.Params",
65. T4.password AS "Items.Password",
66. T4.port AS "Items.Port",
67. T4.privatekey AS "Items.Privatekey",
68. T4.publickey AS "Items.Publickey",
69. T4.snmp_community AS "Items.Snmp Community",
70. T4.snmp_oid AS "Items.Snmp_o ID",
71. T4.snmpv3_authpassphrase AS "Items.Snmpv3 Auth Passphrase",
72. T4.snmpv3_authprotocol AS "Items.Snmpv3 Authprotocol",
73. T4.snmpv3_contextname AS "Items.Snmpv3 Contextname",
74. T4.snmpv3_privpassphrase AS "Items.Snmpv3 Privpassphrase",
75. T4.snmpv3_privprotocol AS "Items.Snmpv3 Privprotocol",
76. T4.snmpv3_securitylevel AS "Items.Snmpv3 Securitylevel",
77. T4.snmpv3_securityname AS "Items.Snmpv3 Securityname",
78. T4.state AS "Items.State",
79. T4.status AS "Items.Status",
80. T4.templateid AS "Items.Template ID",
81. T4.trapper_hosts AS "Items.Trapper Hosts",
82. T4.trends AS "Items.Trends",
83. T4.type AS "Items.Type",
84. T4.units AS "Items.Units",
85. T4.username AS "Items.Username",
86. T4.value_type AS "Items.Value Type",
87. T4.valuemapid AS "Items.Valuemap ID",
88. T5.function AS "Functions.Function",
89. T5.functionid AS "Functions.Function ID",
90. T5.itemid AS "Functions.Item ID",
91. T5.parameter AS "Functions.Parameter",
92. T5.triggerid AS "Functions.Trigger ID",
93. T6.clock AS "History.Clock",
94. T6.itemid AS "History.Item ID",
95. T6.ns AS "History.Ns",
96. T6.value AS "History.Value",
97. T7.applicationid AS "Items_applications.Application ID",
98. T7.itemappid AS "Items_applications.Itemapp ID",
99. T7.itemid AS "Items_applications.Item ID",
100. T8.applicationid AS "Applications.Application ID",
101. T8.hostid AS "Applications.Host ID",
102. T8.name AS "Applications.Name",
103. T9.comments AS "Triggers.Comments",
104. T9.description AS "Triggers.Description",
105. T9.error AS "Triggers.Error",
106. T9.expression AS "Triggers.Expression",
107. T9.flags AS "Triggers.Flags",
108. T9.lastchange AS "Triggers.Last Change",
109. T9.priority AS "Triggers.Priority",
110. T9.state AS "Triggers.State",
111. T9.status AS "Triggers.Status",
112. T9.templateid AS "Triggers.Templateid",
113. T9.triggerid AS "Triggers.Triggerid",
114. T9.type AS "Triggers.Type",
115. T9.url AS "Triggers.Url",
116. T9.value AS "Triggers.Value",
117. T10.acknowledged AS "Events.Acknowledged",
118. from_unixtime(T10.clock) AS "Events.Clock",
119. T10.ns AS "Events.Ns",
120. T10.object AS "Events.Object",
121. T10.objectid AS "Events.Objectid",
122. T10.source AS "Events.Source",
123. T10.value AS "Events.Value",
124. T11.name AS "Trigger Discovery.Name",
125. T11.parent_triggerid AS "Trigger Discovery.Parent Trigger ID",
126. T11.triggerdiscoveryid AS "Trigger Discovery.Trigger Discovery ID",
127. T11.triggerid AS "Trigger Discovery.Trigger ID",
128. T12.comments AS "Triggers (Protype).Comments",
129. T12.description AS "Triggers (Protype).Description",
130. T12.error AS "Triggers (Protype).Error",
131. T12.expression AS "Triggers (Protype).Expression",
132. T12.flags AS "Triggers (Protype).Flags",
133. T12.lastchange AS "Triggers (Protype).Last Change",
134. T12.priority AS "Triggers (Protype).Priority",
135. T12.state AS "Triggers (Protype).State",
136. T12.status AS "Triggers (Protype).Status",
137. T12.templateid AS "Triggers (Protype).Templateid",
138. T12.triggerid AS "Triggers (Protype).Triggerid",
139. T12.type AS "Triggers (Protype).Type",
140. T12.url AS "Triggers (Protype).Url",
141. T12.value AS "Triggers (Protype).Value"
142. FROM
143. groups T1
144. INNER JOIN hosts_groups T2 ON T2.groupid = T1.groupid
145. INNER JOIN hosts T3 ON T3.hostid = T2.hostid
146. INNER JOIN items T4 ON T4.hostid = T3.hostid
147. INNER JOIN functions T5 ON T5.itemid = T4.itemid
148. LEFT JOIN history T6 ON T6.itemid = T4.itemid
149. INNER JOIN items_applications T7 ON T7.itemid = T4.itemid
150. INNER JOIN applications T8 ON T8.applicationid = T7.applicationid
151. INNER JOIN triggers T9 ON T9.triggerid = T5.triggerid
152. INNER JOIN events T10 ON T10.objectid = T9.triggerid
153. LEFT JOIN trigger_discovery T11 ON T11.triggerid = T9.triggerid
154. LEFT JOIN triggers T12 ON T12.triggerid = T11.parent_triggerid

[tmroberts]

Did you ever resolve this? I actually WORK at Tableau and have yet to figure out an effective way to bring Zabbix data into Tableau to analyze and/or visualize. As both a Zabbix Admin/Engineer and a DBA, the database, while effective, is WAY too normalized for use outside of Zabbix. So if you have resolved this, I would love to see what you've done.

[1berto]

Maybe i'm getting it wrong, but wouldn't be better to use the API instead of direct access to the database?