Can anyone tell me what table(s) in the database hold the information that is entered in the filter node or custom query sections, for resources on views? I need to run some queries to get out what we are filtering on.
Thanks!
haasjohn In regards to finding the "Custom Query" SWQL queries currently being used , if you are running the latest releases, you should be able to find them using the following SWQL query:
SELECTrp.ResourceID,r.ResourceName,r.ResourceTitle,r.ResourceFile,rp.PropertyName,rp.PropertyValue,rp.UriFROM Orion.ResourceProperties AS rpJOIN Orion.Resources AS r ON rp.ResourceID=r.ResourceIDWHERE rp.ResourceID IN (SELECT ResourceID FROM Orion.ResourceProperties WHERE (PropertyName LIKE '%swql%' OR PropertyName LIKE '%sql%') AND PropertyValue IS NOT NULL AND PropertyValue != '')
Is this what you are looking for?
Thank you,
-Will
haasjohn Otherwise, if you're just looking for the filter part of regular widgets, I think you can just swap out that "WHERE" line, using the following instead:
WHERE PropertyName LIKE '%filter%'
Let us know if this is what you're looking for.
Hopefully this is the same John Haas I'm thinking of in STL, if you want I can email you a sql based report I use to audit all the custom properties. Shows any cp being used in alerts, reports, dashboard resources, and account/view limitations.
The only gotcha is that you can possibly get false positives for any custom props that are part of longer words, such as City matching anything with the word capacity in it.
Thanks guys. Yeh, it’s me. Been out sick. Should be back in tomorrow. Hope you’re well.
