Find hosts\templates macroses, unused by items and triggers (including autodiscovery)

zabbix_find_unused_macroses.sql

SELECT m.hostid,
       m.macro
FROM hostmacro AS m
WHERE
  NOT (SELECT count(*)
     FROM functions AS f
     JOIN items AS i ON i.itemid = f.itemid
     WHERE i.hostid = m.hostid
     AND  f.parameter LIKE concat('%',m.macro,'%'))
  AND NOT
    (SELECT count(*)
     FROM triggers AS t
     JOIN functions AS f ON f.triggerid = t.triggerid
     JOIN items AS i ON i.itemid = f.itemid
     WHERE i.hostid = m.hostid
       AND (t.expression LIKE concat('%',m.macro,'%')
            OR t.description LIKE concat('%',m.macro,'%')) )
  AND NOT
    (SELECT count(*)
     FROM triggers AS t
     JOIN functions AS f ON f.triggerid = t.triggerid
     JOIN items AS i ON i.itemid = f.itemid
     JOIN trigger_tag AS tag ON tag.triggerid = t.triggerid
     WHERE i.hostid = m.hostid
       AND tag.value LIKE concat('%',m.macro,'%'))
  AND NOT
    (SELECT count(*)
     FROM items AS i
     WHERE i.hostid = m.hostid
       AND (i.key_ LIKE concat('%',m.macro,'%')
            OR i.name LIKE concat('%',m.macro,'%')
            OR i.snmp_community LIKE concat('%',m.macro,'%')
            OR i.username LIKE concat('%',m.macro,'%')
            OR i.password LIKE concat('%',m.macro,'%')) )
  AND NOT
    (SELECT count(*)
     FROM httptest AS ht
     WHERE ht.hostid = m.hostid
       AND (ht.name LIKE concat('%',m.macro,'%')
            OR ht.http_user LIKE concat('%',m.macro,'%')
            OR ht.http_password LIKE concat('%',m.macro,'%')) )
  AND NOT
    (SELECT count(*)
     FROM httpstep AS hs
     JOIN httptest AS ht ON ht.httptestid = ht.httptestid
     WHERE ht.hostid = m.hostid
       AND (hs.name LIKE concat('%',m.macro,'%')
            OR hs.url LIKE concat('%',m.macro,'%')
            OR hs.required LIKE concat('%',m.macro,'%')
            OR hs.status_codes LIKE concat('%',m.macro,'%')) ) ;