Useful Operations Manager 2007 SQL queries

Joe Sack’s SQL Blog - Useful SQL queries for Operations Manager 2007

Joe Sack’s SQL Blog - Useful SQL queries for Operations Manager 2007 — Thu, 18 Oct 2007 22:27:56 GMT

PingBack from http://joesack.com/WordPress/?p=87

Grooming process in the Operations Database

Kevin Holman's OpsMgr Blog — Wed, 13 Feb 2008 05:51:51 GMT

This is a continuation of my other post, on general alert grooming: How grooming and auto-resolution

re: Useful Operations Manager 2007 SQL queries

colinhodges — Wed, 19 Mar 2008 05:25:45 GMT

How 'bout a query that show space used by type, being alert, event, perf, state for the dw?

re: Useful Operations Manager 2007 SQL queries

kevinhol — Wed, 19 Mar 2008 05:28:36 GMT

You can get space used by type by using the large table query.... which is why I include and use that one all the time. It could be made prettier... but it works. There is also a report in SQL 2005 SP2 for reporting on table by size.... which will show you this. The tables are named to be fairly self explanatory.

OpsMgr 2007: Troubleshooting Console Performance in large environments

SMS&MOM — Thu, 17 Jul 2008 15:50:14 GMT

I've been getting a few questions lately about slow performance with the console so I thought a post

re: Useful Operations Manager 2007 SQL queries

sna — Thu, 20 Nov 2008 02:11:29 GMT

Hi - This is a great post.

I was wondering if you could help me with a question.

In the view “PerformanceDataAllView” in the “OperationsManager” database, what do the columns “TimeSampled” and “TimeAdded” mean?

Thanks,

Sna

Availability Query

Availability Query — Thu, 18 Dec 2008 23:29:26 GMT

Hello,

Can you post a query one would run to get availability of a server for a certain time period?

Thanks,

re: Useful Operations Manager 2007 SQL queries

paul keely — Mon, 05 Jan 2009 17:18:02 GMT

what another fantastic resourse!!

re: Useful Operations Manager 2007 SQL queries

npagonor — Wed, 18 Feb 2009 08:10:49 GMT

Could you help with a query to retrieve Dell hardware with critical alert state = New.

Thanks

re: Useful Operations Manager 2007 SQL queries

Orhan — Fri, 03 Apr 2009 23:56:23 GMT

Hello everyone,

I am trying to create report something like this,

User Name Number of Closed Alerts

operatorname1 245

Operatorname2 165

I am querying the OperationManager Database my query is,

SELECT LastModifiedBy AS 'User Name', COUNT(*) AS 'Number of Closed Alerts'

FROM AlertView

WHERE (ResolutionState = '255')

GROUP BY LastModifiedBy

ORDER BY 'Number of Closed Alerts'

I am confused little in the alert view table which filed should I use for the date and time parameters.

if I just want to get the closed alert count by specific user and by specific time is that field going to be "TimeResolved" or which one?

Thank you.

re: Closed Alert report

kevinhol — Sat, 04 Apr 2009 01:58:28 GMT

I already blogged on a closed alert report:

http://blogs.technet.com/kevinholman/archive/2008/07/21/auditing-on-alerts-from-the-data-warehouse.aspx

Does that not work?

re: Useful Operations Manager 2007 SQL queries

Orhan — Tue, 07 Apr 2009 16:37:04 GMT

Hi Kevin,

Your report works fine, but I needed to get last 7 days data which not included in your report because you are getting data from Datawarehouse by default we know Opsmgr keeps 7 days alert data in the OperationManager DB so I figured out from your query and modify it against OperationManager DB.

My issue was I could not figured out which field should I for data and time parameter. I think I found out,

Here is the final query I believe this is correct

SELECT LastModifiedBy AS 'User Name', COUNT(*) AS 'Number of Closed Alerts'

FROM AlertView

WHERE (ResolutionState = '255')

and TimeResolved between @startdate and @enddate

GROUP BY LastModifiedBy

ORDER BY 'Number of Closed Alerts'

Now I have another issue, SQL server TimeZone

SQL server time 4 hours ahead than system time when I pull data from Report manager I am not getting accurate data from database. Do you know how can we fix this issue?

Thank you.

re: Useful Operations Manager 2007 SQL queries

Pradeesh — Fri, 17 Apr 2009 09:36:07 GMT

This a great topic

Thanks

Pradeesh

Operations Manager 2007 SQL Queries

Jonathan Almquist on Operations Manager — Sat, 25 Apr 2009 12:05:12 GMT

I’m starting a new post here, similar to my Command Shell reference .  This will include some useful

My Operations Manager 2007 SQL Queries

Stefan Stranger's Weblog - Manage your IT Infrastructure — Sat, 25 Apr 2009 18:18:58 GMT

Jonathan Almquist started a blog post about Operations Manager 2007 SQL Queries . “I’m starting a new

re: Useful Operations Manager 2007 SQL queries

Olie — Fri, 12 Jun 2009 15:27:22 GMT

Hi Kevin,

Thanks for the post, will prove very useful.

Got an issue when I run a few of the queries though. I reveive an error stating that PerformanceDataAllView is an invalid object name.

Any ideas?

Thanks,

Olie

re: Useful Operations Manager 2007 SQL queries

Olie — Fri, 12 Jun 2009 15:31:46 GMT

Hi Kevin,

Please ignore my last post regarding PerfromanceDataAllView as I have managed to resolve the issue.

Thanks again for the scripts... very handy!

Olie

Useful Operations Manager 2007 SQL queries

Srikanth — Tue, 23 Jun 2009 12:31:24 GMT

How to get InternetInformationServices information from operations manager databse

re: Useful Operations Manager 2007 SQL queries

Nayden — Wed, 05 Aug 2009 17:17:19 GMT

Here is one I found useful (I am sure it can be modified and improved, but this one works for me). It returns the Name, Action Account Identity, Install Date, Version, Health State, Availability (the greyed out icon state when the agent is not reporting back to the MS), and the MS it reports to. Some stuff is commented out, you can include it if you want...

SELECT

--[MT_C].[BaseManagedEntityId] AS [Id],

[MT_C].[PrincipalName] AS [Name],

--[MT_C].[NetbiosDomainName],

--[MT_C].[NetbiosComputerName],

--[MT_C].[NetworkName],

[MT_H].[ActionAccountIdentity],

--[MT_H].[InstalledBy],

[MT_H].[InstallTime],

--[MT_H].[ProxyingEnabled],

--[MT_H].[IsManuallyInstalled],

--[MT_H].[ActiveDirectoryManaged],

[MT_H].[Version],

[S].[HealthState],

CASE

WHEN MAX(CAST(A.[IsAvailable] AS int)) IS NULL

THEN CAST(0 AS BIT)

ELSE CAST(MAX(CAST(A.[IsAvailable] AS int)) AS BIT)

END

AS IsAvailable,

--[BME].[BaseManagedEntityId] AS [HealthServiceId],

--[TME].[ManagedTypeId] AS [HealthServiceTypeId],

[BME2].[DisplayName] AS [ReportingTo]

--[BME2].[TopLevelHostEntityId] AS [TargetHostId],

--[HSC].[RelationshipTypeId] AS [CommunicationTypeId],

--[BME].[LastModified]

FROM dbo.BaseManagedEntity (nolock)

AS BME

INNER JOIN dbo.TypedManagedEntity AS TME ON BME.[BaseManagedEntityId] = TME.[BaseManagedEntityId]

INNER JOIN dbo.Relationship AS HSC ON HSC.[SourceEntityId] = BME.[BaseManagedEntityId]

INNER JOIN dbo.BaseManagedEntity AS BME2 ON BME2.[BaseManagedEntityId] = HSC.[TargetEntityId]

INNER JOIN dbo.MT_Computer AS MT_C ON MT_C.[BaseManagedEntityId] = BME.[TopLevelHostEntityId]

INNER JOIN dbo.MT_HealthService AS MT_H ON MT_H.[BaseManagedEntityId] = BME.[BaseManagedEntityId]

LEFT OUTER JOIN dbo.State AS S ON BME.[BaseManagedEntityId] = S.[BaseManagedEntityId]

AND S.[MonitorId] = dbo.fn_ManagedTypeId_SystemHealthEntityState()

LEFT OUTER JOIN dbo.Relationship AS R ON BME.[TopLevelHostEntityId] = R.[TargetEntityId]

AND R.[RelationshipTypeId] = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceManagesEntity()

AND R.[IsDeleted] = 0

LEFT OUTER JOIN dbo.Availability AS A ON A.[BaseManagedEntityId] = R.[SourceEntityId]

WHERE (( BME.[LastModified] > 'Jul 1 1999 1:24:55:063PM' ) )

AND (((TME.ManagedTypeId = '118C0F18-7A70-5F57-3A9B-EEAF1705B3FC')

AND (HSC.[RelationshipTypeId] = '37848E16-37A2-B81B-DAAF-60A5A626BE93')

AND (BME.[IsDeleted] = 0

AND TME.[IsDeleted] = 0

AND HSC.[IsDeleted] = 0)))

GROUP BY BME.[BaseManagedEntityId], MT_C.[BaseManagedEntityId], MT_C.[PrincipalName],

MT_C.[NetbiosDomainName],

MT_C.[NetbiosComputerName],

MT_C.[NetworkName],

MT_H.[ActionAccountIdentity],

MT_H.[InstalledBy],

MT_H.[InstallTime],

MT_H.[ProxyingEnabled],

MT_H.[IsManuallyInstalled],

MT_H.[ActiveDirectoryManaged],

MT_H.[Version],

S.[HealthState],

TME.[ManagedTypeId],

BME2.[DisplayName],

BME2.[TopLevelHostEntityId],

HSC.[RelationshipTypeId],

BME.[LastModified]

order by Name asc

re: Useful Operations Manager 2007 SQL queries

mvista — Thu, 06 Aug 2009 00:32:10 GMT

Hi Nayden,

more than a great script; thank you. Any chance to add information about the fallback MS in this query?

Thank your very much for such a support.

Alex

re: Useful Operations Manager 2007 SQL queries

Nayden — Thu, 13 Aug 2009 23:59:41 GMT

Well, I am not sure whether it is possible or not - I myself am not a SQL expert... Since I could not find this info anywhere (documentation or via PowerShell) my last resort was to run a trace on the DB and reverse-engineer the query out of the thousands of lines of output. Sorry I couldn't be of more help.

re: trace queries

kevinhol — Fri, 14 Aug 2009 01:24:48 GMT

Dontchya hate that? :-)

I'd much rather write a new query than re-engineer someone elses and trust it.

But I myself am no Tsql expert. Most of these above that I have written are pretty basic joins from simple trables/views. The complex ones are always something I got from someone else and just published it. I think the most complex one I ever wrote was the Outage and Maintenance report, and I had to get some help on that one. Dont feel bad.

re: Useful Operations Manager 2007 SQL queries

kldsts — Tue, 29 Sep 2009 11:26:45 GMT

How can i get the total no of alerts raised for a specifically OS related for a month. ?

How can i get the total no of notification send from my scom server to the notified groups ?

re: Useful Operations Manager 2007 SQL queries

James — Tue, 20 Oct 2009 14:29:14 GMT

Is it possible to have a query that shows the number of alerts per day by a group of servers?

I have a number of groups, ie DomainA, DomainB, DomainC etc so would like to report the number of alerts generated by group with also the number of days to be a variable

re: Useful Operations Manager 2007 SQL queries

Jasmine — Thu, 22 Oct 2009 07:42:33 GMT

I am trying to write a query to display alert history which users might have update on certain alerts under alert properties / History. I have no idea which table I should call as I could not find a table which stores this data. Any advise?

re: Useful Operations Manager 2007 SQL queries

Steve Tolbert — Wed, 04 Nov 2009 19:14:57 GMT

Is there a query to retrieve all rules and monitors running on a given host including overrides?

I am trying to do something similar to the effective configuration viewer but I would prefer to do it in sql.