4 Replies Latest reply on Sep 21, 2018 2:06 AM by lynchnigel

    Need a report listing custom properties and their uses

    borgan

      I need to delete a number of custom properties from my database, but I do not want to do it until I have an idea of how those CPs are being used. So, how would I create a report showing the views, groups, limitations, reports, alerts, etc where a CP is currently used?

       

      Thanks for any help as I believe this will be a pretty hairy SQL job.

        • Re: Need a report listing custom properties and their uses
          mesverrum

          This is the stack of SQL queries I use to make reports on properties that i want to delete:

           

          --views and resources

          SELECT cp.Name as [Property Name], v.viewgroupname, v.viewtitle, r.resourcetitle, r.resourcename, rp.propertyvalue

          FROM sys.columns cp

          left join [dbo].[ResourceProperties] rp on propertyvalue like '%'+cp.Name+'%'

          join resources r on r.resourceid=rp.resourceid

          join views v on v.viewid=r.viewid

          WHERE object_id = OBJECT_ID('NodesCustomProperties')

          and cp.name != 'NodeID'

          order by cp.name

           

           

          --alert actions

          SELECT cp.Name as [Property Name], a.title, ap.propertyname, ap.propertyvalue

          FROM sys.columns cp

          left join [dbo].[ActionsProperties] AP on PROPERTYVALUE like '%'+cp.Name+'%'

          join ACTIONS A ON A.ACTIONID=AP.ACTIONID

          WHERE object_id = OBJECT_ID('NodesCustomProperties')

          and cp.name != 'NodeID'

          order by cp.name

           

           

          --alert configurations

          SELECT cp.Name as [Property Name], ac.name as Alertname

          FROM sys.columns cp

          Left join [dbo].[AlertConfigurations] ac on ([trigger] like '%'+cp.Name+'%' or reset like '%'+cp.Name+'%')

          WHERE object_id = OBJECT_ID('NodesCustomProperties')

          and cp.name != 'NodeID'

          and ac.enabled=1

          order by cp.name

           

           

          --groups

          SELECT cp.Name as [Property Name], c.Name as [Group], cm.name as [Rule Name], cm.expression, cm.definition

          FROM sys.columns cp

          join [dbo].[ContainerMemberDefinitions] cm on cm.expression like '%'+cp.Name+'%' or cm.definition like '%'+cp.Name+'%'

          join containers c on c.containerid=cm.containerid

          WHERE object_id = OBJECT_ID('NodesCustomProperties')

          and cp.name != 'NodeID'

          order by cp.name

           

           

          --view and account limitations

          SELECT cp.Name as [Property Name], l.whereclause, v2.viewgroupname, v2.viewtitle, acc.menuname, acc.limitationid1, acc.limitationid2, acc.limitationid3

          FROM sys.columns cp

          join [dbo].[Limitations] l on l.whereclause like '%'+cp.Name+'%'

          join dbo.views v2 on v2.limitationid=l.limitationid

          join dbo.accounts acc on acc.limitationid1=l.limitationid or acc.limitationid2=l.limitationid or acc.limitationid3=l.limitationid

          order by cp.name

           

           

          --reports

          SELECT cp.Name as [Property Name], rep.name, rep.definition

          FROM sys.columns cp

          join  [dbo].[ReportDefinitions] rep on rep.definition like '%'+cp.Name+'%'

          WHERE object_id = OBJECT_ID('NodesCustomProperties')

          and cp.name != 'NodeID'

          order by cp.name

            • Re: Need a report listing custom properties and their uses
              borgan

              Thank you sir for your help. I tried this and seem to be getting some bogus results - but it could be that I am not reading the reports results correctly.

               

              For example, in the query related to alert actions, the report seems to indicate that the custom property, "City" is being used in an alert action, but the Property Values in the report do not show Node.City anywhere unless I am missing it. See the screenshot please. Any ideas?

            • Re: Need a report listing custom properties and their uses
              mesverrum

              So it is using string matching and some strings are messier than others.  City could be matching with capacity, for example, you can try to narrow it down by putting spaces around city maybe but that might or might not work depending how the fields are written

              • Re: Need a report listing custom properties and their uses
                lynchnigel

                The reports have custom properties in them, you just need to go down the list and it is there...you can do section breaks in the reports as well so you should be able to pull groups etc together.