Configuring vRealize Log Insight Agent on MS SQL Server 2012

There’s a lot of information available about the SQL Server Content Pack for Log Insight, but actually  trying to use it reveals a lot of gaps.  So, here’s what I’ve done to get my SQL Server 2012 R2 instances to correctly report their information to vRealize Log Insight and show up in the Content Pack filters.

To ensure the correct log directories are used and allow reuse of filelog sections, I suggest creating an agent group for each SQL Server.  I’m also a fan of the server-side agent configuration, so if the agent gets totally removed and reinstalled, it’ll acquire the correct configuration again.

Steps:

  1. Install the Log Insight Agent for Windows on the SQL Server machine, be sure to use the FQDN of the Log Insight VIP
  2. While logged on to the SQL Server, locate the Logs folder for each SQL server instance. Note that this is not the database transaction logs folder – if it contains *.ldf files, it’s the wrong folder.   Specifically, you want the folder that contains the ERRORLOG file.  To be certain follow these steps:
    • Launch SQL Server Configuration Manager
    • Select “SQL Server Services” from the left pane
    • Right-click “SQL Server (INSTANCENAME)” from the right page, choose “Properties”.
    • Select the “Startup Parameters” tab
    • In the Existing Parameters box, make a note of the parameter that starts with “-e”
    • The filepath in that parameter (minus “ERRORLOG”) is the folder we need.
    • DO NOT CHANGE ANY VALUES, Cancel Properties, Close Configuration Manager

Startup Params in ConfigurationManager

  1. Logon to Log Insight as an administrator
  2. Check the Content Package area to be sure the “Microsoft – SQL Server” Content Pack is installed
  3. In the next steps, we’ll configure the agent from the server-side. It is possible to do from the client-side.
  4. In the Administration|Agents section of Log Insight select Microsoft – SQL Server from Available Templates drop-down list
  5. Click the “Copy Template” button. In the Copy Agent Group box that appears, change the name to “Microsoft – SQL Server – SERVERNAME”.  (replace SERVERNAME with the actual server’s name).  Click “Copy”.
  6. Now, the dropdown list should indicate that you are editing the new Agent Group
  7. In the Filter box, edit the parameters to create a filter that identifies only the desired server. For example “Hostname | starts with | SERVERNAME”
  8. In the Agent Configuration section
    • Update the section header from “[filelog|MSSQL]” to [filelog|MSSQL-SERVERNAME-INSTANCENAME]” replace SERVERNAME and INSTANCENAME with the correct, actual values. Be sure to keep “MSSQL” as the prefix, since the dashboard elements key on that.
    • Update the directory value to where the ERRORLOG file was found
    • Update the exclude value to read *.trc;*.xel;*.mdmp
    • Example:

[filelog|MSSQL-COREDB1-MSQLSERVER]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.MSSQLSERVER\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

  1. If there are multiple instances of SQL server on the host server, copy the entire section and edit the section header with the Instance Name. Example of configuration for multiple instances:

[filelog|MSSQL-COREDB1-MSSQLSERVER]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.MSSQLSERVER\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

[filelog|MSSQL-COREDB1-WORKLOAD]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.WORKLOAD\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

  1. Click Save New Group.
  2. On the SQL Server, restart the “VMware vRealize Log Insight Agent”
  3. Navigate to %ALLUSERSPROFILE%\VMware\Log Insight Agent and open liagent-effective.ini with notepad. Check that the sections added above appear in this file.
    • If they do not, you may have to adjust the filters on the Agent Group

Automating syslog configuration on NSX

03/09/2016 Comments off

If you’re here, you’ve probably already found VMKB2092228 and been frustrated by the lack of an easy and consistent way to configure all the NSX components to send log data.  Me too.

I put together a vRO workflow to help configure (and reconfigure) syslog on NSX.

  1. It prompts for the NSX manager connection info and Syslog server info
  2. Creates a REST host for the NSX manager using the admin super-user account
  3. Adds several REST operations to the NSX Manager REST host
  4. Deletes and readds the syslog configuration on the NSX Manager
  5. Optionally configures Activity Monitoring on the NSX Manager
  6. Identifies each running Controller; deletes and readds the syslog config on each
  7. Identifies each deployed and “Green” Edge (including DLRs); deletes and readds the syslog config on each
  8. Removes the NSX Manager REST host created earlier

Notes

  • It must use the NSX Manager admin account – it’s the only superuser account that can update teh manager config.
  • If a Controller is not running when the workflow runs, its syslog config will not be updated
  • If an Edge’ state is not “green” or is not fully deployed, its syslog config will not be updated
  • REST-HTTP plugin for vRO is required
  • Tested with vRO 6.0.2 and NSX 6.2.1
  1. Get the package Download Here

    By downloading any code, package or file, you acknowledge that:There is no explicit or implied warranty or support for the code.  Neither Brian Ragazzi, his employer nor anyone else is responsible for any problems, errors, omissions, unexpected behavior, breakage, trauma, outage, fatigue, lost time, lost work or incontinence that may occur as a result of using the code or package.

  2. Import package into vRO
  3. Run the ConfigureSyslogNSX workflow
    Workflow Step 1

    Workflow Step 1

    Workflow Step 2

    Workflow Step 2

About Cross-vCenter NSX

02/02/2016 Comments off

I’ve spent the past several weeks testing, trying to understand Cross-vCenter NSX and make it work in a useful way.  Here’s what I’ve learned so far.

Environment and Set up:

I knew I needed two “sites” with discrete storage, but had a few physical limitations.  I.e; I only own one managed router (Cisco C2821) and one managed switch (Force 10 S50).  I trunked the management and vtep network to all the hosts, but configured a discrete transit network for each “site”.  I hope to work with a much larger lab environment and do a thorough review of the setup and configuration.  In the meantime, here’s the basics.

  • Two-host cluster, NSX manager and vCenter Server for primary site
  • 1 host cluster, NSX manager and vCenter Server for recovery site
  • vSphere 6.0 U1b
  • NSX for vSphere 6.2.1
  • Universal Transport Zone includes both clusters, configured for Local Egress.
  • VTEPs in same L3 network (for simplicity’s sake)
  • Edge per site, discrete transit networks
  • Universal DLR for tenant networks, uplinked to both site ESGs
  • OSPF area for each ESG->UDLR
Partial Lab Network

Partial Lab Network

Objectives

  1. Eliminate need for manual synchronization between sites/NSX instances
    • Success! The distributed firewall and universal objects are respected by the NSX manager at both sites.  Universal Security Groups and Logical Switches are usable at either end.
  2. Span VXLANs between sites
    • Success! This is not really a surprise.  As long as the clusters share a Transport Zone and teh VTEPs can route to each other, this works great.
  3. Minimize network alterations when used with Site Recovery Manager to failover protected VMs between vCenter Servers.
    • Partial success.  A placeholder/recovered VM will be in the same Universal Security Groups as the protected VM and the distributed firewall rules will apply as expected without any changes needed.  However, even with Local Egress enabled, you’ll have to apply some sort of route updates so that traffic destined for the recovered VMs can get there.  It looks like the route redistribution for egress us handled automatically though.  This configuration pushed the limits of what I can do with my lab.
  4. Make use of site-to-site microsegmentation (my definition:  application of security policy regardless of L3 network scope)
    • Partial Success.  I was really disappointed here. When the document states that you can add IP Sets, MAC Sets and other Universal Security Groups to a Universal Security Group, it means that’s ALL you can add.  I’ve blogged a few times about adding VMs to a Security Group – even doing it as a Resource Action in vRA.  That won’t work with Universal Security Groups!  The only workaround I see is to add the desired VM’s IP or MAC to a Set and include the Set in the Security group.  Blech!
  5. Retain ability to assign security policy via security group membership through vRA
    • FAILURE.  As noted above, we cannot assign Universal security group membership to a VM using any of the existing custom properties.

Notes and Observations

I guess I get it.  The vm id between vCenter Servers will differ, so saying that “vm-123 is a member of securitygroup-456” is not valid on a different vCenter Server.  But a table of IPs and MACs would be universally valid.  I’m hoping that in a near-future version, the universal security group membership capabilities can be extended; perhaps with a shared or replicated vCenter Inventory Service.

 

Resolving Site Recovery Manager Error “Unable to create protection group. No VRM registered with vCenter Server”

Just a quick note here about this error.  I felt kinda silly once I realized what I’d done.

Scenario:

  1. Installed a vCenter Server at both sites
  2. Deployed a vSphere Replication 6.1 appliance at both sites.  Connected each to its corresponding PSC and vCenter Server
  3. Installed Site Recovery Manager 6.1 on each site, registered to corresponding vCenter Server
  4. Complete mappings and basic configurations in SRM, confirm VR is available at each site

    SRM configured with VR

    SRM configured with VR

  5. Attempt to create a protection group
  6. Receive Error “Unable to create protection group. No VRM registered with vCenter Server …”
  7. Google frantically

Its not clear from the documentation, but you have to configure at least one VM for replication using vSphere Replication section of Web Client first.  Totally counter-intuitive.  Just right-click the target VM and select “All vSphere Replication Actions | Configure Replication” and walk through the wizard.  Once complete, you can return to the SRM configuration and succesfully set up a Protection Group and subsequent Recovery Plan.

Weak Diffie-Hellman key in vRealize Orchestrator

{Edited Oct 19 2015 to reflect updated information inVMKB 2131619}

Recent versions of Google Chrome and Mozilla Firefox have begun rejecting connections using SSLv3 ciphers. Chrome complains of a weak ephemeral Diffie-Hellman public key, calling it a “disastrous misconfiguration”.  Firefox’s message also complains of a weak ephemeral Diffie-Hellman key in Server Key Exchange, but doesn’t foreshadow impending doom.

Interestingly (I guess), Internet Explorer 11, still happily connects…

Firefox message on vRO configuration page

Firefox message on vRO configuration page

Chrome message on vRO configuration page

Chrome message on vRO configuration page

Let’s fix Orchestrator so that we can use FF and Chrome…

Procedure

Confirmed this works on the vCO Appliance v5.5.2.1 through v6.0.2.1 and on the vRealize Automation Appliance v6.2.x

  1. SSH into the appliance
  2. Enter this to navigate to the configuration for the configuration page

    cd /etc/vco/configuration

  3. Enter this to backup the server.xml file

    cp ./server.xml ./serverxml.backup

  4. Use vi, or whatever you’re familiar with, to edit server.xml and replace the line that reads (as one line)

    ciphers=“TLS_DHE_RSA_WITH_AES_256_CBC_SHA,
    TLS_DHE_DSS_WITH_AES_256_CBC_SHA,
    TLS_RSA_WITH_AES_256_CBC_SHA,
    TLS_DHE_RSA_WITH_AES_128_CBC_SHA,
    TLS_DHE_DSS_WITH_AES_128_CBC_SHA,
    TLS_RSA_WITH_AES_128_CBC_SHA”

    with (again, as one line)

    ciphers=“TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,
    TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,
    TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA,TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA,
    TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA,
    TLS_ECDH_RSA_WITH_AES_256_CBC_SHA,
    TLS_RSA_WITH_AES_256_CBC_SHA,TLS_RSA_WITH_AES_128_CBC_SHA,
    TLS_RSA_WITH_3DES_EDE_CBC_SHA”

  5. save the file
  6. Repeat the steps above for /etc/vco/app-server/server.xml
  7. Restart the vco-server and vcp-configurator services

    service restart vco-server
    service restart vco-configurator

That’s it, you should be good to go. There’s probably other VMware applications that will need the same treatment though.

Not Diffie-Hellman

Dang, wrong Hellman

Fix – Unable to import vCAC/vRA certificates into Orchestrator

07/17/2015 Comments off

Problem:

While in the vRealize Orchestrator Client you find that the Library/Configuration/SSL Trust Manager/”Import a certificate from URL” workflow returns an error reading “InternalError: handshake alert: unrecognized_name” when provided. The URL the resolves to the Load-Balancer VIP for the vCAC/vRA appliances.

 

Background:

Signed SSL certificate installed on vCAC/vRA Appliance, SSL Passthrough on NSX/vCNX Load-Balancer, vCAC/vRA Settings/Hostname set to resolve to VIP, matching SSL cert.

 

Fix:

  1. SSH into the vCAC Appliance as root
  2. Backup /etc/apache2/vhosts.d/vcac.conf to vcac.conf.bak
  3. Use vi to edit /etc/apache2/vhosts.d/vcac.conf
  4. Scroll down to  <virtualHost _default_:443>
  5. Add these lines

    ServerName fqdn.of.appliance.node

    ServerAlias: load.balancer.name

  6. Scroll further to ensure these params aren’t listed elsewhere, remove or revise if so.
  7. save the file and exit vi
  8. restart the vCAC/vRA services

Automating NSX Security Groups with vCAC/vRA – Part 2

***UPDATE***  The download link is currently broken.   I seem to have lost the file, will fix the link as soon as I find or recreate it.  Sorry about that. 😦

In part 1 of this series, we created a list of security groups and displayed that list to users during the request.  In this post, we want to enhance that functionality by adding these features.

  • Creation of Security Groups and inclusion in Dropdown lists
  • Add a VM to a Security Group post-provisioning
  • Import existing Security Groups into vRA inventory
  • Add a Security Group to a Dropdown list

We’re going to do that by importing a vCO package with some new workflows and actions, then link up the workflows to Advanced Services and Resource Actions.  As before, we’ll require the NSX and vCAC/vRA plugins for Orchestrator.

Preparation

  • Complete the creation of the VCNS.SecurityGroup.Names.production property dictionary and valuelist attribute from Part 1.  We’re going to reuse those items. so make a note of the exact name of the property dictionary and the valuelist attribute.  In my case, I’ve named the Property Dictionary VCNS.SecurityGroup.Names.production and also named the valuelist attribute for it VCNS.SecurityGroup.Names.production
  • Make sure vRA Advanced Services Server Configuration is complete and test the connection to the Orchestrator server.  The default, built-in VCO is fine.
  • Login to vCO client as a vCO Admin. Set the mode to “Design” and navigate to the Inventory tab.  Make sure that you have a connection listed under “vCAC Infrastructure Administration” and a connection listed under “NSX”.

 

Confirm that you have the necessary connections on the inventory tab

Confirm that you have the necessary connections on the inventory tab

Get the Package

I’ve put together a handful of workflows and actions that use or expand the NSX plugin to provide information of functionallity back to vRA.  Where possible, I reused existing library workflows, but in some cases, I had to use the API to create a REST call and consume that in an action.

By downloading any code, package or file, you acknowledge that:

There is no explicit or implied warranty or support for the code.  Neither Brian Ragazzi, his employer nor anyone else is responsible for any problems, errors, omissions, unexpected behavior, breakage, trauma, outage, fatigue, lost time, lost work or incontinence that may occur as a result of using the code or package.

Download the zip file.  It contains the package and a couple of images that can be used for the advanced services

Import the Package

  1. Extract the zip file
  2. In the vCO Client, navigate to the packages tab.
  3. Click the “import package” button and select the extracted .package file
  4. On the Package Import Information step, click “Import

    Package Import Information

    Package Import Information

  5. On the Import package… step, check the “Select/Deselect all” box to check all of the items.  Please note the server path, these should not be duplicates of anything else you have in your vCO inventory (unless you’ve already imported this package previously).  Click “Import Selected elements”.

    Select all items

    Select all items

  6. Review the workflows and actions added to your inventory.

Configure Advanced Services – Create Security Group

This service enables the user to create a new NSX Security Group and automatically adds its name to the appropriate dropdown list of security groups.  It can be added once for each different list of security groups.  You’ll need to know the exact name of the Property Dictionary and valuelist attribute you created in Part 1.

NSX Management Services

NSX Management Services

NSX Security Groups in vRA Items

NSX Security Groups in vRA Items

  1. While logged into vRA as a service architect, navigate to the Advanced Services tab, click “Custom Resources
  2. We need to make vRA aware of NSX Security Groups.  Click the Add button.
  3. In the Orchestrator Type field, enter “NSX:SecurityGroup“; for the Name, I suggest “NSX Security Group“, click Next

    Add NSX Security Group as Custom Resource

    Add NSX Security Group as Custom Resource

  4. On the details form, we’re not going to make any changes, but if you wanted to hide certain properties, you could here.  Click “Add“.
  5. Click “Service Blueprints”, then the “Add” button.
  6. On the Workflow tab, select the AddNewSecurityGrouptoDropdown workflow, click next.

    Select "AddNewSecurityGrouptoDropdown" workflow

    Select “AddNewSecurityGrouptoDropdown” workflow

  7. On the Details tab, set the name to something like “Create new Production NSX Security Group“, because we’re going to create the security group and add its name to the “production”dropdown list.  Click Next.

    Set the Service Item Name

    Set the Service Item Name

  8. On the Blueprint  Form tab, under the “Step” Form page (default), mouseover the text field labelled “Name of Custom Property Dictionary in vCAC/vRA”.  Click the pencil “edit” icon when it appears.

    Edit the Form Fields

    Edit the Form Fields

  9. Click the Constraints tab of the “Edit Form Field” window.  On the Value field, select “Constant” and enter “VCNS.SecurityGroup.Names.Production” (or whatever suffix you used) for the Property Dictionary.  Set the Visible value to “No” so it doesn’t show up. Click Submit on the Edit Form Field window.

    Set the name of the Property Dictionary to be updated

    Set the name of the Property Dictionary to be updated

  10. Using the same method, set the Name of the Attribute appropriately and its visibility to no
  11. Edit the “Value to be appended to the ValueList attribute” field.  Set the label to “New Security Group Name“.  Do not set a value or make this one invisible, we need the user to enter a value, submit to save.
  12. Edit the vCACIaaSHost field – using the Constraints tab again– when setting the value, choose constant, then click Add by the green plus, to display a treeview, where you can choose your connection to the IaaS Server.  Visible: No, submit to save.

    Select connection to IaaS host

    Select connection to IaaS host

  13. Edit the “NSX endpoint” field in the same way, selecting the NSX connection.
  14. When done, all fields except “New Security Group Name” will have a value.  Click Next.
  15. On the Provisioned Resource tab, select “securityGroup [NSX Security Group]“.  Click Add to save the service blueprint.
  16. Repeat steps 5-15 for any other dropdown lists containing security groups; say “Non-Production” for instance
  17. Highlight the Service Blueprint and click “Publish” to make the blueprint available for entitlements
  18. Navigate to Administration, Services.
  19. Add a new Service named “NSX Management” (for example) – I included a nifty image in the zip file
  20. Under Catalog Items, click the “Create new Production NSX Security Group” item to edit it.
  21. The Catalog item should inherit the Security Group icon from vCO, set its Service to “NSX Management”,click update to save.
  22. Create or Edit an entitlement to include the new Service and/or catalog item.
  23. Try it out, confirm that the Security Group was created in NSX, is visible in vCAC items and it name was added to the Property Dictionary

Configure Advanced Services – Import Security Group

This service allows you to make existing security groups visible as items in the vCAC Items view.  Once this is done, we’ll add actions that allow you to add the security group to a dropdown list.

  1. Click “Service Blueprints”, then the “Add” button.
  2. Select the “GetNSXSecurityGroup” workflow, click Next
  3. On the details tab, set the name to “Import NSX Security Group“, click Next
  4. On the Blueprint form, set the “connection” to the NSX connection in vCO, then hide the field.  Security Group Name will be a dropdown list of existing NSX Security Groups for the user to choose from. Click Next
  5. On the Provisioned Resource tab, select “securityGroup [NSX Security Group]“.  Click Add to save the service blueprint.
  6. Just as before, publish the service blueprint, add it to a service and an entitlement.

 Configure Advanced Services – Add Security Group to Dropdown list

With this service, we’ll let the user add the name of an existing Security Group to a drop down list.  Unlike the first two, this is implemented as a Resource Action, meaning it’ll be executed against an existing item (a Security Group in this case)

  1. Under Resource Actions, click “Add”
  2. For the Workflow, select the “AddExistingSecurityGrouptoDropdown“, click Next
  3. On the “Input Resource” tab, keep NSX Security Group, click Next
  4. On the Details tab, set the Name to “Add Security Group to Production list” or similar, set the description, leave the Type options unchecked.  click Next

    Set Action Name and Description

    Set Action Name and Description

  5. On the Form tab, just like the first service blueprint, set the Property Dictionary and Attribute names as appropriate.  VCNS.SecurityGroup.Names.production in my example, set visible to no on both.
  6. Again, we’ll set the vCACIaaSHost to the connection to the Server and hide the field
  7. Click Add to save the action.
  8. Repeat steps 1-6 for each security group dropdown list (say “non-production” for instance)
  9. Publish the action and add it to an entitlement

    Add Action to Entitlement

    Add Action to Entitlement

  10. Test by navigating toNSX under Items, highlight a Security group and Select “Add Security Group to…” from the Actions menu.

    Yay! A Resource Action

    Yay! A Resource Action

 Configure Advanced Services – Add VM to a Security Group

This service lets you add a provisioned VM to additional Security Groups.  So, at provisioning-time, the VM is added to the Security Group selected by the user, but we may need to refine the security by adding that VM to additional Security Groups.

  1. Under Resource Actions, click “Add
  2. For the Workflow, select the “AddVMtoSecurityGroup“, click Next
  3. On the “Input Resource” tab, keep IaaS VC VirtualMachine, click Next
  4. On the Details tab, set the name to “Add VM to a Security Group“, click Next
  5. On the Form tab, set the connection Value to the NSX connection.
  6. Leave the NSX Security Group field visible, click Add to save the action
  7. Publish the action and add it to an entitlement
  8. Test by selecting a machine under Items and “Add VM to a Security Group” from the Actions menu

    VM Resource Action for Security Groups

    VM Resource Action for Security Groups

  9. You’ll be presented with the list of allNSX Security Groups to which you can add the selected VM

    Select Security Group

    Select Security Group

Conclusion

This part of the series should help streamline the management of VMs and their membership in Security Groups.  Obviously, items like removing a VM from a Security Group or even removing a Security Group are not included here.  The NSX plugin is missing quite a bit of functionality available in the API, so those additional functions require significantly more configuration.

Thanks to John Dias for his information and examples posted here.