System Center Service Manager–SQL Query to get a full List of your Incidents

In the most cases, the Reporting has to be done through the Data warehouse DB for SCSM, but in some cases you have to get all the Data from the ServiceManager DB. For Example if you don’t have a Data warehouse DB for SCSM or you need some more Details.

In this Case you have to query the Service Manager DB, and unfortunately, this DB is not as simple as the Data warehouse DB. So to get a complete list of your Incidents, including all your Status, Classifications, Affected Users, Assigned Users and everything else, it will cost some time to build this Query.

Also to get the correct Values for Status, Classification and so on, you have to make some joins, but see yourself.

Take this Query as a basis and changed it to fit your needs. Below is a Link to Download

(Read more…)

System Center Orchestrator–SQL Query detailed Logs and behind the Scene

In some cases it can happen that the Log Purge is not working anymore, or not working correctly. In a real bad situation it can happen, that the daily Log Purge will make more troubles, maybe stops your Runbooks from working.

Than you will see a lot of orphaned Runbooks, and in the most cases this can happen when the extended Logging is activated at to many Runbooks. I always see customer, which are using the extended Logging at each Runbook and for ever.

Extended Logging should be used with care and only for testing and Dev Phase, not in production and not persistent.

Get Runbooks with active extended Logging

To get a List of Runbook with active, extended logging, run he following Query.

use Orchestrator 

select Name, LastModified, Enabled  from POLICIES where LogSpecificData = 1 or LogCommonData = 1


(Read more…)

System Center Orchestrator SQL Query–Query failed Runbooks

For your routine Orchestrator Checks, it can be vefy useful to see all failed Runbooks in a period. Of course you can go to each Runbook, and check the Logs, but to be honest, this is not fun.

So, there is a SQL Query to help on this. At the end of the the Query, there is a number, which will control the Timespan of the Query, in our example we want all failed Runbooks from the last 300 Hours, change this to your needs.

SQL Query get failed Runbooks

use Orchestrator

Select Name, TimeStarted, TimeEnded, POLICYINSTANCES.Status
From [Microsoft.SystemCenter.Orchestrator.Runtime].Jobs AS Jobs
 INNER JOIN POLICIES ON Jobs.RunbookId = POLICIES.UniqueID
 inner join POLICYINSTANCES on jobs.Id = POLICYINSTANCES.JobId 
 where POLICYINSTANCES.Status != 'success' 
 and  TimeEnded > dateadd(HOUR, -300, getdate()) 
 order by Name

(Read more…)

System Center Orchestrator SQL Query–Get Parent Caller Runbook

In huge System Center Orchestrator Environments, it is usual to make a lot of Sub Runbooks, that will be called by more than one Parent Runbook.

For Example, you build a Runbook to add a User in a Group. This Runbook will definitely be used by different Parent Runbooks. And in this case, it can sometimes be useful to query all Runbooks, which are calling our “Add User to Group” Runbook.

So, without any longer explanation, here is the SQL Query, which need to be run against the Orchestrator DB.

Don’t forget to change the Name of the Runbook, you want to query, at the End of the Script, in our example the name is “1.1-AddUserToGroup”

(Read more…)

System Center Orchestrator SQL Query–Runbook Run Count

This will be a short Post, sometimes it can be helpful to get a List of Runbooks and the Count, on how many times the Runbook has been executed.

Maybe if you are looking for most executed Runbook in case of an Performance Issue or you just want to track your environment.

Get Runbook Execution Count from the last Month

use Orchestrator

select	Name, count(policies.UniqueID) as Count from Policies 
inner join POLICYINSTANCES on policies.UniqueID=POLICYINSTANCES.PolicyID 
where POLICYINSTANCES.TimeEnded > DATEADD(mm,-1,GETDATE()) 
group by name 
order by Count Desc


 

(Read more…)

Orchestrator SQL Query to get Runbook running-time

Lately, we had a Requirement, at one of our Customers, to alert someone, if a Runbook is longer running than 30 minutes. There is a way to write an Event Log if a Runbook is running longer than XX Minutes, but this has to be configured for each single Runbook, so that wasn’t a good Solution.

So I have created a small SQL Query for that, which I would like to present to you. feel Free to use.

 

The Script

select 
Policies.name,
ins.TimeStarted,
DateDiff(second, ins.TimeStarted, GETUTCDATE()) as Totalseconds ,
DateDiff(second, ins.TimeStarted, GETUTCDATE()) / 3600 as Hours, 
(DateDiff(second, ins.TimeStarted, GETUTCDATE()) % 3600) / 60 as Minutes, 
DateDiff(second, ins.TimeStarted, GETUTCDATE()) % 60 as Seconds

from POLICYINSTANCES as Ins
inner join POLICIES on Ins.PolicyID=POLICIES.UniqueID

 where Ins.Status is null and Name not like '%-MON-%'
order by Totalseconds desc

The Result

image

Some Information

The SQL Script is excluding all Runbooks which contain “-MON-“, cause this are the Monitoring Runbooks, we don’t want to alert, cause they are running all the time.

We are calculating the totalseconds, and additionally the hours, minutes and seconds as a Result.

 

Michael Seidl aka Techguy

Endlich ein nützlicher SCDPM Report

Wer kennt es nicht, man nimmt einen DPM erfolgreich in Betrieb und erhofft sich dann auch ein anständiges Reporting.

Aber leider sind die Standard-Reports bei SCDPM nicht gerade sehr nützlich.

Da kommt es natürlich sehr gelegen, das Steven Buchanan, seinerseits MVP, mehrfacher Buchautor und durchaus ein Vorbild im System Center Bereich, mit seiner langjährigen Erfahrung einen tollen Report für SCDPM erstellt hat.

 

Der Report funktioniert mit SCDPM 2010, SCDPM 2012, SCDPM 2012 SP1 und SCDPM 2012 R2, und nutzt natürlich die SQL Server Reporting Features.

Hier ein paar Bilder des Reports:

(Read more…)

DB Autoprotection unter DPM funktioniert nicht

Grundsätzlich ist das DB Autoprotection von DPM ein tolles Feature. Einmal in der Schutzgruppe für einen SQL Server aktiviert, werden alle neuen DB’s automatisch in die Sicherung inkludiert.

Probleme gibt es nur dann, wenn der DPM Server mittels WMI den Server nicht abfragen, und somit auch nicht die erforderlichen Datenbanken ermitteln kann. Dann kommt es meist zu diesem Fehler:

One or more databases could not be protected automatically because auto-protection failed. If the databases belong to a SharePoint farm, then the farm recovery points will continue to get crashed without these databases. (ID 32511)

1) To fix auto-protection, resolve the errors and run AutoProtectInstances.ps1 from DPM Management Shell.

Nach einem Klick auf “View Detailed Errors” erscheint folgendes

DPM could not enumerate SQL Server instances using Windows Management Instrumentation on the protected computer ***

Please make sure that Windows Management Instrumentation for SQL server is in good state.

1

(Read more…)

Dynamic Memory richtig einsetzen und konfigurieren

Hat etwas lange gedauert, bis ich diesen Artikel fertig hatte, aber Gut Ding braucht Weile. Ich habe letztens unseren Hyper-V Cluster vollständig mit Dynamic Memory konfiguriert, die Hosts mit ausreichend Memory Reserve ausgestattet und mich erkundigt welche Software überhaupt Dynamic Memory unterstützt.

In diesem Artikel habe ich euch erklärt, wie der Cluster mit SP1 aktualisiert wird, und welche Voraussetzungen für Dynamic Memory geschaffen werden müssen. Jetzt können wir unsere Hosts und VMs konfigurieren. Arbeiten wir uns von untern nach oben durch, also beginnen wir bei den VM Hosts.

VM Host für Dynamic Memory vorbereiten

Hier gibt es 2 Dinge zu beachten, einmal die RAM Reserve und das Pagefile.

(Read more…)

DPM – How To: Sichern von SQL 2008 mittels DPM 2010

Nach “DPM – How To: Sichern von Exchange 2010 mittel DPM 2010” zeige ich euch heute, wie ihr euren SQL 2008 sichern könnt, und was DPM 2010 in Verbindung mit SQL 2008 noch alles kann. Vorweg sei gesagt, DPM 2010 wurde speziell entwickelt um den SQL 2008 zu sichern und wiederherzustellen. DPM sichert automatisch die vollständige Instanz des SQL Servers, somit werden auch neue Datenbanken automatisch mit gesichert. Ein DPM 2010 Server kann bis zu 2.000 Datenbanken sichern, zusätzlich gibt es die Möglichkeit, euren DB-Admins ein Tool in die Hand zu drücken, mit dem diese dann selber die benötigten Datenbanken wiederherstellen können. Aber nun zum How-To.

1. Wir erstellen eine neue Schutzgruppe, und wählen Server

image

2. In der Liste suchen wir unseren SQL Server, und wählen die gewünschte Instanz, sofort wird die Instanz mit (Auto) markiert, somit werden alle neuen DB’s automatisch mit gesichert

(Read more…)