API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 20th, 2019
111
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.75 KB | None | 0 0
raw download clone embed print report
  1. zabbix=> copy (
  2. select
  3. h.hostid,
  4. sum(value),
  5. extract(day from to_timestamp(clock)),
  6. extract(month from to_timestamp(clock)),
  7. 2015,
  8. 'DailyExemptUsage'
  9. from
  10. hosts h,
  11. items i,
  12. history_uint hu
  13. where
  14. h.hostid = i.hostid
  15. and i.itemid = hu.itemid
  16. and i.name like 'Air%bound%Tot%'
  17. and clock between 1430463600 and 1432969200
  18. and extract(hour from to_timestamp(clock)) between 1 and 5
  19. and h.name like '172.xxx.%'
  20. group by
  21. h.hostid,
  22. extract(day from to_timestamp(clock)),
  23. extract(month from to_timestamp(clock))
  24. )
  25. to '/home/me/my_file' with csv;
  26.  
  27. Table "public.hosts"
  28. Column | Type | Modifiers
  29. --------------------+------------------------+----------------------------------------
  30. hostid | bigint | not null
  31. proxy_hostid | bigint |
  32. host | character varying(128) | not null default ''::character varying
  33. status | integer | not null default 0
  34. disable_until | integer | not null default 0
  35. error | character varying(128) | not null default ''::character varying
  36. available | integer | not null default 0
  37. errors_from | integer | not null default 0
  38. lastaccess | integer | not null default 0
  39. ipmi_authtype | integer | not null default 0
  40. ipmi_privilege | integer | not null default 2
  41. ipmi_username | character varying(16) | not null default ''::character varying
  42. ipmi_password | character varying(20) | not null default ''::character varying
  43. ipmi_disable_until | integer | not null default 0
  44. ipmi_available | integer | not null default 0
  45. snmp_disable_until | integer | not null default 0
  46. snmp_available | integer | not null default 0
  47. maintenanceid | bigint |
  48. maintenance_status | integer | not null default 0
  49. maintenance_type | integer | not null default 0
  50. maintenance_from | integer | not null default 0
  51. ipmi_errors_from | integer | not null default 0
  52. snmp_errors_from | integer | not null default 0
  53. ipmi_error | character varying(128) | not null default ''::character varying
  54. snmp_error | character varying(128) | not null default ''::character varying
  55. jmx_disable_until | integer | not null default 0
  56. jmx_available | integer | not null default 0
  57. jmx_errors_from | integer | not null default 0
  58. jmx_error | character varying(128) | not null default ''::character varying
  59. name | character varying(128) | not null default ''::character varying
  60. flags | integer | not null default 0
  61. templateid | bigint |
  62. description | text | not null default ''::text
  63. Indexes:
  64. "hosts_pkey" PRIMARY KEY, btree (hostid)
  65. "hosts_1" btree (host)
  66. "hosts_2" btree (status)
  67. "hosts_3" btree (proxy_hostid)
  68. "hosts_4" btree (name)
  69. "hosts_5" btree (maintenanceid)
  70. Foreign-key constraints:
  71. "c_hosts_1" FOREIGN KEY (proxy_hostid) REFERENCES hosts(hostid)
  72. "c_hosts_2" FOREIGN KEY (maintenanceid) REFERENCES maintenances(maintenanceid)
  73. "c_hosts_3" FOREIGN KEY (templateid) REFERENCES hosts(hostid) ON DELETE CASCADE
  74. Referenced by:
  75. TABLE "applications" CONSTRAINT "c_applications_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  76. TABLE "autoreg_host" CONSTRAINT "c_autoreg_host_1" FOREIGN KEY (proxy_hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  77. TABLE "drules" CONSTRAINT "c_drules_1" FOREIGN KEY (proxy_hostid) REFERENCES hosts(hostid)
  78. TABLE "group_prototype" CONSTRAINT "c_group_prototype_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  79. TABLE "host_discovery" CONSTRAINT "c_host_discovery_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  80. TABLE "host_discovery" CONSTRAINT "c_host_discovery_2" FOREIGN KEY (parent_hostid) REFERENCES hosts(hostid)
  81. TABLE "host_inventory" CONSTRAINT "c_host_inventory_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  82. TABLE "hostmacro" CONSTRAINT "c_hostmacro_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  83. TABLE "hosts" CONSTRAINT "c_hosts_1" FOREIGN KEY (proxy_hostid) REFERENCES hosts(hostid)
  84. TABLE "hosts" CONSTRAINT "c_hosts_3" FOREIGN KEY (templateid) REFERENCES hosts(hostid) ON DELETE CASCADE
  85. TABLE "hosts_groups" CONSTRAINT "c_hosts_groups_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  86. TABLE "hosts_templates" CONSTRAINT "c_hosts_templates_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  87. TABLE "hosts_templates" CONSTRAINT "c_hosts_templates_2" FOREIGN KEY (templateid) REFERENCES hosts(hostid) ON DELETE CASCADE
  88. TABLE "httptest" CONSTRAINT "c_httptest_2" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  89. TABLE "interface" CONSTRAINT "c_interface_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  90. TABLE "items" CONSTRAINT "c_items_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  91. TABLE "maintenances_hosts" CONSTRAINT "c_maintenances_hosts_2" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  92. TABLE "opcommand_hst" CONSTRAINT "c_opcommand_hst_2" FOREIGN KEY (hostid) REFERENCES hosts(hostid)
  93. TABLE "optemplate" CONSTRAINT "c_optemplate_2" FOREIGN KEY (templateid) REFERENCES hosts(hostid)
  94. TABLE "screens" CONSTRAINT "c_screens_1" FOREIGN KEY (templateid) REFERENCES hosts(hostid) ON DELETE CASCADE
  95.  
  96. Table "public.items"
  97. Column | Type | Modifiers
  98. -----------------------+-------------------------+------------------------------------------
  99. itemid | bigint | not null
  100. type | integer | not null default 0
  101. snmp_community | character varying(64) | not null default ''::character varying
  102. snmp_oid | character varying(255) | not null default ''::character varying
  103. hostid | bigint | not null
  104. name | character varying(255) | not null default ''::character varying
  105. key_ | character varying(255) | not null default ''::character varying
  106. delay | integer | not null default 0
  107. history | integer | not null default 90
  108. trends | integer | not null default 365
  109. status | integer | not null default 0
  110. value_type | integer | not null default 0
  111. trapper_hosts | character varying(255) | not null default ''::character varying
  112. units | character varying(255) | not null default ''::character varying
  113. multiplier | integer | not null default 0
  114. delta | integer | not null default 0
  115. snmpv3_securityname | character varying(64) | not null default ''::character varying
  116. snmpv3_securitylevel | integer | not null default 0
  117. snmpv3_authpassphrase | character varying(64) | not null default ''::character varying
  118. snmpv3_privpassphrase | character varying(64) | not null default ''::character varying
  119. formula | character varying(255) | not null default ''::character varying
  120. error | character varying(2048) | not null default ''::character varying
  121. lastlogsize | numeric(20,0) | not null default (0)::numeric
  122. logtimefmt | character varying(64) | not null default ''::character varying
  123. templateid | bigint |
  124. valuemapid | bigint |
  125. delay_flex | character varying(255) | not null default ''::character varying
  126. params | text | not null default ''::text
  127. ipmi_sensor | character varying(128) | not null default ''::character varying
  128. data_type | integer | not null default 0
  129. authtype | integer | not null default 0
  130. username | character varying(64) | not null default ''::character varying
  131. password | character varying(64) | not null default ''::character varying
  132. publickey | character varying(64) | not null default ''::character varying
  133. publickey | character varying(64) | not null default ''::character varying
  134. privatekey | character varying(64) | not null default ''::character varying
  135. mtime | integer | not null default 0
  136. flags | integer | not null default 0
  137. interfaceid | bigint |
  138. port | character varying(64) | not null default ''::character varying
  139. description | text | not null default ''::text
  140. inventory_link | integer | not null default 0
  141. lifetime | character varying(64) | not null default '30'::character varying
  142. snmpv3_authprotocol | integer | not null default 0
  143. snmpv3_privprotocol | integer | not null default 0
  144. state | integer | not null default 0
  145. snmpv3_contextname | character varying(255) | not null default ''::character varying
  146. evaltype | integer | not null default 0
  147. Indexes:
  148. "items_pkey" PRIMARY KEY, btree (itemid)
  149. "items_1" UNIQUE, btree (hostid, key_)
  150. "items_3" btree (status)
  151. "items_4" btree (templateid)
  152. "items_5" btree (valuemapid)
  153. "items_6" btree (interfaceid)
  154. Foreign-key constraints:
  155. "c_items_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
  156. "c_items_2" FOREIGN KEY (templateid) REFERENCES items(itemid) ON DELETE CASCADE
  157. "c_items_3" FOREIGN KEY (valuemapid) REFERENCES valuemaps(valuemapid)
  158. "c_items_4" FOREIGN KEY (interfaceid) REFERENCES interface(interfaceid)
  159. Referenced by:
  160. TABLE "functions" CONSTRAINT "c_functions_1" FOREIGN KEY (itemid) REFERENCES items(itemid) ON DELETE CASCADE
  161. TABLE "graphs" CONSTRAINT "c_graphs_2" FOREIGN KEY (ymin_itemid) REFERENCES items(itemid)
  162. TABLE "graphs" CONSTRAINT "c_graphs_3" FOREIGN KEY (ymax_itemid) REFERENCES items(itemid)
  163. TABLE "graphs_items" CONSTRAINT "c_graphs_items_2" FOREIGN KEY (itemid) REFERENCES items(itemid) ON DELETE CASCADE
  164. TABLE "host_discovery" CONSTRAINT "c_host_discovery_3" FOREIGN KEY (parent_itemid) REFERENCES items(itemid)
  165. TABLE "httpstepitem" CONSTRAINT "c_httpstepitem_2" FOREIGN KEY (itemid) REFERENCES items(itemid) ON DELETE CASCADE
  166. TABLE "httptestitem" CONSTRAINT "c_httptestitem_2" FOREIGN KEY (itemid) REFERENCES items(itemid) ON DELETE CASCADE
  167. TABLE "item_condition" CONSTRAINT "c_item_condition_1" FOREIGN KEY (itemid) REFERENCES items(itemid) ON DELETE CASCADE
  168. TABLE "item_discovery" CONSTRAINT "c_item_discovery_1" FOREIGN KEY (itemid) REFERENCES items(itemid) ON DELETE CASCADE
  169. TABLE "item_discovery" CONSTRAINT "c_item_discovery_2" FOREIGN KEY (parent_itemid) REFERENCES items(itemid) ON DELETE CASCADE
  170. TABLE "items" CONSTRAINT "c_items_2" FOREIGN KEY (templateid) REFERENCES items(itemid) ON DELETE CASCADE
  171. TABLE "items_applications" CONSTRAINT "c_items_applications_2" FOREIGN KEY (itemid) REFERENCES items(itemid) ON DELETE CASCADE
  172.  
  173. Table "public.history_uint"
  174. Column | Type | Modifiers
  175. --------+---------------+-------------------------------
  176. itemid | bigint | not null
  177. clock | integer | not null default 0
  178. value | numeric(20,0) | not null default (0)::numeric
  179. ns | integer | not null default 0
  180. Indexes:
  181. "history_uint_1" btree (itemid, clock)
  182. Triggers:
  183. partition_trg BEFORE INSERT ON history_uint FOR EACH ROW EXECUTE PROCEDURE trg_partition('day')
  184. Number of child tables: 18 (Use d+ to list them.)
  185.  
  186. zabbix=> copy (
  187. select hostid,
  188. value,
  189. extractDay,
  190. extractMonth,
  191. intYear,
  192. DailyUsage
  193. from
  194. (
  195. select
  196. h.hostid,
  197. sum(value) as value,
  198. extract(day from to_timestamp(clock)) as extractDay,
  199. extract(month from to_timestamp(clock)) as extractMonth,
  200. 2015 as intYear,
  201. 'DailyExemptUsage' as DailyUsage
  202. from
  203. hosts h,
  204. items i,
  205. history_uint hu
  206. where
  207. h.hostid = i.hostid
  208. and i.itemid = hu.itemid
  209. and i.name like 'Air%bound%Tot%'
  210. and clock between 1430463600 and 1432969200
  211. and extract(hour from to_timestamp(clock)) between 1 and 5
  212. and h.name like '172.xxx.%'
  213. ) as source
  214. group by
  215. hostid,
  216. extractDay,
  217. extractMonth
  218. )
  219. to '/home/me/my_file' with csv;
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!