select cp.CONTENTID,sp.SPACEID, cp.STRINGVAL,
       CASE
            WHEN REGEXP_LIKE(cp.STRINGVAL, 'add-label|choose-label|cql-search|create-page|currency-converter|include-version|includes-report|lock-content-macro|markdown|mugshot-gallery|page-info|version-history') THEN 'ScriptRunner'
            WHEN REGEXP_LIKE(cp.STRINGVAL, 'drawio-sketch|drawio|inc-drawio') THEN 'draw.io'
            WHEN REGEXP_LIKE(cp.STRINGVAL, 'attachment-table|csv|jql-table|json-table|table-plus') THEN 'Advanced Tables'
            WHEN REGEXP_LIKE(cp.STRINGVAL, 'card|cloak|composition-setup|deck|float|banner|show-card|showcase|toggle-cloak|tab-group') THEN 'Composition Tabs'
            WHEN (REGEXP_LIKE(CP.STRINGVAL,'mail-form|forms-additional-recipients|forms-attachment|checkbox-button-group|checkbox-matrix|conditional-fields|mail-date-picker|mail-outgoing-parameter|dropdown-list|mail-fieldset|hidden-field-macro|mail-input')
                or REGEXP_LIKE(CP.STRINGVAL,'mail-body-formatter|forms-page-macro|radio-button-group|radio-matrix|responses-table|star-rating|mail-success|mail-textarea|user-picker')
                )THEN 'Forms for Confluence'
            WHEN REGEXP_LIKE(cp.STRINGVAL,'add-page|add-page-form|child-counter|link-page|link-to|link-window|incoming-links|orphaned-links|outgoing-links|undefined-links') THEN 'Linking for Confluence'
            WHEN REGEXP_LIKE(cp.STRINGVAL, 'svg|plantuml|plantumlrender') THEN 'PlantUML for Confluence'
            WHEN REGEXP_LIKE(cp.STRINGVAL, 'ui-button|ui-children|ui-children-cards|ui-expand|ui-image|ui-step|ui-steps|ui-tab|ui-tabs|ui-text-box') THEN 'Refined Toolkit for Confluence DC'
            WHEN REGEXP_LIKE(cp.STRINGVAL, 'csv-table|json-from-table|table-chart|table-excerpt|table-excerpt-include|table-filter|table-joiner|pivot-table') THEN 'Table Filter, Charts & Spreadsheets for Confluence'
            WHEN REGEXP_LIKE(cp.STRINGVAL, 'gliffy') THEN 'Gliffy Diagrams for Confluence'
            WHEN (REGEXP_LIKE(cp.STRINGVAL, 'alert|align|auibutton|auibuttongroup|auidialog|auihorizontalnav|auihorizontalnavpage|auimessage|auiprogress|auiprogressstepstatic|auitabs|auitabspage|bgcolor|bibtex-display|bibtex-reference|center|clickable')
                or REGEXP_LIKE(cp.STRINGVAL, 'colgroup|copyright|display-footnotes|div|fancy-bullets|footnote|highlight|htmlcomment|iframe|img|latex-formatting|\bli\b|lozenge|\bol\b|\bp\b|pre|privacy-mark|privacy-policy|reg-tm|rest-table|rolloverwithoutbody|roundrect')
                or REGEXP_LIKE(cp.STRINGVAL, 'search-box|\bsm\b|span|strike|style|table|tbody|\btd\b|\bth\b|thead|\btm\b|tooltip|\btr\b|\bul\b|h_color')
               )THEN 'Mosaic: Content Formatting Macros & Templates'
            ELSE 'Unknown Dependency'
        END as App
from CONTENTPROPERTIES cp
    join CONTENT c on c.CONTENTID = cp.CONTENTID
    join SPACES sp on sp.SPACEID = c.SPACEID
where c.PREVVER is null
  and c.CONTENT_STATUS not like '%deleted%'
  and cp.PROPERTYNAME = 'macroNames'
  and c.CONTENTTYPE in ('BLOGPOST', 'PAGE')
  and sp.SPACESTATUS = 'CURRENT'
and (
    --ScriptRunner
    cp.stringval like '%add-label%'
    or cp.stringval like '%choose-label%'
    or cp.stringval like '%cql-search%'
    or cp.stringval like '%create-page%'
    or cp.stringval like '%currency-converter%'
    or cp.stringval like '%include-version%'
    or cp.stringval like '%includes-report%'
    or cp.stringval like '%lock-content-macro%'
    or cp.stringval like '%markdown%'
    or cp.stringval like '%mugshot-gallery%'
    or cp.stringval like '%page-info%'
    or cp.stringval like '%version-history%'
    -- draw.io
    or cp.stringval like '%drawio-sketch%'
    or cp.stringval like '%drawio%'
    or cp.stringval like '%inc-drawio%'
    -- Advanced Tables
    or cp.stringval like '%attachment-table%'
    or cp.stringval like '%csv%'
    or cp.stringval like '%jql-table%'
    or cp.stringval like '%json-table%'
    or cp.stringval like '%table-plus%'
    -- Composition Tabs
    or cp.stringval like '%card%'
    or cp.stringval like '%cloak%'
    or cp.stringval like '%composition-setup%'
    or cp.stringval like '%deck%'
    or cp.stringval like '%float%'
    or cp.stringval like '%banner%'
    or cp.stringval like '%show-card%'
    or cp.stringval like '%showcase%'
    or cp.stringval like '%toggle-cloak%'
    or cp.stringval like '%tab-group%'
    -- Forms for Confluence
    or cp.stringval like '%mail-form%'
    or cp.stringval like '%forms-additional-recipients%'
    or cp.stringval like '%forms-attachment%'
    or cp.stringval like '%checkbox-button-group%'
    or cp.stringval like '%checkbox-matrix%'
    or cp.stringval like '%conditional-fields%'
    or cp.stringval like '%mail-date-picker%'
    or cp.stringval like '%mail-outgoing-parameter%'
    or cp.stringval like '%dropdown-list%'
    or cp.stringval like '%mail-fieldset%'
    or cp.stringval like '%hidden-field-macro%'
    or cp.stringval like '%mail-input%'
    or cp.stringval like '%mail-body-formatter%'
    or cp.stringval like '%forms-page-macro%'
    or cp.stringval like '%radio-button-group%'
    or cp.stringval like '%radio-matrix%'
    or cp.stringval like '%responses-table%'
    or cp.stringval like '%star-rating%'
    or cp.stringval like '%mail-success%'
    or cp.stringval like '%mail-textarea%'
    or cp.stringval like '%user-picker%'
    -- Linking for Confluence
    or cp.stringval like '%add-page%'
    or cp.stringval like '%add-page-form%'
    or cp.stringval like '%child-counter%'
    or cp.stringval like '%link-page%'
    or cp.stringval like '%link-to%'
    or cp.stringval like '%link-window%'
    or cp.stringval like '%incoming-links%'
    or cp.stringval like '%orphaned-links%'
    or cp.stringval like '%outgoing-links%'
    or cp.stringval like '%undefined-links%'
    -- PlantUML for Confluence
    or cp.stringval like '%svg%'
    or cp.stringval like '%plantuml%'
    or cp.stringval like '%plantumlrender%'
    -- Refined Toolkit for Confluence DC
    or cp.stringval like '%ui-button%'
    or cp.stringval like '%ui-children%'
    or cp.stringval like '%ui-children-cards%'
    or cp.stringval like '%ui-expand%'
    or cp.stringval like '%ui-image%'
    or cp.stringval like '%ui-step%'
    or cp.stringval like '%ui-steps%'
    or cp.stringval like '%ui-tab%'
    or cp.stringval like '%ui-tabs%'
    or cp.stringval like '%ui-text-box%'
    -- Table Filter, Charts & Spreadsheets for Confluence
    or cp.stringval like '%csv-table%'
    or cp.stringval like '%json-from-table%'
    or cp.stringval like '%table-chart%'
    or cp.stringval like '%table-excerpt%'
    or cp.stringval like '%table-excerpt-include%'
    or cp.stringval like '%table-filter%'
    or cp.stringval like '%table-joiner%'
    or cp.stringval like '%pivot-table%'
    -- Gliffy Diagrams for Confluence
    or cp.stringval like '%gliffy%'
    -- Mosaic: Content Formatting Macros & Templates
    or cp.stringval like '%alert%'
    or cp.stringval like '%align%'
    or cp.stringval like '%auibutton%'
    or cp.stringval like '%auibuttongroup%'
    or cp.stringval like '%auidialog%'
    or cp.stringval like '%auihorizontalnav%'
    or cp.stringval like '%auihorizontalnavpage%'
    or cp.stringval like '%auimessage%'
    or cp.stringval like '%auiprogress%'
    or cp.stringval like '%auiprogressstepstatic%'
    or cp.stringval like '%auitabs%'
    or cp.stringval like '%auitabspage%'
    or cp.stringval like '%bgcolor%'
    or cp.stringval like '%bibtex-display%'
    or cp.stringval like '%bibtex-reference%'
    or cp.stringval like '%center%'
    or cp.stringval like '%clickable%'
    or cp.stringval like '%colgroup%'
    or cp.stringval like '%copyright%'
    or cp.stringval like '%display-footnotes%'
    or cp.stringval like '%div%'
    or cp.stringval like '%fancy-bullets%'
    or cp.stringval like '%footnote%'
    or cp.stringval like '%highlight%'
    or cp.stringval like '%htmlcomment%'
    or cp.stringval like '%iframe%'
    or cp.stringval like '%img%'
    or cp.stringval like '%latex-formatting%'
    or cp.stringval like '%li%'
    or cp.stringval like '%lozenge%'
    or cp.stringval like '%ol%'
    or cp.stringval like '%p%'
    or cp.stringval like '%pre%'
    or cp.stringval like '%privacy-mark%'
    or cp.stringval like '%privacy-policy%'
    or cp.stringval like '%reg-tm%'
    or cp.stringval like '%rest-table%'
    or cp.stringval like '%rolloverwithoutbody%'
    or cp.stringval like '%roundrect%'
    or cp.stringval like '%search-box%'
    or cp.stringval like '%sm%'
    or cp.stringval like '%span%'
    or cp.stringval like '%strike%'
    or cp.stringval like '%style%'
    or cp.stringval like '%table%'
    or cp.stringval like '%tbody%'
    or cp.stringval like '%td%'
    or cp.stringval like '%th%'
    or cp.stringval like '%thead%'
    or cp.stringval like '%tm%'
    or cp.stringval like '%tooltip%'
    or cp.stringval like '%tr%'
    or cp.stringval like '%ul%'
    or cp.stringval like '%h_color%'
    );