Microsoft SQL Server is one of the most used database platforms, in this guide I wanted to share with you how you can easily install SQL Server 2016 remotely by using a System Center Orchestrator 1801.
Continue reading “Installing SQL Server 2016 remotely with Orchestrator 1801”
I wanted to share some helpful Orchestrator SQL queries that could save you some time when managing or troubleshooting your Orchestrator environment.
Since there are not many tools available for Orchestrator I find it easiest by doing SQL queries straight in the Orchestrator database.
If you need to audit changes that’s been done to your runbooks, you can use the following SQL query to list all changes in all runbooks:
SELECT P.Name AS [Runbook Name], O.Name AS [Activity Name], OT.Name AS [Activity Type], OA.Action, CASE WHEN OA.Attribute LIKE '%[0-F][0-F][0-F][0-F][0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F]- [0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F][0-F][0-F][0-F][0-F][0-F][0-F] [0-F][0-F]%' THEN 'NEW ACTIVITY' ELSE OA.Attribute END AS Attribute, OA.OldValue, OA.NewValue, CIH.DateTime AS [Change Timestamp], S.Account AS [User] FROM OBJECT_AUDIT AS OA INNER JOIN OBJECTS AS O ON OA.ObjectID = O.UniqueID INNER JOIN POLICIES AS P ON O.ParentID = P.UniqueID INNER JOIN OBJECTTYPES AS OT ON OA.ObjectType = OT.UniqueID INNER JOIN CHECK_IN_HISTORY AS CIH ON CIH.UniqueID = OA.TransactionID INNER JOIN SIDS AS S ON CIH.CheckInUser = S.SID WHERE (O.Deleted = 0) ORDER BY [Change Timestamp] DESC
Not sure if you have checked in all runbooks? No problem! With the following SQL query you can see all checked out runbooks:
SQL Query:
SELECT p.Name ,p.CheckOutTime ,p.CheckOutLocation ,p.Description ,s.Account FROM Orchestrator.dbo.POLICIES P Join Orchestrator.dbo.SIDS S on P.CheckOutUser = S.SID Where CheckOutUser is not NULL and p.Deleted = 0
To make sure you have checked in all your runbooks, you can double check it by running this SQL query:
SQL Query:
SELECT TOP (10) SIDS.Account, POLICIES.Name, CHECK_IN_HISTORY.Comment, CHECK_IN_HISTORY.DateTime FROM CHECK_IN_HISTORY INNER JOIN SIDS ON CHECK_IN_HISTORY.CheckInUser = SIDS.SID INNER JOIN POLICIES ON CHECK_IN_HISTORY.ObjectID = POLICIES.UniqueID ORDER BY CHECK_IN_HISTORY.DateTime DESC
If you want to check who’s connected to your Orchestrator environment’s console, you can do it with the following query:
SQL Query:
SELECT TOP 10 [UniqueID] ,[ManagementServer] ,[ClientMachine] ,[ClientUser] ,[ClientVersion] ,[ConnectionTime] ,[LastActivity] FROM [Orchestrator].[dbo].[CLIENTCONNECTIONS]
Ever had a runbook that gets stuck or drain a lot of memory? The solution is then to kill the runbook’s process but first you will need to know the process ID. Here’s how you can find the process ID of a runbook:
SQL Query:
SELECT POLICYINSTANCES.TimeStarted, POLICYINSTANCES.TimeEnded, POLICYINSTANCES.ProcessID, POLICYINSTANCES.SeqNumber, POLICIES.Name FROM POLICYINSTANCES INNER JOIN POLICIES ON POLICYINSTANCES.PolicyID = POLICIES.UniqueID ORDER BY POLICYINSTANCES.TimeStarted DESC
You can of course check the status of a runbook in the log in Orchestrator console, but what if you need to check the status of many runbooks which are spread out all over the place? Here’s the solution for you:
SQL Query:
SELECT PI.Status, POLICIES.Name, PI.TimeEnded, PI.TimeStarted, PI.Computer FROM POLICIES INNER JOIN (SELECT PI1.PolicyID, PI1.TimeStarted, PI1.TimeEnded, PI1.Status, ACTIONSERVERS.Computer FROM PolicyInstances AS PI1 INNER JOIN ACTIONSERVERS ON PI1.ActionServer = ACTIONSERVERS.UniqueID WHERE PI1.TimeEnded = (SELECT MAX(PI2.TimeEnded) FROM PolicyInstances AS PI2 WHERE PI2.PolicyID = PI1.PolicyID) ) AS PI ON PI.PolicyID = Policies.UniqueID WHERE (POLICIES.Deleted = 0) AND (POLICIES.CheckOutUser IS NULL)
There’s a few ways of getting to know a GUID of a runbook, I will post two ways here:
SQL Query:
Select lower(POLICIES.UniqueID) as RunbookID, lower(CUSTOM_START_PARAMETERS.UniqueID) as ParameterID, CUSTOM_START_PARAMETERS.value From POLICIES INNER JOIN OBJECTS on POLICIES.UniqueID = OBJECTS.ParentID LEFT OUTER JOIN CUSTOM_START_PARAMETERS on OBJECTS.UniqueID = CUSTOM_START_PARAMETERS.ParentID Where POLICIES.Name = 'My Runbook Name' and policies.deleted = 0
Accidentally deleted a folder in your Orchestrator environment? Don’t worry, all is not lost! All deleted objects can still be found in the Orchestrator database.
Note: You will need to know the name of the folder that was deleted.
SQL Query:
Select UniqueID, ParentID, LastModified, Deleted from FOLDERS where Name like 'My deleted folder name'
To recover a deleted folder, you will need to know the UniqueID of the folder.
By running the previous SQL query to “Find a deleted folder” you will get the UniqueID that you need for your query to recover your deleted folder.
SQL Query:
UPDATE FOLDERS set Deleted = 0 where UniqueID = 'The UniqueID of the deleted folder, you can get it by running the "Find a deleted folder SQL query"'
If the SQL query is ran successfully you should see the (1 row(s) affected).
Note: Remember to click refresh in your Orchestrator console to make the folder reappear.
If you accidently delete a runbook, you can recover it from the Orchestrator database.
Basically a deleted object gets the value “1” in the database, a non deleted object has the value “0”.
By running the following SQL query you will find all deleted runbooks within a certain period of time:
SQL Query:
select * from POLICIES where Deleted = 1 and LastModified > '2018-01-01 01:00:00.000' and LastModified < '2018-04-29 23:00:00.000'
To recover your deleted runbook(s) you basically change the deleted value in the database to from “1” to “0”.
SQL Query:
UPDATE POLICIES Set Deleted = 0 where Deleted = 1 and LastModified > '2018-01-01 01:00:00.000' and LastModified < '2018-04-28 23:00:00.000'
If the SQL query is ran successfully you should see the (1 row(s) affected).
Note: Remember to click refresh in your Orchestrator console to make the runbook reappear.
