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

Use PowerShell to Retrieve a List of Servers from VMM

Useful script to go after servers in VMM and output the list to a csv file.

Import-Module virtualmachinemanager;
$MasterList = @();
$serverList = Get-VM -VMMServer seacorpmgmt05 | Where-Object { (($_.Name -like '*DEMO*' -or $_.Name -like '*APP*' -or $_.Name -like '*WEB*' -or $_.Name -like '*CORP*' -or $_.Name -like '*UTIL*' -or $_.Name -like '*MGMT*') -and $_.Name -notlike '*dev*') };
foreach ($server in $serverList) {
   $serverNetworkAdapter = Get-SCVirtualNetworkAdapter -VM $server;
   $MyObject = New-Object PSObject -Property @{
      ServerName = $server.Name;
      ServerEnvironment = $server.Name.Substring(3,4);
      HostName = $server.HostName;
      VMStatusString = [string]$server.StatusString;
      VMState = [string]$server.VirtualMachineState;
      ServerIPAddress = $serverNetworkAdapter.IPv4Addresses;
      CPUCount = $server.CPUCount;
      Memory = $server.Memory;
      OperatingSystem = $server.OperatingSystem;
      IsHighlyAvailable = $server.IsHighlyAvailable;
      Description = $server.Description.Replace([Environment]::NewLine, '');
      CreationTime = $server.CreationTime;
   $MasterList += $MyObject;
$MasterList | Select-Object @{label='Server Name';expression={$_.ServerName.ToUpper()}}, @{label='Environment';expression={$_.ServerEnvironment.ToUpper()}}, @{label='VM Status';expression={if ($_.VMStatusString -eq $_.VMState) { $_.VMStatusString } else { $_.VMState + ' (' + $_.VMStatusString + ')'}}}, @{label='Host Name';expression={$_.HostName.Replace('.ds-iq.corp','').ToUpper()}},@{Name=’ServerIPAddress’;Expression={if ([string]::join(";", ($_.ServerIPAddress))) {[string]::join(";", ($_.ServerIPAddress))} else { " " }}},@{label='Cores';expression={$_.CPUCount}},@{label='Memory';expression={$_.Memory}},@{label='OS';expression={$_.OperatingSystem}},@{label='Highly Available';expression={$_.IsHighlyAvailable}},@{label='Comments';expression={if ($_.Description) {$_.Description + ', Created ' + $_.CreationTime} else { 'Created ' + $_.CreationTime }}} | EXPORT-CSV c:\temp\app-server-2.csv -notype;
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

-- Variables
declare @strsql char(200),
  @ThePolicyId varchar(50),
  @USRCOUNT int,
  @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

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

SET @PRINTUSRCOUNT = 'Attempting to run ' + CAST(@USRCOUNT AS VARCHAR(36)) + ' Runbooks' 

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

-- Begin cursor 
SELECT PolicyId FROM #toRun 


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


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



--End cursor
PRINT 'Finished running'

--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

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


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.

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

Some good notes:

Add a New Windows Events Monitor to Ops Manager 2012

I don't add monitors and alerts to Ops Manager often, so I thought I'd post a refresher:

  1. Open Ops Manager
  2. Click on Authoring
  3. Expand Management Pack Objects
  4. Right click on Monitors, select Create a Monitor, and click on Unit Monitor type
  5. Expand Windows Events, expand Simple Event Detection, and select Windows Event Reset.
  6. Select a management pack, click Next
  7. Give the event a name and description
  8. Select the Monitor Target, Windows Server 2008 R2 Operating System, click Next
  9. Select the Log name: Application
  10. Setup the filter as follows:
    1. Event Id Equals 17061
    2. Event Source Equals MSSQLSERVER
    3. EventDescription Contains Error: 60000

Note: EventDescription is not readily available. Click Insert and choose then “…” in the new parameter box. In “Select an Event Property” choose “Use parameter name not specified above” and then insert EventDescription  click OK

  1. Select an Event Log for the Healthy Event.
  2. Setup the Event Expression (Healthy Event) to be the opposite of the Event Expression (Unhealthy Event), click next.
  3. Configure Health, the First Event Raised should be Critical and the Second Event (healthy) should be Healthy. Click Next
  4. Configure Alerts, setup an alert for this monitor. Generate the alert when the monitor is in a critical state.
  5. Add to the event description as needed. Some examples are below:

Event Description: $Data[Default='']/Context/EventDescription$

Event Number: $Data/Context/EventNumber$

Event Source Name: $Data/Context/EventSourceName$

Logging Computer: $Data/Context/LoggingComputer$

Target Host Principal Name: $Target/Host/Property[Type="MicrosoftWindowsLibrary7585010!Microsoft.Windows.Computer"]/PrincipalName$

  1. Click Create.


Renew DHCP in Ubuntu Linux

I often have to look this up. Release a DHCP lease in Ubuntu with the following command (for the GUI search for gnome-terminal):

sudo dhclient -r

Next retrieve a new lease:

sudo dhclient

Or, on one line:

sudo dhclient -r && sudo dhclient;

If that does not work, check if networking is up:

ifdown eth0
ifup eth0

Finally, you can always restart networking:

/etc/init.d/network restart


/etc/init.d/networking restart

Don't forget to reset the certificate used by Ops Manager 2012 (after a hostname rename, look for the hostname does not match the FQDN on certificate error). The following command will clear all previous Ops Manager files (The –f option forces the files in /etc/opt/microsoft/scx/ssl to be overwritten):

/opt/microsoft/scx/bin/tools/scxsslconfig -f -v

Next, restart the agent:

/opt/microsoft/scx/bin/tools/scxadmin -restart

The Ops Manager discovery and install should now be successful.

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.
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:

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

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


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:


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

Here is what my Expand Fields activity looks like:


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 – 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 ^$).