SCOM also provides administrator with writing their own knowledge. It's called company's knowledge. It could be knowledge base (or KB) for their companies.
Well, It's a blank page.
In this posting, I'd like to explain how we get the KB to a certain alert, of course, I'll do in the database, too. I'll find it using both of OM DB and DW. As we know, DW stores long-term data but I couldn't find it from DW. So I had to reference KB on OM DB. In other words, I used KB on OM DB and others on DW. In fact, my lab environment, both DBs are installed on the same server.
1. Get the alert and its rule.
The Alert comes from rules. The rule defines about how they produce wanings or errors when they have a breach of regulations. So looking at Alert, we can find rule's records.
DB: select * from OperationsManager.dbo.AlertView
WorkflowRowId column matches with RuleView.
DW: select * from OperationsManagerDW.Alert.vAlert
MonitoringRuleID columns matches with dbo.Rule.
These views that get alerts are nearly same except for one thing. it is that DW also has relationship between tables by Rowid, OM DB mainly by uniqueidentifier type.
2. Get KB from DB
DB: select * from OperationsManager.dbo.KnowledgeArticle
this table has a column which has relation with Rule. that is KnowledgeReferenceId column. KnowledgeArticle has many different kind of subjects. It's saved management pack KB, Monitor KB and so on.
3. DB Query
As we find tables what we need, and then let me show you my query.
It looks like difficult.
- Lines between 9 and 13 are about getting Alert status. vAlertResolutionState view has historical data, it needs the latest data.
- Line No.14 is relation vAlert and vRule.
- Line No. from 18 to 27 are to get KB from OM DB.
- finally, Comany's KB is on lines between 28 and the 33. in this example, I made a user defined MP which named "CloudPortal"