SWQL / SQL query for custom property

Hi,

I need a query on custom properties based on the following:

1) Need a query for custom property to find out which is not being used or assigned on any node / Groups / Alerts / Views etc.

2) I am searching a query to find out which custom property is being used.

For example :If my CP has a value (State = 'Washington'), I would like to know where the CP like (State = 'Washington') is being used in any of the views / Alert configurations / Reports etc.

Note : The one which I mentioned about CP as State has a dropdown menu like 'Washington', 'Texas' etc.

Please assist here if anyone has idea about this.

Thanks in advance.

@AlexSoul @aLTeReGo @rschroeder @CourtesyIT

Find more posts tagged with

custom_properties

Orion2020.2.4

npm

Accepted answers

mesverrum

You could narrow your search by tweaking the queries to have an extra condition with your value in it, so tweaking the query on views and limits you could do

declare @Property as nvarchar(50) = 'State'
declare @Value as nvarchar(50) = 'Washington'

--views and resources
SELECT distinct v.viewgroupname, v.viewtitle, r.resourcetitle, r.resourcename, rp.propertyvalue, v.viewid
FROM sys.columns cp
join [dbo].[ResourceProperties] rp on propertyvalue like '%'+cp.Name+'%' and propertyvalue like '%'+@Value+'%'
join resources r on r.resourceid=rp.resourceid
join views v on v.viewid=r.viewid
WHERE cp.name like @Property

order by v.viewid

All comments

stuartd

I'd be interested in the answer to this as well.

Currently I just fire up Custom Properties manager and do a search for the CP I'm looking for. So using your example I'd search for Washington and if there are any, it would show me the nodes it is assigned to. Klunky but it does work

Being able to search direct from the GUI (via a widget) would be a real bonus.

WinterSoldier

Yes exactly. If someone can help us with the query that would be more helpful.

@KMSigma.SWI or Anyone can assist here?

KMSigma.SWI

The start for a query like that would be:

SELECT TOP 1000 Table, Field, IsForAlerting, IsForFiltering, IsForGrouping, IsForReporting, IsForEntityDetail, IsForAssetInventory
FROM Orion.CustomPropertyUsage

Which shows where each custom view is applies (the type of entity). Views, Groups, and Alerts are slightly different in the way they are implemented.

It would be incredibly helpful if you give me the reasoning and the way you'll use this information. I'd like to know how the information in this report would be used. Have you already checked out the Report Lab for something similar? I can't see this request being 100% unique, but there might at least be a starting point in there.

stuartd

Without jumping on Alex's question too much, then from my perspective we have just onboarded a new client that used a very old version of SolarWinds - but are now on the latest and greatest. One of the things their team relied on was heavy use of a search across CPs for various info which could be a Site ID, on call details and a bout a dozen other CPs. To make matters more awkward (for us anyway due to our poor SWQL skills) they want to search across different 'types' pf CPs.

We already have a search for CP's that fit into the node category but this type of global(?) search across any CP type without having to pre-select it being a node or IP or...etc would be hugely beneficial for us, let alone the onboarded team.

WinterSoldier

Hello @KMSigma.SWI

Here's the query that I have. This query is for Views & Resources

SELECT cp.Name as [Property Name], v.viewid, v.viewgroupname, v.viewtitle, r.resourcetitle, r.resourcename, rp.propertyname, rp.propertyvalue
FROM sys.columns cp
left join [dbo].[ResourceProperties] rp on propertyvalue like '%Force10%'
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

I need similar query for SWQL. The term Force10 is my CP and it has a value like (Yes/No). So I can find my CP 'Force10' in Views & resources

However, I am trying to find out something which shows me results for my CP having dropdown values.

Like I gave you an example of CP as State = 'Washington' or State = 'Texas'

mesverrum

I often had to go through this exercise when I was consulting, this is a report I used out there quite a bit.

It finds all the places where a custom property name is being referenced. The way it's written it doesn't do the mapping of values to check for every case of a single value, but thats a big ask because the formatting of things would vary widely, from your example State = 'Washington' could end up being something like

State='Washington'

State in ('Washington','other things')

State like 'Washington'

State like '%Washington%'

and a lot of other possibilities. The only way to really be certain is to review the results manually, but the report does make it relatively easy to do that. Covers views, groups definitions, alert conditions and actions, reports, account and view limitations.

It can take a long time to run if you have a big environment, so i usually prefer to do this query in SSMS so I can tweak it as i go to hunt for things.
[View:/cfs-file/__key/communityserver-discussions-components-files/4/Custom_2B00_Property_2B00_Impact_2B00_Assessment.xml:320:240]

mesverrum

You could narrow your search by tweaking the queries to have an extra condition with your value in it, so tweaking the query on views and limits you could do

declare @Property as nvarchar(50) = 'State'
declare @Value as nvarchar(50) = 'Washington'

--views and resources
SELECT distinct v.viewgroupname, v.viewtitle, r.resourcetitle, r.resourcename, rp.propertyvalue, v.viewid
FROM sys.columns cp
join [dbo].[ResourceProperties] rp on propertyvalue like '%'+cp.Name+'%' and propertyvalue like '%'+@Value+'%'
join resources r on r.resourceid=rp.resourceid
join views v on v.viewid=r.viewid
WHERE cp.name like @Property

order by v.viewid

WinterSoldier

Thank you for sharing details. Its like I am almost there. However, Can you please validate below query?

The term Colo is my CP name and 'ASP-30%' is my value. I am sure I did put Colo = 'ASP-30%' in multiple views. But when I am checking it through query its showing blank output.

declare @Property as nvarchar(50) = 'Colo'
declare @Value as nvarchar(50) = 'ASP-30%'

--views and resources
SELECT distinct TOP 5 v.viewgroupname, v.viewtitle, r.resourcetitle, r.resourcename, rp.propertyvalue, v.viewid
FROM sys.columns cp
join [dbo].[ResourceProperties] rp on propertyvalue like 'ASP-30%' and propertyvalue like 'ASP-30%'
join resources r on r.resourceid=rp.resourceid
join views v on v.viewid=r.viewid
WHERE cp.name like @Property

order by v.viewid