Forum Discussion

ldoodle's avatar
ldoodle
Icon for Advisor rankAdvisor
2 months ago

Filter within a widget

Hi, I have a custom datasource for some specific SQL metrics. Presently the collection script is explicitly excluding system databases (master, model, tempdb etc.) with a WHERE clause. I'd like to ...
ldoodle's avatar
ldoodle
Icon for Advisor rankAdvisor
2 months ago
WHERE
	[db].[name] NOT IN
	(
		 'master'
		,'model'
		,'tempdb'
		,'msdb'
	)

and 

WHERE
	[db].[name] IN
	(
		 'master'
		,'model'
		,'tempdb'
		,'msdb'
	)

 

Lewis_Beard's avatar
Lewis_Beard
Icon for Professor rankProfessor
2 months ago

The only things I can think of would be:

  • Custom Datasource with the Inverse Query (as you mentioned)

  • Include all databases in your query and instance name, but give an instance name prefix of s_ or n_ to represent system or non-system and use wild cards in your widget, such as s_* on one widget and n_* on the other

  • Include all databases in your query and instance name, but make sure the database name terminates the instance name. Use globs to include or exclude, example: *_(master|model|tempdb|msdb) ... though in testing globs generically to make sure it worked, I had a hard time making the inverse work


Ideally, one day LM would support both resource and instance property filters on all widgets. If they ever had that, then we could do these kinds of things cleanly. In this future hypothetical, you could put an auto property source in the code on your instances, make the datasource multi-instanced, make instance groupings based on the instance property, AND use the instance property in the widget.

But I dont think they are there quite yet.