Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

1Apr/140
Clear Orchestrator Logs from Runbooks by Folder Name

I found it useful to clear Orchestrator Runbook logs by Folder. This allowed me to clear smaller chunks of logs from Orchestrator where in the past I was clearing the entire database which was a very slow process. A small improvement, but it helps.

-- This script will remove logs from a folder in Orchestrator
-- enter a folder name into the folder variable

-- Create temporary table for sysprocesses
set QUOTED_IDENTIFIER Off
SET NOCOUNT ON

-- Variables
declare @strsql char(200),
  @ThePolicyId varchar(50),
  @USRCOUNT int,
  @PRINTUSRCOUNT VARCHAR (255), 
  @SqlScript varchar(255),
  @PolicyId varchar(50),
  @FolderName VARCHAR(100),
  @NumberOfDaysToKeep INT

-- Folder name
SET @FolderName = 'Folder Name'
SET @NumberOfDaysToKeep = 180

-- Query for UniqueId number of all Runbooks in a folder
use Orchestrator
set @strsql = 'select p.UniqueID from [dbo].[POLICIES] p 
INNER JOIN [dbo].[FOLDERS] f ON p.ParentID = f.UniqueID
WHERE f.[Name] = ''' + @FolderName + '''
AND p.Deleted = 0
ORDER BY f.name, p.name'

IF OBJECT_ID('tempdb..#toRun') IS NOT NULL DROP TABLE #toRun

create table #toRun
(
  PolicyId uniqueidentifier,
)

-- Insert into temp table
INSERT INTO #toRun EXEC(@strsql)

-- Comment out the query below to hide connections
--select * from #toRun

SELECT @USRCOUNT = (SELECT CONVERT(CHAR, COUNT(1)) FROM #toRun)
SET @PRINTUSRCOUNT = 'Attempting to run ' + CAST(@USRCOUNT AS VARCHAR(36)) + ' Runbooks' 
PRINT @PRINTUSRCOUNT 

-- Pause script for 1 seconds
WAITFOR DELAY '00:00:01' 

-- Begin cursor 
DECLARE CUSRRUN SCROLL CURSOR FOR 
SELECT PolicyId FROM #toRun 

OPEN CUSRRUN

FETCH FIRST FROM CUSRRUN INTO @PolicyId 
SELECT @SqlScript = 'DECLARE @Completed bit 
	SET @Completed = 0 
	WHILE @Completed = 0 EXEC sp_CustomLogCleanup 
	@Completed OUTPUT, 
	@FilterType=2,
	@YDays=''' + CAST(@NumberOfDaysToKeep AS VARCHAR) + ''',
	@PolicyID=''' + CAST(@PolicyId AS VARCHAR(36)) + ''''
PRINT 'Runbook: ' + CAST(@PolicyId AS VARCHAR(36))
PRINT @SqlScript 
EXEC(@SqlScript) 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  FETCH NEXT FROM CUSRRUN into @PolicyId 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 

    SELECT @SqlScript = 'DECLARE @Completed bit 
		SET @Completed = 0 
		WHILE @Completed = 0 EXEC sp_CustomLogCleanup 
		@Completed OUTPUT, 
		@FilterType=2,
		@YDays=''' + CAST(@NumberOfDaysToKeep AS VARCHAR) + ''',
		@PolicyID=''' + CAST(@PolicyId AS VARCHAR(36)) + ''''
    PRINT 'Runbook: ' + CAST(@PolicyId AS VARCHAR(36))
    PRINT @SqlScript 
    EXECUTE(@SqlScript) 

    FETCH NEXT FROM CUSRRUN INTO @PolicyId 

  END 
END 
CLOSE CUSRRUN 
DEALLOCATE CUSRRUN 

--End cursor
PRINT 'Finished running'
DROP TABLE #toRun

--select TOP 10 * from [dbo].[POLICIES] p 

--select TOP 10 f.Name,* from [dbo].[POLICIES] p 
--INNER JOIN [dbo].[FOLDERS] f ON p.ParentID = f.UniqueID
--WHERE f.[Name] = 'Cisco'
--AND p.Deleted = 0
--ORDER BY f.name, p.name

--select * from [dbo].[FOLDERS] f WHERE f.[Name] = 'Cisco' ORDER BY f.name

Notes:

When the sp_CustomLogCleanup stored procedure is executed, it identifies 200 policy instances to purge and returns either a 0 or a 1 upon completion. The stored procedure identifies the 200 policy instances that are to be purged by executing one of the three additional stored procedures based on the log purging option selected:

  • sp_GetLogEntriesForDelete_FilterByEntries
  • sp_GetLogEntriesForDelete_FilterByDays
  • sp_GetLogEntriesForDelete_FilterByEntriesAndDays

The log purge utility has a hard-coded time out value for the execution of the stored procedure. While not recommended, you can change this value within the stored procedure to accommodate extremely large log counts.

If you choose to turncate, truncating the OBJECTINSTANCEDATA and OBJECTINSTANCES tables should be enough, as these two tables contain the bulk of log data. You may not be able to truncate the POLICYINSTANCES due to foreign key constraints.

31Mar/140
Search Orchestrator for Text Under an Activity’s Script

I had a client request that I search within all scripts within their PowerShell objects in Orchestrator for a string. They had over 1,000 Runbooks and an unknown number of PowerShell scripts. The key is to search in the QIKOBJECT table, joined to the OBJECTS table, within the Properties field under QIKOBJECT.

Here is an example:

select f.Name, p.Name, o.Name,q.Properties from [dbo].[QIKOBJECT] q
INNER JOIN [dbo].[OBJECTS] o ON q.UniqueID = o.UniqueID
INNER JOIN [POLICIES] p on p.UniqueID = o.ParentID
INNER JOIN [dbo].[FOLDERS] f ON f.UniqueID = p.ParentID
WHERE q.Properties like '%string%'
AND o.Deleted <> 1
ORDER BY f.Name, p.Name, o.Name

Find PowerShell objects within this query:

select * FROM [dbo].[OBJECTTYPES] WHERE [Name] Like '%PowerShell%'

Or the title of an Activity:

select * from [dbo].[OBJECTS] WHERE [Name] = 'title of an activity'

List all Runbooks in a Folder:

select TOP 10 f.Name,* from [dbo].[POLICIES] p
INNER JOIN [dbo].[FOLDERS] f ON p.ParentID = f.UniqueID
WHERE f.[Name] = 'name of folder'
AND p.Deleted = 0
ORDER BY f.name, p.name

Some good notes: http://blogs.technet.com/b/orchestrator/archive/2012/05/29/ips-and-activities-and-how-those-are-represented-in-the-database.aspx

12Apr/130
Linux VM Deployment from Orchestrator Using a VMM Template

I had to capture this in a blog as soon as possible so this article might be rough. I just successfully deployed a Linux VM from a VMM Template using an Orchestrator Runbook. This might sound simple, but I couldn't find help online as Microsoft's documentation lacks some important details.

I'll document how to setup the Linux VMM Template in another post. For now I'll just cover the Orchestrator Runbook. For the Orchestrator VMM Activities I'm using the SC 2012 Virtual Machine Manager integration pack.

  1. Create a new Runbook in Orchestrator
  2. Add the Initialize Data (from Runbook Control) and Create VM From Template (from SC 2012 Virtual Machine Manager) activities.
  3. To the Initialize Data activity, add the following parameters:
    1. destinationType (Host|Cloud)
    2. destinationHost (FQDN)
    3. vmName
  4. Within the Create VM From Template activity: If your SC 2012 Virtual Machine Manager integration pack is configured correctly (pointed to the service), open the Create VM From Template activity and select the correct VMM Configuration. Fill out the activity as follows:
    1. Destination Type: Select destinationType (Host|Cloud) from the databus.
    2. Destination: Select destinationHost (FQDN) from the databus.
    3. Path: C:\ProgramData\Microsoft\windows\hyper-v\Virtual Machines\
    4. VM Name: Select vmName from the databus.
    5. Source Template Name: Select the Linux VM Template from the VMM Library.
    6. Cloud Capability Profile: Select appropriately.
    7. Click on Optional Properties and add and fill out the following: Computer Name, Guest OS Profile, Hardware Profile, Linux Administrator SSH Key File, Linux Domain Name, Operating System, Start Action, Stop Action, Time Zone.
  5. That should do it. Run the Runbook and note any errors. Add additional activities to the Runbook, such as Get VM and Start VM to complete the Runbook.
11Apr/130
Using .NET C# and the Orchestrator Web Service

I needed to capture the blogs I used to create the helper class for the website I am building. This site will launch Orchestrator Runbooks using the Orchestrator service. I found it a lot easier to use the helper class generated from the service.

This blog helped:

http://blogs.technet.com/b/neilp/archive/2012/02/14/sql-cluster-with-custom-front-end.aspx

But here is the blog that helped me get over the learning curve:

http://www.damianflynn.com/2011/08/15/orchestrator-c-and-runbooks/

I'll post the steps I used to create the site when I find time.

Create a helper class from the Orchestrator service:

cd c:\Windows\Microsoft.NET\Framework\v3.5\

DataSvcUtil.exe /dataservicecollection /version:2.0 /language:CSharp /out:SCORCHWebServiceClass.cs /uri:https://orchestratorws:444/Orchestrator2012/Orchestrator.svc

 

28Feb/130
Orchestrator – Matching on the New Line Character

There may be a bug in the way Orchestrator matches the new line character (\n). Sometimes it just does not find the new line! After a bit of research, I discovered that you can use the Expand Fields activity and match new lines the built-in Data Manipulation function like so:

[lower('
')]

This is all you need to 'declare' the newline character.

Here is what my Expand Fields activity looks like:

orchestrator-13

28Feb/130
Orchestrator – Looping on a PowerShell Object

Looping can be a little tricky if you don't know the rules Orchestrator uses:

The Exit tab specifies the conditions that will determine if the loop will exit. The Do Not Exit tab specifies the conditions that will cause the loop to continue. The rules on the Do Not Exit tab will supersede the rules on the Exit tab.
The rules within each tab are joined using an Or condition. Only one of the conditions on a tab will need to be true for the entire tab to be true.
I like to stack the rules within the Exit tab to make it easier to track which conditions will cause the activity to continue.
My looping exit conditions will look like this:
orchestrator-12
16Feb/130
Orchestrator – Check for Blank or Null

A good way to check if published data returned from an activity is blank or null is to match against the pattern ^$ (regex for null is ^$).

orchestrator-10

16Feb/130
Orchestrator – Fail a parent policy based on child status

Set the link from the triggered runbook to fail if the Child policy status equals failed or warning. Succeed if the Child policy status equals success.

orchestrator-14

This runbook will look like this:

orchestrator-15

16Feb/130
Orchestrator – PowerShell error handling

We can use the Common Published Data “Error Summary Text” to capture the error from the PowerShell activity. Testing shows that if there are no errors, the Error Summary Text is blank. If there are errors, a message will appear preceded by “error message”.

orchestrator-11