What happens behind the scenes when you run a PowerShell Command for SCOM?

So have you ever wondered what happens when you run a PowerShell command against your SCOM environment? Of course you have or you wouldn’t be here. Well you probably wouldn’t be surprised to learn that behind the scenes a lot of PowerShell cmdlets are really only performing a SQL query against your SCOM Databases.

For example, let’s consider the PowerShell cmdlet “Get-Monitor”. By itself this cmdlet will return all of the monitors that you have available in your environment.

Get-Monitor

Now behind the scenes PowerShell is really hitting the SQL Database and asking for all of this information to be returned. But by using the SQL Profiler we can peek into what SQL is doing.

And this is what it runs: You can quite happily paste this into New Query window in SQL Management Studio and run this against your “Operations Database” and it will return all Monitors, just the same as running Get-Monitor does.

exec sp_executesql N’– MonitorByCriteria <LanguageCode1,LanguageCode2,IncludeConfiguration>
SELECT
[MonitorView].[Id],
[MonitorView].[MonitorTypeId],
[MonitorView].[ParentMonitorId],
[MonitorView].[IsUnitMonitor],
[MonitorView].[IsInternalRollupMonitor],
[MonitorView].[IsExternalRollupMonitor],
[MonitorView].[ManagementPackId],
[MonitorView].[Name],
[MonitorView].[Accessibility],
[MonitorView].[TargetMonitoringClassId],
[MonitorView].[Enabled],
[MonitorView].[Algorithm],
[MonitorView].[AlgorithmParameter],
[MonitorView].[MonitoringRelationshipClassId],
[MonitorView].[MemberMonitorId], CASE @IncludeConfiguration WHEN 1 THEN ConfigurationXml ELSE NULL END AS ConfigurationXml,
[MonitorView].[Category],
[MonitorView].[RunAsId],
[MonitorView].[ConfirmDelivery],
[MonitorView].[MemberMonitorName],
[MonitorView].[DisplayName],
[MonitorView].[Description],
[MonitorView].[Priority],
[MonitorView].[Remotable],
[MonitorView].[AlertPriority],
[MonitorView].[AlertOnState],
[MonitorView].[AlertAutoResolve],
[MonitorView].[AlertSeverity],
[MonitorView].[AlertMessage],
[MonitorView].[AlertParameter1],
[MonitorView].[AlertParameter2],
[MonitorView].[AlertParameter3],
[MonitorView].[AlertParameter4],
[MonitorView].[AlertParameter5],
[MonitorView].[AlertParameter6],
[MonitorView].[AlertParameter7],
[MonitorView].[AlertParameter8],
[MonitorView].[AlertParameter9],
[MonitorView].[AlertParameter10],
[MonitorView].[MemberInMaintenance],
[MonitorView].[MemberUnAvailable],
[MonitorView].[TimeAdded],
[MonitorView].[LastModified],
[MonitorView].[LanguageCode],
[MonitorView].[HasNonCategoryOverride]
FROM dbo.fn_MonitorView(@LanguageCode1, @LanguageCode2)
AS MonitorView’,N’@LanguageCode1 varchar(3),@LanguageCode2 varchar(3),@IncludeConfiguration bit’,@LanguageCode1=’ENU’,@LanguageCode2=NULL,@IncludeConfiguration=1

 

The more SQL-orientated readers will see that this is simply pulling the information from the MonitorView Table. In fact in the SQL Management Studio if you head over to Views and do a SELECT on it, it will basically return the same information.

New SQL Query

 

Another example, the “Get-Rule” cmdlet. What does that do?

It produces this SQL:

exec sp_executesql N’– Rules <LanguageCode1,LanguageCode2,LimitToContentReadable>
SELECT
[RuleView].[Id],
[RuleView].[Name],
[RuleView].[ManagementPackId],
[RuleView].[TargetMonitoringClassId],
[RuleView].[Category],
[RuleView].[Enabled],
[RuleView].[ConfirmDelivery],
[RuleView].[DisplayName],
[RuleView].[Description],
[RuleView].[Priority],
[RuleView].[Remotable],
[RuleView].[DiscardLevel],
[RuleView].[TimeAdded],
[RuleView].[LastModified],
[RuleView].[LanguageCode],
[RuleView].[HasNonCategoryOverride] FROM dbo.fn_RuleView(@LanguageCode1, @LanguageCode2) AS RuleView
WHERE ((RuleView.ContentReadable = 1))’,N’@LanguageCode1 varchar(3),@LanguageCode2 varchar(3),@LimitToContentReadable bit’,@LanguageCode1=’ENU’,@LanguageCode2=NULL,@LimitToContentReadable=1

 

It’s not really rocket science, it’s all SQL on the backend and something needs to pull all of that information from the database. So if you ever wondered what that SCOM cmdlet is doing, run the SQL Profiler and find out!

 
Comments

No comments yet.

Leave a Reply