7 Replies Latest reply on Jun 1, 2017 1:30 PM by mesverrum

    Need an "inventory" report of custom properties for database cleanup!

    patriot

      I am attempting to create a report showing hoe certain custom properties are being used. I have a list of outdated custom properties (nodes and interfaces) that have been flagged for deletion, but I don;t want to do that until I can see where they are being used in group dynamic queries, view limitations, report filters, alert triggers, etc. In other words, I want to know what is going to break if these CPs are deleted.

       

      Has anyone created a report that will show this type of information in a nice concise manner? Thanks for any guidance.

        • Re: Need an "inventory" report of custom properties for database cleanup!
          patriot

          Anyone have an idea or an example?

            • Re: Need an "inventory" report of custom properties for database cleanup!
              mesverrum

              I did it in sql for a client before, since it was a one time deal it could likely be optimized and will likely need some evaluation for false positives and such, but it gives you a starting point.  It checks for references to the CP in the alert configurations, alert actions, group definitions, account or view limitations, dashboard resources, or reports  I wrote it to use inside of SQL Studio, it doesn't show up quite right if you use orion database manager.

               

              DECLARE @property varchar(30); 

              SET @property = '%YOURCUSTOMPROPERTY%'; 

               

              SELECT * FROM [dbo].[ActionsProperties] AP

              JOIN DBO.ACTIONS A ON A.ACTIONID=AP.ACTIONID

              WHERE PROPERTYVALUE LIKE @property

               

              SELECT * FROM [dbo].[AlertConfigurations]

              where [trigger] like @property or reset like @property

               

              SELECT * FROM [dbo].[ContainerMemberDefinitions]

              where expression like @property or definition like @property

               

              SELECT * FROM [dbo].[Limitations] l

              left join dbo.views v on v.limitationid=l.limitationid

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

              where whereclause like @property

               

              SELECT * FROM [dbo].[Resources] r

              join [dbo].[ResourceProperties] rp on rp.resourceid=r.resourceid

              where propertyvalue like @property

               

              SELECT * FROM [dbo].[ReportDefinitions]

              where definition like @property

               

              -Marc Netterfield

                  Loop1 Systems: SolarWinds Training and Professional Services

                • Re: Need an "inventory" report of custom properties for database cleanup!
                  patriot

                  This is perfect! Thanks. So, for example, if I want to see where "City" is used, I would substitute City in between the % signs in the first DECLARE statement?

                   

                  How would the query change if I wanted to search for multiple custom properties?

                    • Re: Need an "inventory" report of custom properties for database cleanup!
                      mesverrum

                      yeah just like that.  I only wrote it to do them one at a time, making it do more than one at a time seemed like I would go down a road that was going to take more time than I wanted to dedicate.  In a super simple form you could just declare more variables for the other CP add "or" bits to the where lines in each segment, so something like

                       

                      WHERE PROPERTYVALUE LIKE @property1

                      or PROPERTYVALUE LIKE @property2

                      or PROPERTYVALUE LIKE @property3

                      ... etc

                       

                      If I was really going to rework the thing I would start by generating a list of the column names in the nodescustomproperties table, then some joins to all the relevant tables, and using a union to stack it all up into a single result set, but I haven't had the appetite to take that on yet as I'm much more likely to be adding custom properties to a client environment than deleting them.

                       

                      -Marc Netterfield

                          Loop1 Systems: SolarWinds Training and Professional Services

                        • Re: Need an "inventory" report of custom properties for database cleanup!
                          patriot

                          Yeah I get what you mean. My immediate task is that I am needing to delete some CPs that are no longer needed, but I don't want to break any customizations that might be using those custom properties. So, I need to see a list of the reports, views, groups, limitations, etc. where a CP is being used so I can edit them before I finally delete the old CPs.

                           

                          My first run using your report and targeting "City" did not show any results even though I had included City as a report filter, a user limitation and in a Group dynamic query. Any ideas why the report did not pick those occurrences up?

                           

                          Thanks so much again.

                          • Re: Need an "inventory" report of custom properties for database cleanup!
                            mesverrum

                            I just played with it, words like address or city are going to be ugly with a lot of false positives because there are a lot of matches for the term address, or words like capacity which are very common across solarwinds.  Good luck

                             

                            Also it looks like, trying to hit multiple cp against multiple tables takes a brutal long time to execute, even in my small lab.  Another point in favor if leaving it broken up.

                             

                            Not sure why City isn't getting any hits for you, in my lab i've got a ton of them.