Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

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

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.

Find more posts tagged with

Custom Property

Accepted answers

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

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

All comments

patriot

Anyone have an idea or an example?

patriot

Helllloooo....

mesverrum

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

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

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?

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

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems