Category Archives: Powershell

Azure Powershell

Quick Tip: Listing your Shared key in Azure VPN with Multisite configuration

Published by:

If you ever configured an Azure VPN, you may have used the trick of downloading the device script configuration from the portal to obtain the shared secret for your VPN device. However, if you have a multi-site configuration that procedure is not effective, since there are different secrets for each network. In that case, PowerShell to the rescue!

 

All you need to know is the name of you Azure VPN Gateway and run this (one liner) below. Make sure you are logged on to the Azure subscription containing the gateway (add-azureaccount) and that you’ve selected the right subscription (select-azuresubscription).

 

(Get-AzureVNetSite -VNetName “MyVPNGateway“).gatewaysites | foreach {Write-host “Local Site: $($_.name) Key: $((Get-AzureVNetGatewayKey -VNetName “MyVPNGateway” -LocalNetworkSiteName $_.Name).Value)” }

 

You’ll get then a list for each local site.

Easy. Quick. PowerShell.

 

Hope this helps!

Automation Azure OMS Powershell

Using MSOMS Alerts with Remediation Runbooks

Published by:

 

Microsoft recently put Operations Management Suite Alerts feature on public preview. Official announcement is here.

One of the greatest features along with alerting itself is the possibility of triggering Azure Automation runbooks to remediate a possible issue found by the alerts.

First of all, make sure you enable the feature since it is a preview:

image

Let’s create a simple alert that will for sure be triggered, in order to have some data. Suppose I want to be alerted when computers talk to more than 5 remote IPs. Ok, I know, it doesn’t make sense, but I want a query that will sure bring data and not a lot.

For example:

Type=WireData Direction=Outbound | measure count() by RemoteIP

Got some interesting numbers:

image

Now, let’s save this search, for future use:

image

After that, we can create an image:

image

Notice you can pick the current search or a previously created search.

Next, you will need to pick a threshold and the window of time for the query. It can’t go further back more than 60 minutes.

image

Notice also that OMS gives you a preview of the results. I love that!

Select the Subject and Recipient of the notification, should you need one, as below:

image

The next is step is to setup some remediation:

image

If you look at the New Azure Portal, you will notice a webhook:

image

If you want your remediation to run on premises, by a Hybrid Worked, you will need to set it up here:

image

And there you have it. Once the alert is triggered, you will see the log:

image

Notice the Input:

image

And there is your data, in a JSON format:

image

image

Now you can grab the data using standard Runbook procedure, as described here.

 

Hope this helps!

Hyper-V Powershell

Creating Hyper-V Host Virtual NICs

Published by:

In a scenario where you have multiple of your NICs in the hosts teamed, you may not want to dedicate a full gigabit network card only to Heartbeat, CSV or Live migration. With Server 2012 R2 and Hyper-V, you are able to configure virtual NICs in the host (parent) partition and allow communication between the hosts.

In my scenario, the hosts have each 11 NICs (which would be enough for the physical cluster network approach, but not that much fun). 8 of the NICS were teamed. On top of that team, Hyper-V switch called Host Virtual Switch was created and pointed to the NIC adapter:

image

On top of that Switch , the following script will create the vEthernet NICs for HeartBeat, CSV and Live Migration:

Add-VMNetworkAdapter -ManagementOS -SwitchName “Host Virtual Switch” -Name “Heartbeat”
Get-VMNetworkAdapter -ManagementOS -Name “Heartbeat” | Set-VMNetworkAdapterVlan -VlanId 10 -Access
Get-NetAdapter -Name “vEthernet (Heartbeat)” | Set-NetIPInterface -Dhcp Disabled
Get-NetAdapter -Name “vEthernet (Heartbeat)” | New-NetIPAddress -IPAddress 192.168.10.1 -PrefixLength 24

Add-VMNetworkAdapter -ManagementOS -SwitchName “Host Virtual Switch” -Name “Live”
Get-VMNetworkAdapter -ManagementOS -Name “Live” | Set-VMNetworkAdapterVlan -VlanId 20 -Access
Get-NetAdapter -Name “vEthernet (Live)” | Set-NetIPInterface -Dhcp Disabled
Get-NetAdapter -Name “vEthernet (Live)” | New-NetIPAddress -IPAddress 192.168.20.1 -PrefixLength 24

Add-VMNetworkAdapter -ManagementOS -SwitchName “Host Virtual Switch” -Name “CSV”
Get-VMNetworkAdapter -ManagementOS -Name “CSV” | Set-VMNetworkAdapterVlan -VlanId 30 -Access
Get-NetAdapter -Name “vEthernet (CSV)” | Set-NetIPInterface -Dhcp Disabled
Get-NetAdapter -Name “vEthernet (CSV)” | New-NetIPAddress -IPAddress 192.168.30.1 -PrefixLength 24

Easy as pie!

Hope this helps!

Automation Azure Powershell

Stopping specific VMs every night with Azure Automation

Published by:

As many companies start to use Windows Azure as a development and test environment, the need for cost saving in the subscriptions start to become a real issue. I run into the same issue when trying to move at least part of my lab into Azure. I wanted it to be available, but I wanted some automated task to bring down everything that was non essential to the environment (my domain controller, for example).

So, I have decided to leverage a recently made public Azure feature called automation. Automation leverages PowerShell Workflow to perform automation tasks against your Azure environment.

Below you will find the steps on how to configure it.

Initial Setup

In order to run the workflow against your Azure VMs, you will need an Azure credential (AD User) that is an administrator of your subscription. So I went ahead and created a new AzureAdmin in my default directory and assigned subscription administration rights.

If you don’t know how to do that, you can check this post.

Now that you have your user, you can go ahead and create your automation account. For that go to the Automation section in the portal:

 

image

You should see something like this, if you never configured an account.

image

Create and account and choose a region.

image

Documentation doesn’t say specifically, but I assume the region, among other things, will define the timezone for the scheduling feature.

image

The last step before you actually create your runbook is to create a credential asset. For that you must go to your automation account->assets option:

image

Click on Add Setting:

image

Add a credential:

image and image

 

Finally, let’s create the Runbook!

image

I’m using a runbook available in the gallery and then I will do some customizations. Follow the steps below:

image

image

image

image

image

image

As you probably noticed, the Runbook will stop ALL VMs,so, that doesn’t work for me. So, I have customized the script lightly to allow for an exclusion list, in form of an Array:

$exceptionlist = @(“fehsedc02”)

I have then customized the PS line to exclude the vms in the list:

Get-AzureVM | where{$_.status -ne ‘StoppedDeallocated’ -and $_.Name -notin $exceptionlist} | Stop-AzureVM –force

image

I have added an extra line, just to show the VMs that remained started after the procedure.

Let’s give it a spin:

image

image

 

image

image

image

The cherry on the top of the cake is the schedule. What I wanted is the VMs to stop at 8:00 PM every night. So, here it goes:

In the Runbook section, click on Schedule:

image

Name it

image

Not a lot of options, but enough for my purposes:

image

IMPORTANT: Initially, I assumed it would respect the time zone of the Automation account, but in fact, it seems it will respect the time zone by checking what is set in the browser  (or the computer). Namely, where you are. If you need it to be different, it seems you’ll need to change you time zone or calculate the hours manually.

Also note the scheduler won’t respect daylight saving time.

A few hours later:

image

image

image

There you go. You can go to sleep safely without the fear of spending a lot of money on your subscription!

 

Hope this helps

Authoring Powershell SCOM

Quick and Dirty: a handy SQL Query PS Rule

Published by:

Very often, I get the request to monitor a remote SQL Server as a Synthetic Transaction and I normally end up creating something custom, even though SCOM has an OLE DB Template, which can come in handy, but it can create a bit of overhead from a class and objects perspective. The approach here is to create a disabled rule, target to a  common and existing class and enable it whenever necessary on the computers selected to be the watcher nodes. Doesn’t look as neat as an entry in the console, but, hey,do you really need to know how sausages are made to enjoy the hotdog?

Let’s get down to it. One thing that is often forgotten is authentication: when running the query, integrated auth is usually a good choice and having a Run as profile to assign an account for the query will sure be need.

1.For that, you will need a Secure Reference and its respective display string.

<TypeDefinitions>

<SecureReferences>
      <SecureReference ID=”ABC.Application.RunAsProfileSQLQueries” Accessibility=”Public” Context=”Windows!Microsoft.Windows.Computer” />
</SecureReferences>

</TypeDefinitions>

<DisplayStrings>

<DisplayString ElementID=”ABC.Application.RunAsProfileSQLQueries”>
  <Name>ABC Application RunAsProfile for SQL Queries</Name>
</DisplayString>
</DisplayStrings>

2.Since this is a custom rule, let’s start by creating a Scripting Probe:

  <TypeDefinitions>

<ModuleTypes>

      <ProbeActionModuleType ID=”ABC.Application.Probe.GenericSQLQueryPS” Accessibility=”Public” RunAs=”ABC.Application.RunAsProfileSQLQueries” Batching=”false” PassThrough=”false”>
        <Configuration>
          <xsd:element minOccurs=”1″ name=”SQLInstance” type=”xsd:string” />
          <xsd:element minOccurs=”1″ name=”Database” type=”xsd:string” />
          <xsd:element minOccurs=”1″ name=”strQuery” type=”xsd:string” />
        </Configuration>
        <ModuleImplementation Isolation=”Any”>
          <Composite>
            <MemberModules>
              <ProbeAction ID=”Probe” TypeID=”Windows!Microsoft.Windows.PowerShellPropertyBagTriggerOnlyProbe”>
                <ScriptName>PSSQLProbe.ps1</ScriptName>
                <ScriptBody><![CDATA[param([string]$SQLInstance,[string]$strQuery,[string]$Database)

$oAPI = New-Object -ComObject “MOM.ScriptAPI”
$oBag = $oAPI.CreatePropertyBag()

$strServer = “$SQLInstance”
$SQLQuery=”$strQuery”
$oAPI.LogScriptEvent(“PSSQLProbe.ps1″, 555,0,”Preparing query against $SQLInstance on Database $Database with query: $SQLQuery .”)
$ADOCon = New-Object -ComObject “ADODB.Connection”
$oResults = New-Object -ComObject “ADODB.Recordset”
$adOpenStatic = 3
$adLockOptimistic = 3
$ADOCon.Provider = “sqloledb”
$ADOCon.ConnectionTimeout = 60
$nowInUTC = (Get-Date).ToUniversalTime()
$conString = “Server=$strServer;Database=$Database;Integrated Security=SSPI”
try {
    $ADOCon.Open($conString)
}
catch {
    $oAPI.LogScriptEvent(“PSSQLProbe.ps1″, 555,1,”Error connecting. Constring: $conString Error: $error”)
}
if ($ADOCon.State -ne 0)
{
   
    $time=measure-command {
        try {    
           
            $oResults.Open($SQLQuery, $ADOCon, $adOpenStatic, $adLockOptimistic)
            $oAPI.LogScriptEvent(“PSSQLProbe.ps1″, 555,0,”Successfully executed query against $SQLInstance on Database $Database”)
            If (!$oResults.EOF)
            {
                $oBag.AddValue(‘RecordCount’,$oResults.RecordCount)
                $oBag.AddValue(‘TransactionTimeMS’, $time.Milliseconds)

            }
            else
            {
                $oBag.AddValue(‘RecordCount’,0)
                $oBag.AddValue(‘TransactionTimeMS’, $time.Milliseconds)
            }
            $oBag
        }
        catch
        {
           #write-host “Error running query”
           $oAPI.LogScriptEvent(“PSSQLProbe.ps1″, 555,1,”Error executing query against $SQLInstance on Database $Database with query $SQLQuery”)
        }
    }
    $oResults.Close()
    $ADOCon.Close()
}]]></ScriptBody>
                <Parameters>
                  <Parameter>
                    <Name>SQLInstance</Name>
                    <Value>$Config/SQLInstance$</Value>
                  </Parameter>
                  <Parameter>
                    <Name>strQuery</Name>
                    <Value>$Config/strQuery$</Value>
                  </Parameter>
                  <Parameter>
                    <Name>Database</Name>
                    <Value>$Config/Database$</Value>
                  </Parameter>
                </Parameters>
                <TimeoutSeconds>60</TimeoutSeconds>
              </ProbeAction>
            </MemberModules>
            <Composition>
              <Node ID=”Probe” />
            </Composition>
          </Composite>
        </ModuleImplementation>
        <OutputType>System!System.PropertyBagData</OutputType>
        <TriggerOnly>true</TriggerOnly>
      </ProbeActionModuleType>

  </ModuleTypes>
</TypeDefinitions>

<DisplayStrings>

<DisplayString ElementID=”ABC.Application.Probe.GenericSQLQueryPS”>
  <Name>ABC Application Probe Generic SQL Query PS</Name>
</DisplayString>

</DisplayStrings>

The probe has the 3 basic parameters: SQL Instance or server, database and SQL Query.

3. With the probe ready, you need a data source module:

<TypeDefinitions>

<ModuleTypes>

<DataSourceModuleType ID=”ABC.Application.DataSource.GenericSQLQueryPS” Accessibility=”Public” RunAs=”ABC.Application.RunAsProfileSQLQueries” Batching=”false”>
  <Configuration>
    <xsd:element minOccurs=”1″ name=”IntervalSeconds” type=”xsd:integer” />
    <xsd:element minOccurs=”0″ name=”SyncTime” type=”xsd:string” />
    <xsd:element minOccurs=”1″ name=”SQLInstance” type=”xsd:string” />
    <xsd:element minOccurs=”1″ name=”Database” type=”xsd:string” />
    <xsd:element minOccurs=”1″ name=”strQuery” type=”xsd:string” />
  </Configuration>
  <OverrideableParameters>
    <OverrideableParameter ID=”SQLInstance” Selector=”$Config/SQLInstance$” ParameterType=”string” />
    <OverrideableParameter ID=”Database” Selector=”$Config/Database$” ParameterType=”string” />
    <OverrideableParameter ID=”strQuery” Selector=”$Config/strQuery$” ParameterType=”string” />
    <OverrideableParameter ID=”IntervalSeconds” Selector=”$Config/IntervalSeconds$” ParameterType=”int” />
    <OverrideableParameter ID=”SyncTime” Selector=”$Config/SyncTime$” ParameterType=”string” />
  </OverrideableParameters>
  <ModuleImplementation Isolation=”Any”>
    <Composite>
      <MemberModules>
        <DataSource ID=”scheduler” TypeID=”System!System.SimpleScheduler”>
          <IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>
          <SyncTime />
        </DataSource>
        <ProbeAction ID=”SQLProbe” TypeID=”ABC.Application.Probe.GenericSQLQueryPS”>
          <SQLInstance>$Config/SQLInstance$</SQLInstance>
          <Database>$Config/Database$</Database>
          <strQuery>$Config/strQuery$</strQuery>
        </ProbeAction>
      </MemberModules>
      <Composition>
        <Node ID=”SQLProbe”>
          <Node ID=”scheduler” />
        </Node>
      </Composition>
    </Composite>
  </ModuleImplementation>
  <OutputType>System!System.PropertyBagData</OutputType>
</DataSourceModuleType>

  </ModuleTypes>
</TypeDefinitions>

<DisplayStrings>

<DisplayString ElementID=”ABC.Application.DataSource.GenericSQLQueryPS”>
  <Name>ABC Application DataSource Generic SQL Query PS</Name>
</DisplayString>
4. Now, the last part: the rule itself, which leverages the Datasource directly and adds a condition detection and an alert action:

<Monitoring>

<Rules>

<Rule ID=”ABC.Application.Rule.SQLQuery.Test” Enabled=”false” Target=”Windows!Microsoft.Windows.Server.OperatingSystem” ConfirmDelivery=”true” Remotable=”true” Priority=”Normal” DiscardLevel=”100″>
  <Category>Custom</Category>
  <DataSources>
    <DataSource ID=”DS” RunAs=”ABC.Application.RunAsProfileSQLQueries” TypeID=”ABC.Application.DataSource.GenericSQLQueryPS”>
      <IntervalSeconds>300</IntervalSeconds>
      <SQLInstance>your_SQL_Server_Instance</SQLInstance>
      <Database>Database</Database>
      <strQuery>SELECT ETC FROM ETC</strQuery>
    </DataSource>
  </DataSources>
  <ConditionDetection ID=”Filter” TypeID=”System!System.ExpressionFilter”>
    <Expression>
      <And>
        <Expression>
          <SimpleExpression>
            <ValueExpression>
              <XPathQuery Type=”String”>Property[@Name=’RecordCount’]</XPathQuery>
            </ValueExpression>
            <Operator>Greater</Operator>
            <ValueExpression>
              <Value Type=”String”>0</Value>
            </ValueExpression>
          </SimpleExpression>
        </Expression>
        <Expression>
          <SimpleExpression>
            <ValueExpression>
              <XPathQuery Type=”String”>Property[@Name=’TransactionTimeMS’]</XPathQuery>
            </ValueExpression>
            <Operator>Less</Operator>
            <ValueExpression>
              <Value Type=”String”>5000</Value>
            </ValueExpression>
          </SimpleExpression>
        </Expression>
      </And>
    </Expression>
  </ConditionDetection>
  <WriteActions>
    <WriteAction ID=”Alert” TypeID=”Health!System.Health.GenerateAlert”>
      <Priority>1</Priority>
      <Severity>2</Severity>
      <AlertMessageId>$MPElement[Name=”AlertMessageID50555aef48434eeea982400717e04b15″]$</AlertMessageId>
      <Suppression>
        <SuppressionValue>$Target/Host/Property[Type=”Windows!Microsoft.Windows.Computer”]/NetbiosComputerName$</SuppressionValue>
      </Suppression>
    </WriteAction>
  </WriteActions>
</Rule>

</Rules>

</Monitoring>

<Presentation>
  <StringResources>
    <StringResource ID=”AlertMessageID50555aef48434eeea982400717e04b15″ />

  </StringResources>

</Presentation>
<DisplayStrings>

<DisplayString ElementID=”AlertMessageID50555aef48434eeea982400717e04b15″>
          <Name>APP SQL Query Error – SQL server unavailable</Name>
          <Description>”No records were return when querying the Server or over time</Description>
        </DisplayString>

</DisplayStrings>

 

And there you go. All you have to do is put all that into Visual Studio Authoring Extensions, mix, stir and poof! You got your MP.

Make sure you read my previous VSAE related posts.

If you just want to get the MP, get it here! Don’t forget to create more rules, for different queries (or create overrides) and to override the rule to enable it to the computers you want to be the watches nodes. I also strongly recommend you to seal the management pack, just so you can update it freely and still keep your overrides.

Hope this helps!