SQL queries

-

Note: Not sure if these are solid so you may need to tweak.

Common stuff

Variables

declare @MyVar as varchar(100)
set @MyVar = '119084%'
select *
FROM [commserv].[dbo].[CommCellBackupInfo]
where jobid like @MyVar

Insert row

insert into [SCOMCmdb].[dbo].[Configuration]
(Name, Class, Category, status, comment)
values ('server.name', 'FederationServer', 'CatA', 'Project', 'Test jump box.')

Delete row

delete from [SCOMCmdb].[dbo].[Configuration]
where Name ='server.name' and Class='WindowsServer'

Update existing row

update [SCOMCmdb].[dbo].[Configuration]
set status = 'Active', Category = 'CatB'
where Name = 'server.name'
and Class = 'IISWebServer'

Get unique rows from column

SELECT distinct class from [SCOMCmdb].[dbo].[Configuration]

Inner Join

SELECT column_name(s)
FROM table1 tb1
INNER JOIN table2 tb2
ON tb1.column_name = tb2.column_name --these have to match.

SCO

Get runbooks with logging enabled

USE Orchestrator
SELECT * FROM POLICIES WHERE LogCommonData = 1 OR LogSpecificData = 1

Get runbook info (name, path etc)

USE Orchestrator
SELECT v.Id, v.Name, LEFT(v.Path, LEN(v.Path)-LEN(Name)) as [Path], v.IsMonitor, v.CreationTime, v.LastModifiedTime, v.CheckedOutTime, v.CheckedOutBy
FROM [Microsoft.SystemCenter.Orchestrator].[Runbooks] v --this is a view
ORDER BY Name, [Path]

Get concurrent runbook limit

USE Orchestrator
SELECT MaxRunningPolicies FROM ACTIONSERVERS

Get events

SELECT * FROM [Orchestrator].[dbo].[EVENTS]
ORDER BY DateTime DESC

Get runbook servers

Table

SELECT * FROM [Orchestrator].[dbo].[ACTIONSERVERS]

View

SELECT * FROM [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime].[RunbookServers]

Can't remove orphaned runbook instances

I couldn't remove orphaned runbook instances even after running this SQL command recommended in  this  article:

USE YourScoDb
DECLARE @return_value int
EXEC @return_value = [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[ClearOrphanedRunbookInstances]
SELECT 'Return Value' = @return_value

The query would always run for 30+ minutes.

Then I followed  this  article and they cleared out. It's probably extreme but it worked.

In short, the steps are:
  1. Stop all running runbooks
  2. Stop all Orchestrator services - I didn't do that
  3. Run this command:

USE YourScoDb
 DELETE FROM POLICYINSTANCES
 TRUNCATE TABLE OBJECTINSTANCES
 TRUNCATE TABLE OBJECTINSTANCEDATA
 TRUNCATE TABLE [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs

Get runbooks that should be running (but ain't)

This will return selected runbooks that are not running.

These are the different states for a job:

  • 0 - Pending
  • 1 - Running
  • 2 - Failed
  • 3 - Cancelled
  • 4 - Completed

WITH GetRunbookState AS
(
SELECT Runbook.Id AS [Id], Runbook.Name AS [Name], LEFT(Path, LEN(Path)-LEN(Name)) as [Path],
CASE WHEN ID IN (SELECT PPQ.PolicyID FROM [Orchestrator].dbo.[POLICY_PUBLISH_QUEUE] PPQ)
THEN 'Running'
ELSE 'NotRunning'
END AS [State]
FROM [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] Runbook
WHERE Runbook.IsMonitor = '1'
AND Runbook.Path = '\RunbookPath1'
OR Runbook.Path = '\RunbookPath2'
OR Runbook.Id = 'BC50A2CB-E436-4CE7-9B46-E556AF1D429D'
OR Runbook.Id = 'D908E46C-B0C5-4F45-9B7F-EF02150D5E4F'
)
SELECT * FROM GetRunbookState
WHERE [State] = 'NotRunning'

SCCM

Get server OS

use cm_cas
select Netbios_Name0, Full_Domain_Name0, Operating_System_Name_and0, Last_Logon_Timestamp0
from vSMS_R_System
where vSMS_R_System.Operating_System_Name_and0 like '%server%'

Get collections

select *
FROM [CM_CAS].[dbo].[vCollections]
--where CollectionName = 'add_name'
--where ObjectPath like 'add_path'
order by CollectionName

SCOM

Get database size/used space

This shows the DB and log file size plus the used/free space in each.

SELECT convert(decimal(12,0),round(sf.size/128.000,2)) AS 'FileSize(MB)', 
convert(decimal(12,0),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)) AS 'SpaceUsed(MB)', 
convert(decimal(12,0),round((sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)) AS 'FreeSpace(MB)', 
CASE smf.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR(10),smf.growth) +' %' ELSE convert(VARCHAR(10),smf.growth/128) +' MB' END AS 'AutoGrow',
convert(decimal(12,0),round(sf.maxsize/128.000,2)) AS 'AutoGrowthMB(MAX)',
left(sf.NAME,15) AS 'NAME', 
left(sf.FILENAME,120) AS 'PATH',
sf.FILEID
from dbo.sysfiles sf
JOIN sys.master_files smf on smf.physical_name = sf.filename

Get all alerts last x days

This is a basic query that gets all the main alert properties you generally need. Make sure you specifiy a value for @Days otherwise it will pull back all alerts. Add '*' if you need to see more columns.

Notice the duplicate val.AlertGuid rows. This is because a new row is added as changes are made to each alert i.e. update resolution state. This can be tricky if you only want one row returned i.e. you want a report on alerts closed by users - just need to figure out how to return the most recent row.

Use OperationsManagerDW
 DECLARE @Days INT
 SET @Days = 100 --get alerts from last x days
 SELECT
 val.AlertGuid,
 CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,val.RaisedDateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as [RaisedDateTime], -- convert local time.
 CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,val.DWLastModifiedDateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as [DWLastModifiedDateTime], -- convert local time.
 val.AlertName,
 vEntity.Path,
 vad.TicketId,
 case
 when val.Severity='0' then 'Information' --(0=Information, 1=Warning, 2=Critical)
 when val.Severity='1' then 'Warning'
 when val.Severity='2' then 'Critical'
 end as Severity,
 case
 when val.Priority = '0' then 'Low' --(0=Low, 1=Medium, 2=High)
 when val.Priority = '1' then 'Medium'
 when val.Priority = '2' then 'High'
 end as Priority,
 case
 when val.MonitorAlertInd='0' then 'Rule'
 when val.MonitorAlertInd='1' then 'Monitor'
 end as WorkflowType,
 case
 when [var].ResolutionState = '0' then 'New'
 when [var].ResolutionState = '1' then 'Assigned'
 when [var].ResolutionState = '249' then 'Acknowledged'
 when [var].ResolutionState = '255' then 'Closed'
 end as ResolutionState,
 [var].StateSetByUserId,
 val.AlertDescription
 from alert.vAlert val
 inner join alert.vAlertDetail vad
 on val.AlertGuid = vad.AlertGuid
 inner join alert.vAlertParameter vap
 on vap.AlertGuid = val.AlertGuid
 inner join alert.vAlertResolutionState [var]
 on [var].AlertGuid = val.AlertGuid
 inner join vManagedEntity vEntity
 on val.ManagedEntityRowId = vEntity.ManagedEntityRowId
 WHERE val.RaisedDateTime > GETDATE()-@Days
 order by val.AlertGuid, val.RaisedDateTime

Get closed alerts with unhealthy monitors

This is a tricky one. I've had a few goes at this (queries 2,3) but I think query 1 is the most accurate. The only problem is it doesn't look at alert status. Use all three to get the job done.

Query 1

You might need to comment/uncomment certain lines to get the info you need.

use OperationsManager
SELECT bme.path, -- This might be comp name.
bme.DisplayName, -- This might be comp name.
mv.DisplayName as AlertName,
mv.Name AS 'MonitorName',
CASE
when s.HealthState = '0' then 'Not monitored'
when s.HealthState = '1' then 'Success'
when s.HealthState = '2' then 'Warning'
when s.HealthState = '3' then 'Error'
end as HealthState,
s.LastModified AS 'StateLastModified'
--mv.DisplayName AS 'MonitorDisplayName',
--bme.FullName AS 'Target',
FROM State s
JOIN BaseManagedEntity bme ON s.BaseManagedEntityId = bme.BaseManagedEntityId
JOIN MonitorView mv ON mv.Id = s.MonitorId
WHERE s.HealthState > 1 -- 0=Not monitored or MM, 1=Success, 2=Warning, 3=Error
and mv.IsUnitMonitor = 1
--and mv.Name like '%trellix%' -- filter on monitor name
--and bme.path = 'COMPUTER' -- filter on computer name
--and bme.DisplayName = 'COMPUTER' -- filter on computer name
order by bme.path, s.LastModified, mv.Name

Query 2

use OperationsManagerDW
select distinct f.Path, a.AlertName--, b.StateSetByUserId
from Alert.vAlert a
inner join alert.vAlertResolutionState b on a.AlertGuid = b.AlertGuid
inner join alert.vAlertDetail c on c.AlertGuid = b.AlertGuid
inner join [OperationsManager].[dbo].[MonitorView] d on a.AlertProblemGuid = d.Id
inner join [OperationsManager].[dbo].[State] e on d.Id = e.MonitorId
inner join [OperationsManager].[dbo].[BaseManagedEntity] f ON e.BaseManagedEntityId = f.BaseManagedEntityId
where b.StateSetByUserId NOT IN ('System', 'Maintenance Mode', 'Auto-resolve', 'ANY_OTHER_NAMES')
and a.AlertName NOT IN ('Exchange Health Set')
and (b.ResolutionState = 255 or b.ResolutionState = 254)
and a.MonitorAlertInd = 1
and e.HealthState > 1 --0=Not monitored or MM, 1=Success, 2=Warning, 3=Error
and d.IsUnitMonitor = 1
order by f.path, a.AlertName

Query 3

This doesn't use DISTINCT. It should get the most recent record by date (to avoid duplicates) and might be a better option. This doesn't filter out usernames.

use OperationsManagerDW
select f.path as Ojbect, a.AlertName, d.Name, MAX(a.RaisedDateTime) LatestRecord
from Alert.vAlert a
inner join alert.vAlertResolutionState b on a.AlertGuid = b.AlertGuid
inner join alert.vAlertDetail c on c.AlertGuid = b.AlertGuid
inner join [OperationsManager].[dbo].[MonitorView] d on a.AlertProblemGuid = d.Id
inner join [OperationsManager].[dbo].[State] e on d.Id = e.MonitorId
inner join [OperationsManager].[dbo].[BaseManagedEntity] f ON e.BaseManagedEntityId = f.BaseManagedEntityId
where (b.ResolutionState = 255 or b.ResolutionState = 254) -- Closed or Resolved.
and a.MonitorAlertInd = 1
and e.HealthState > 1 --0=Not monitored or MM, 1=Success, 2=Warning, 3=Error
and d.IsUnitMonitor = 1
and a.AlertName like '%trellix%' -- Filter alert name.
group by f.Path, a.AlertName, d.Name
order by f.Path, a.AlertName

Get alerts closed by users

This will show alerts that have been closed by users. The query filters out non-user accounts System, Maintenance Mode, Auto-resolve. I can't find a column that shows the time the alert was closed, the closest thing is the LastModified column.

Use OperationsManagerDW
 DECLARE @Days INT
 SET @Days = 30 --get alerts from last x days
 SELECT
 CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,vAlert.RaisedDateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as [TimeCreated], -- convert local time.
 CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,vAlert.DWLastModifiedDateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as [LastModified], -- convert local time.
 vAlert.AlertName,
 Path,
 case
 when vAlert.Severity='0' then 'Information' --(0=Information, 1=Warning, 2=Critical) (0=Low, 1=Medium, 2=High)
 when vAlert.Severity='1' then 'Warning'
 when vAlert.Severity='2' then 'Critical'
 end as Severity,
 case
 when vAlert.Priority = '0' then 'Low'
 when vAlert.Priority = '1' then 'Medium'
 when vAlert.Priority = '2' then 'High'
 end as Priority,
 case
 when valert.MonitorAlertInd='0' then 'Rule'
 when valert.MonitorAlertInd='1' then 'Monitor'
 end as WorkflowType,
 case
 when vres.ResolutionState = '0' then 'New'
 when vres.ResolutionState = '1' then 'Assigned'
 when vres.ResolutionState = '249' then 'Acknowledged'
 when vres.ResolutionState = '255' then 'Closed'
 end as ResolutionState,
 vres.StateSetByUserId,
 vAlert.AlertDescription
 FROM
 alert.vAlert vAlert
 inner join vManagedEntity vEntity
 on vAlert.ManagedEntityRowId = vEntity.ManagedEntityRowId
 inner join alert.vAlertResolutionState vres
 on valert.AlertGuid = vres.AlertGuid
 WHERE ResolutionState = '255'
 AND RaisedDateTime > GETDATE()-@Days
 and StateSetByUserId NOT IN ('System', 'Maintenance Mode', 'Auto-resolve')
 order by RaisedDateTime desc

Get alerts by name

Use OperationsManagerDW
 DECLARE @AlertName VARCHAR(MAX), @Days INT
 SET @AlertName = '%pop%' -- put alert name here.
 SET @Days = 20 --get alerts from last x days
 SELECT
 CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,vAlert.RaisedDateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as [TimeCreated], -- convert local time.
 vAlert.AlertName,
 Path,
 case
 when vAlert.Severity='0' then 'Information' --(0=Information, 1=Warning, 2=Critical) (0=Low, 1=Medium, 2=High)
 when vAlert.Severity='1' then 'Warning'
 when vAlert.Severity='2' then 'Critical'
 end as Severity,
 case
 when vAlert.Priority = '0' then 'Low'
 when vAlert.Priority = '1' then 'Medium'
 when vAlert.Priority = '2' then 'High'
 end as Priority,
 case
 when valert.MonitorAlertInd='0' then 'Rule'
 when valert.MonitorAlertInd='1' then 'Monitor'
 end as WorkflowType,
 case
 when vres.ResolutionState = '0' then 'New'
 when vres.ResolutionState = '1' then 'Assigned'
 when vres.ResolutionState = '249' then 'Acknowledged'
 when vres.ResolutionState = '255' then 'Closed'
 end as ResolutionState,
 vAlert.AlertDescription
 FROM
 alert.vAlert vAlert
 inner join vManagedEntity vEntity
 on vAlert.ManagedEntityRowId = vEntity.ManagedEntityRowId
 inner join alert.vAlertResolutionState vres
 on valert.AlertGuid = vres.AlertGuid
 WHERE vAlert.AlertName like @AlertName
 AND RaisedDateTime > GETDATE()-@Days
 order by RaisedDateTime desc

Get all alert history

This query is unconfirmed.

This gets all alerts and shows rule/monitor/mp info. You can filter with the variables to reduce the list:

You need to set 4 variables:

  • @Severity - filter by alert severity
  • @Priority - filter by alert priority
  • @MP - filter by mp display name (not dotted name)
  • @Days - filter by last x days

Comment out @Severity and @Priority to get all alerts.

USE OperationsManagerDW
 DECLARE @Severity INT, @Priority INT, @MP VARCHAR(MAX), @Days INT
 SET @Severity = 1 --use for filtering (0=Information, 1=Warning, 2=Critical)
 SET @Priority = 1 --use for filtering (0=Low, 1=Medium, 2=High)
 SET @MP = '%' --this returns all MPs. Filter with '%sql%' for example.
 SET @Days = 365 --get alerts from last x days
 SELECT
 'Monitor' [Type],
 a.AlertName,
 a.Priority,
 a.Severity,
 a.WorkflowRowId,
 m.MonitorDefaultName [MonitorRuleDisplayName],
 m.MonitorSystemName [MonitorRuleName],
 mp.ManagementPackDefaultName [MpDisplayName],
 mp.ManagementPackSystemName [MpName],
 MIN(a.RaisedDateTime) RaisedDateTime
 FROM Alert.vAlert a
 JOIN dbo.vMonitor m ON a.WorkflowRowId = m.MonitorRowId
 JOIN dbo.vManagementPack mp ON m.ManagementPackRowId = mp.ManagementPackRowId
 WHERE mp.ManagementPackDefaultName LIKE @MP
 AND a.RaisedDateTime > GETDATE()-@Days
 AND a.MonitorAlertInd = 1
 AND a.Severity = @Severity --remove if needed
 AND a.Priority = @Priority --remove if needed
 GROUP BY
 a.AlertName,
 a.Priority,
 a.Severity,
 a.WorkflowRowId,
 m.MonitorDefaultName,
 m.MonitorSystemName,
 mp.ManagementPackDefaultName,
 mp.ManagementPackSystemName
 UNION ALL
 SELECT
 'Rule' [Type],
 a.AlertName,
 a.Priority,
 a.Severity,
 a.WorkflowRowId,
 r.RuleDefaultName,
 r.RuleSystemName,
 mp.ManagementPackDefaultName,
 mp.ManagementPackSystemName,
 MIN(a.RaisedDateTime) RaisedDateTime
 FROM Alert.vAlert a
 JOIN dbo.vRule r ON a.WorkflowRowId = r.RuleRowId
 JOIN dbo.vManagementPack mp ON r.ManagementPackRowId = mp.ManagementPackRowId
 WHERE mp.ManagementPackDefaultName LIKE @MP
 AND a.RaisedDateTime > GETDATE()-@Days
 AND a.MonitorAlertInd = 0
 AND a.Severity = @Severity --remove if needed
 AND a.Priority = @Priority --remove if needed
 GROUP BY
 a.AlertName,
 a.Priority,
 a.Severity,
 a.WorkflowRowId,
 r.RuleDefaultName,
 r.RuleSystemName,
 mp.ManagementPackDefaultName,
 mp.ManagementPackSystemName
 ORDER BY [Type], [MonitorRuleName]

Get Windows agents

USE OperationsManager
SELECT UPPER(DisplayName) AS PrincipalName
FROM MT_Microsoft$SystemCenter$Agent sca
INNER JOIN BaseManagedEntity bme
ON sca.BaseManagedEntityId = bme.BaseManagedEntityId
ORDER BY DisplayName

Get computer maintenance mode history

This will get all maintenance mode history for a Windows/Linux/UNIX computer.

Note: If you don't get anything returned, chances are the  Microsoft.Windows.Computer class wasn't used to put the computer into maintenance mode. This happens when people apply MM on the alert. To see all classes in MM, uncomment all references to @Class.

You need to set 2 variables:

  • @Class - set to Microsoft.Windows.Computer or Microsoft.Unix.Computer
  • @Object - set to computer FQDN

USE OperationsManagerDW
 DECLARE @TimeOffset INT, @Class VARCHAR(MAX), @Object VARCHAR(MAX)
 SET @TimeOffset = DATEDIFF (HH, GETUTCDATE(), GETDATE())
 SET @Class = '' --update
SET @Object = '' --update
SELECT me.DisplayName,
 FORMAT(DATEADD(HOUR, @TimeOffset, mmh.DBLastModifiedDateTime), 'dd-MM-yyyy HH:mm') AS StartTime,
 FORMAT(DATEADD(HOUR, @TimeOffset, mmh.ScheduledEndDateTime), 'dd-MM-yyyy HH:mm') AS StopTime,
 CASE WHEN DATEDIFF(hh, mmh.DBLastModifiedDateTime, mmh.ScheduledEndDateTime) < 24 THEN '0' ELSE CONVERT(VARCHAR(10),DATEDIFF(dd, mmh.DBLastModifiedDateTime, mmh.ScheduledEndDateTime)) END + 'd ' +
 CASE WHEN DATEDIFF(mi, mmh.DBLastModifiedDateTime, mmh.ScheduledEndDateTime) < 60 THEN '0' ELSE CONVERT(VARCHAR(10),DATEDIFF(hh, mmh.DBLastModifiedDateTime, mmh.ScheduledEndDateTime) % 24) END + 'h ' +
 CONVERT(VARCHAR(10),DATEDIFF(mi, mmh.DBLastModifiedDateTime, mmh.ScheduledEndDateTime) % 60) + 'm' AS Duration,
 mmh.Comment,
 mmh.UserId AS Account,
 'Type' = CASE mmh.PlannedMaintenanceInd
 WHEN 1 THEN 'Scheduled'
 WHEN 0 THEN 'Unscheduled'
 ELSE 'Unknown'
 END,
 'Category' = CASE mmh.ReasonCode
 WHEN 0 THEN 'Other (Planned)'
 WHEN 1 THEN 'Other (Unplanned)'
 WHEN 2 THEN 'Hardware: Maintenance (Planned)'
 WHEN 3 THEN 'Hardware: Maintenance (Unplanned)'
 WHEN 4 THEN 'Hardware: Installation (Planned)'
 WHEN 5 THEN 'Hardware: Installation (Unplanned)'
 WHEN 6 THEN 'Operating System: Reconfiguration (Planned)'
 WHEN 7 THEN 'Operating System: Reconfiguration (Unplanned)'
 WHEN 8 THEN 'Application: Maintenance (Planned)'
 WHEN 9 THEN 'Application: Maintenance (Unplanned)'
 WHEN 10 THEN 'Application: Installation (Planned)'
 WHEN 11 THEN 'Application: Unresponsive'
 WHEN 12 THEN 'Application: Unstable'
 WHEN 13 THEN 'Security Issue'
 WHEN 14 THEN 'Loss of network connectivity (Unplanned)'
 END
 FROM ManagedEntity me WITH (NOLOCK)
 INNER JOIN
 MaintenanceMode mm (NOLOCK) ON me.ManagedEntityRowId = mm.ManagedEntityRowId
 INNER JOIN
 MaintenanceModeHistory mmh (NOLOCK) ON mm.MaintenanceModeRowId = mmh.MaintenanceModeRowId
 WHERE FullName = @Class+':'+@Object
 ORDER BY mmh.DBLastModifiedDateTime DESC

Get Unix / Linux agents

USE OperationsManager
 SELECT UPPER(DisplayName) AS PrincipalName
 FROM MT_Microsoft$Unix$Computer
 ORDER BY DisplayName

Get Windows clusters info

This will show Windows cluster names, nodes and node OS and matches what's in the SCOM console. You will need to update the CASE statement as new OSs are released.

This helped with identifying OSs but also had to Google a bit.

USE OperationsManager
SELECT
UPPER(cl.DisplayName) AS ClusterName,
nr.PrincipalName AS ComputerName,
CASE
WHEN OSVersion_3EA023B7_F70D_3284_71CE_6B287C837724='10.0.14393' THEN 'Microsoft Windows Server 2016'
WHEN OSVersion_3EA023B7_F70D_3284_71CE_6B287C837724='6.3.9600' THEN 'Microsoft Windows Server 2012 R2'
WHEN OSVersion_3EA023B7_F70D_3284_71CE_6B287C837724='6.1.7601' THEN 'Microsoft Windows Server 2008 R2'
END AS OperatingSystem
FROM MT_Microsoft$Windows$Cluster cl
INNER JOIN MT_Microsoft$Windows$Cluster$Node cn ON cn.ClusterName_B70F3ED5_E421_C995_F2FE_F377AA50368E = cl.DisplayName
inner join MTV_Microsoft$Windows$Cluster$NodeRole nr ON nr.DisplayName = cn.NodeName_1C5DA5AC_4171_58A3_DCC7_63D6C7BBDDAB
--where cl.DisplayName = 'add cluster name to filter'
ORDER BY cl.DisplayName, nr.PrincipalName

I've been trying to do this without a CASE statement as it's neater but it's returing more nodes so it's not reliable. Here's the code:

USE OperationsManager
 SELECT distinct
 cl.DisplayName AS ClusterName,
 nr.PrincipalName AS ComputerName,
 os.DisplayName AS OperatingSystem
 FROM MT_Microsoft$Windows$OperatingSystem os
 inner join MT_Microsoft$Windows$Cluster cl ON cl.OSVersion_3EA023B7_F70D_3284_71CE_6B287C837724 = os.OSVersion_53D6DEB6_BE2E_D1B6_D49E_A623518BD867
 inner JOIN MT_Microsoft$Windows$Cluster$Node cn ON cl.DisplayName=cn.ClusterName_B70F3ED5_E421_C995_F2FE_F377AA50368E
 inner join MTV_Microsoft$Windows$Cluster$NodeRole nr ON nr.DisplayName = cn.NodeName_1C5DA5AC_4171_58A3_DCC7_63D6C7BBDDAB
 ORDER BY cl.DisplayName, nr.PrincipalName

Get single alert history

This will get alert history for a single alert. This is useful for the "SCOM didn't alert" arguments.

You need to set 1 variable:

  • @AlertName - the name of the alert

USE OperationsManagerDW
DECLARE @TimeOffset INT, @AlertName VARCHAR(MAX)
SET @TimeOffset = DATEDIFF (HH, GETUTCDATE(), GETDATE())
SET @AlertName = '' --add alert name here.
SELECT DISTINCT a.AlertGuid, a.AlertName, a.AlertProblemGuid AS RuleMonitorId, a.RaisedDateTime AS UtcTime,
FORMAT(DATEADD(HOUR, @TimeOffset, RaisedDateTime), 'dd-MM-yyyy HH:mm:ss') AS [LocalTime]
FROM [OperationsManagerDW].[Alert].[vAlert] a
INNER JOIN [OperationsManagerDW].[Alert].[vAlertDetail] b
ON a.[AlertGuid]=b.[AlertGuid]
WHERE AlertName = @AlertName
ORDER BY a.RaisedDateTime DESC

Get top 10 low free space on C: drives

use OperationsManager
 select top 10 Path, InstanceName, CAST(SampleValue/1024 as decimal(10,1)) as 'GB_Free', TimeSampled
 from (select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled,
 ROW_NUMBER() over (PARTITION by path order by TimeSampled desc) as seqnum
 from PerformanceDataAllView pdv with (NOLOCK) 
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid 
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId 
 where objectname = 'LogicalDisk' 
 AND countername = 'Free Megabytes'
 and InstanceName = 'c:'
 ) t
 where seqnum = 1
 order by SampleValue

Get all disks with less than 10GB free

I think this is working but needs more testing. This is enough to get you started.

The where lines are the interesting ones. I still don't understand how they work.

use OperationsManager
 select top 10 Path, InstanceName, SampleValue, TimeSampled
 from (select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled,
 ROW_NUMBER() over (PARTITION by path order by TimeSampled desc) as seqnum
 from PerformanceDataAllView pdv with (NOLOCK) 
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid 
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId 
 where objectname = 'LogicalDisk' 
 AND countername = 'Free Megabytes'
 and InstanceName = 'c:'
 ) t
 where seqnum = 1
 --and SampleValue < '5000'
 --and InstanceName = 'c:'
 order by SampleValue

Basic query to get C: drive counter

Need to figure out how to pull back last sampled value from single computer

select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where path = 'server_fqdn'
AND objectname = 'LogicalDisk'
AND countername = 'Free Megabytes'
AND InstanceName = 'c:'
order by timesampled DESC

Get every perf counter - single computer

select Distinct Path, ObjectName, CounterName, InstanceName
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where path = 'server_fqdn'
order by objectname, countername, InstanceName

CPU % Processor Time - single computer

select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
 from PerformanceDataAllView pdv with (NOLOCK)
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
 where path = 'server_fqdn'
 AND objectname = 'Processor Information'
 AND countername = '% Processor Time'
 order by timesampled DESC

CPU % Processor Time - single computer

select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
 from PerformanceDataAllView pdv with (NOLOCK)
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
 where path = 'server_fqdn'
 AND objectname = 'System'
 AND countername = 'Processor Queue Length'
 order by timesampled DESC

Get memory Available MBytes - single computer

select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
 from PerformanceDataAllView pdv with (NOLOCK)
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
 where path = 'server_fqdn'
 AND objectname = 'Memory'
 AND countername = 'Available MBytes'
 order by timesampled DESC

Get memory PercentMemoryUsed - single computer

select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
 from PerformanceDataAllView pdv with (NOLOCK)
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
 where path = 'server_fqdn'
 AND objectname = 'Memory'
 AND countername = 'PercentMemoryUsed'
 order by timesampled DESC

Get C: drive space from group members

This needs work, reports multiple instances.

use OperationsManagerDW
select  vme.Path,  InstanceName ,MaxValue, MinValue,AverageValue, convert(date,vpd.DateTime )
from Perf.vPerfDaily vpd
inner join vManagedEntity vme on vpd.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on vpd.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE objectname = 'LogicalDisk' and CounterName = 'Free Megabytes' and InstanceName = 'C:'
and path in (
select vme1.displayname from vRelationship vr
inner join vManagedEntity vme on vr.SourceManagedEntityRowId=vme.ManagedEntityRowId
inner join vManagedEntity vme1 on vr.TargetManagedEntityRowId=vme1.ManagedEntityRowId
where vme.displayName='CMDB Windows Server CatA')

Get Exchange counter info from Data warehouse

This is a good query to get Exchange counter info from the data warehouse. It's raw data but is a good starting point if you need to dig deeper using other queries.

use OperationsManagerDW
 DECLARE @Days INT
 SET @Days = 30 --get alerts from last x days
 SELECT 
 CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,vph.DateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as [LocalTime], -- convert local time.
 vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName, vPerformanceRuleInstance.InstanceName,
 ROUND(vph.MaxValue, 1) AS Milliseconds
 FROM Perf.vPerfHourly AS vph 
 INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vph.PerformanceRuleInstanceRowId 
 INNER JOIN vManagedEntity ON vph.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 
 INNER JOIN vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
 WHERE vPerformanceRule.ObjectName = 'Exchange Database' 
 --AND vPerformanceRule.CounterName IN ('I/O Log Reads Average Latency (ms)')
 AND vManagedEntity.Path like '%FQDN%'
 and MaxValue > 50
 AND vph.DateTime > GETDATE()-@Days
 order by LocalTime desc

Get group members

This seems reliable. It just returns names in the group, not class type of members.

use OperationsManager
 select TargetObjectName
 from RelationshipGenericView 
 where SourceObjectFullName = 'Cmdb.Group.WindowsServerCatA'
 and isDeleted=0
 ORDER BY TargetObjectDisplayName

Get Windows agents in maintenance mode

use OperationsManager
 SELECT DisplayName AS 'Server Name', StartTime AS 'Start Time', ScheduledEndTime AS 'End Time', [User] AS 'Scheduled By',
 CASE tb1.ReasonCode
 WHEN '0' THEN 'Other (Planned)' 
 WHEN '1' THEN 'Other (Unplanned)' 
 WHEN '2' THEN 'Hardware: Maintenance (Planned)' 
 WHEN '3' THEN 'Hardware: Maintenance (Unplanned)' 
 WHEN '4' THEN 'Hardware: Installation (Planned)' 
 WHEN '5' THEN 'Hardware: Installation (Unplanned)' 
 WHEN '6' THEN 'Operating System: Reconfiguration (Planned)' 
 WHEN '7' THEN 'Operating System: Reconfiguration (Unplanned)' 
 WHEN '8' THEN 'Application: Maintenance (Planned)' 
 WHEN '9' THEN 'Application: Maintenance (Unplanned)' 
 WHEN '10' THEN 'Application: Installation (Planned)' 
 WHEN '11' THEN 'Application: Unresponsive' 
 WHEN '12' THEN 'Application:  Unstable' 
 WHEN '13' THEN 'Security Issue' 
 WHEN '14' THEN 'Loss of network connectivity (Unplanned)' 
 END AS 'Category',
 tb1.Comments as Reason
 FROM OperationsManager.[dbo].[MaintenanceModeView] tb1
 inner join BaseManagedEntity tb2
 on tb1.BaseManagedEntityId=tb2.BaseManagedEntityId
 where IsInMaintenanceMode='1'
 and FullName like 'Microsoft.SystemCenter.HealthService:%'
 and IsDeleted='0'
 order by DisplayName

Get SCOM agents

This will match with agents, management servers and gateway servers in the console.

SELECT DisplayName
FROM [OperationsManager].[dbo].[MT_HealthService] hs
inner join [OperationsManager].[dbo].[Availability] av
on hs.BaseManagedEntityId=av.BaseManagedEntityId
order by DisplayName

Get count of Windows Operating System

This is the most accurate way I've found to get a count of Windows Operating Systems from SCOM. It doesn't include virtual computers from clusters so it should match up with the number of agents, management servers and gateway servers.

Sort by computer name, OS

use OperationsManager
 select upper(hsw.DisplayName) as Computer, wos.DisplayName AS OperatingSystem
 FROM MTV_HealthService hsw
 inner join MTV_Microsoft$Windows$OperatingSystem wos on hsw.DisplayName = wos.PrincipalName
 order by Computer

Count by OS

use OperationsManager
 select wos.DisplayName AS OperatingSystem, count(*) Count
 FROM MTV_HealthService hsw
 inner join MTV_Microsoft$Windows$OperatingSystem wos on hsw.DisplayName = wos.PrincipalName
 group by wos.DisplayName
 order by Count desc

Get agent patch version

This is hit and miss. It doesn't show the patch in some servers. Use the SCOM.Admin mp instead.

USE OperationsManager
 DECLARE @TimeOffset INT, @Class VARCHAR(MAX), @Object VARCHAR(MAX)
 SET @TimeOffset = DATEDIFF (HH, GETUTCDATE(), GETDATE())
 SELECT UPPER(MT_HealthService.DisplayName) AS PrincipalName,
 PatchList,
 FORMAT(DATEADD(HOUR, @TimeOffset, InstallTime), 'dd-MM-yyyy HH:mm') AS ApprovedTime,
 InstalledBy AS ApprovedBy
 FROM MT_HealthService
 WHERE IsAgent = 1
 ORDER BY PatchList, DisplayName

Get grey agents, gateways and mgmt servers

This is a constant work in progress.

USE OperationsManager
SELECT hs.DisplayName, av.IsAvailable, mm.IsInMaintenanceMode, hs.IsAgent, hs.IsGateway, hs.IsManagementServer, hs.IsRHS
FROM [OperationsManager].[dbo].[MT_HealthService] hs
INNER JOIN [OperationsManager].[dbo].[Availability] av
ON hs.BaseManagedEntityId=av.BaseManagedEntityId
INNER JOIN [OperationsManager].[dbo].[MaintenanceModeView] mm
ON mm.BaseManagedEntityId=hs.BaseManagedEntityId
WHERE av.IsAvailable='0'
AND mm.IsInMaintenanceMode='0'
ORDER BY hs.DisplayName

Comments

Post a Comment