In the previous post on User-Defined Collections (UDC's), we looked at using a view to hide the underlying objects of the query, and also the tip of setting the datatype to string.
In this final post, I'll show how to use a stored procedure in the UDC and a couple implications for that.
To call a stored procedure in the UDC, we simply use the "execute database.user.stored_procedure" syntax. Thanks for coming to my TED talk!
Using a stored procedure let's us hide the underlying objects from the Foglight agent user. We do need to remember to grant execute privileges on it, and the stored procedure needs to be created on each instance where we'll have a UDC.
After verifying the query, remember to set the "frequently modified", "is key", and "data type" fields appropriately. In this case, all fields were unchecked for frequently modified and set to string datatype.
We can see from the drilldown that the data is collecting properly. But what if instead of 1 or 0, we wanted to make it more clear?
Since we used a stored procedure, we can edit the sp definition instead of having to go back and edit the UDC in Foglight. The key here is to make sure you return the same columns in the same order that existed when you setup the UDC. In this case, we're adding a case statement to return yes or no instead of 1 or 0.
Once the stored procedure is re-created, we can see the change take place in Foglight:
I'm skipping a step here, but by now hopefully we're in the rhythm of creating a UI Query to return our UDC results across all instances. In this example I created one named "getSQLLogins3-sp" and dragged it onto a dashboard to create a table:
You can also do filtering at the dashboard level, so for the table above, I put a filter in place "where sysadmin contains 'Yes'" to only show those logins with sysadmin server role.
Now for one final tip on UDC's - the data obeys the time series - as it should. We can see in the dashboard below, that I moved the time range back several minutes, and I've highlighted "nothing". In the meantime, I created a login and granted it sysadmin.
Moving the time selector forward, we see that login appear - as it should.
Remember to hit like on the post, and if you have ideas or comments around UDC's, drop them in the comments.
