SQL Sentry - Orion Integration

Requirements:

  1. Solarwinds SAM (any Orion product can be used with some tweaks).
  2. Solarwinds SAM Application Insights, assigned to SQL Servers (can be tweaked if Application Insights are not used).
  3. Solarwinds SQL Sentry with the Web Portal already setup.
  4. Credentials to the SQL Sentry database (read only is fine).
  5. Credentials to Orion with permission to update custom properties.

Setup Steps:

On the Orion server (or another server) install the Orion SDK.  This is the server where the below PowerShell script will be run from.

Create an Application Custom Property in your Orion instance named SqlSentryGUID. This Application Custom Property should have a format of ‘Text’.

To make sure the SQLSERVER PowerShell module is installed,
Open PowerShell as administrator and run the following commandlet: Install-Module sqlserver

From the server where the SDK has been installed, update the PowerShell script variables to match your environment.  Here is a list of variables from the script that will need to be edited. Below this list you will find the PowerShell script to edit and run.


  • $SqlSentryInstance
  • $SqlSentryDatabase
  • $SqlSentryDatabaseUsername
  • $SqlSentryDatabasePassword
  • $OrionHostname
  • $OrionUser
  • $OrionPassword

###
### SQL Sentry Connection Properties
###
$SqlSentryInstance = ""
$SqlSentryDatabase = ""
$SqlSentryDatabaseUsername = ""
$SqlSentryDatabasePassword = ""

###
### Orion SWIS Connection Properties
###
$OrionHostname = ''
$OrionUser = ''
$OrionPassword = ''

###
### Connect to SQL and get SqlSentry monitored instance details
###
$SqlSentryQuery = "
  select [esc].[ObjectID]     as ObjectID
       , [esc].[ObjectName]   as ObjectName
	   , [esc].[InstanceName] as InstanceName
       , [device].[IPAddress] as IPAddress
       from [" + $SQLSentryDatabase + "].[dbo].[EventSourceConnection]     as [esc]
          , [" + $SQLSentryDatabase + "].[dbo].[EventSourceConnectionType] as [esct]
          , [" + $SQLSentryDatabase + "].[dbo].[Device]                    as [device]
          where [esct].[Name] like 'SQL Server'
	        and [esct].[ObjectID] = [esc].[EventSourceConnectionTypeID]
            and [esc].[DeviceID] = [device].[id]"

$SqlSentryData = Invoke-Sqlcmd -ServerInstance $SqlSentryInstance -Database $SqlSentryDatabase -Username $SqlSentryDatabaseUsername -Password $SqlSentryDatabasePassword -Query $SqlSentryQuery

###
### Connect to Orion SWIS and get AppInsight Details
###
$SWISConnection = Connect-Swis -Hostname $OrionHostname -Username $OrionUser -Password $OrionPassword
#$swis = Connect-Swis -Hostname $hostname -Credential $creds
$SWISData = Get-SwisData $SWISConnection "
select [node].[ipAddress]    as [NodeIP]
     , [app].[ApplicationID] as [AppID]
     , [app].[Name]          as [AppName]
     , [app].[DisplayName]   as [AppDisplayName]
     , [app].[NodeID]        as [NodeID]
     , [app].[ApplicationTemplateID]  as [TemplateID]
     , [app].[uri] as [uri]
     from [Orion].[APM].[Application] as [app]
        , [Orion].[Nodes]             as [node]
        where [app].[ApplicationTemplateID] = 13
          and [node].[NodeID] = [app].[NodeID]"

###
### Create Custom Properties associating Orion AppInsight for SQL with its corresponding SentryOne node.
###
foreach ($instance in $SqlSentryData) {
   #write-host "GUID " $instance.ObjectID " IP " $instance.IPAddress
   foreach ($server in $SWISData) {
       if (($server.NodeIP -eq $instance.IPAddress) -and ($server.AppName -eq $instance.InstanceName)) {
           $customProps = @{ SqlSentryGUID=$instance.ObjectID.ToString(); }
           write-host "Inserting " $instance.ObjectID " into AppID " $server.AppID
           #$uri = "swis://$OrionHostname/Orion/Orion.Nodes/NodeID="+$server.NodeID+"/Applications/ApplicationID="+$server.AppID+"/CustomProperties"
           $AppUri = "$($server.uri)/CustomProperties"
           write-host $AppUri
           Set-SwisObject $SWISConnection -Uri $AppUri -Properties $customProps
           break
       } elseif (($server.NodeIP -eq $instance.IPAddress) -and ([string]::IsNullOrWhiteSpace($instance.InstanceName) -and ($server.AppName -match '(MSSQL)|(SQLEXPRESS)'))) {
           $customProps = @{ SqlSentryGUID=$instance.ObjectID.ToString(); }
           write-host "Inserting " $instance.ObjectID " into AppID " $server.AppID
           #$uri = "swis://$OrionHostname/Orion/Orion.Nodes/NodeID="+$server.NodeID+"/Applications/ApplicationID="+$server.AppID+"/CustomProperties"
           $AppUri = "$($server.uri)/CustomProperties"
           write-host $AppUri
           Set-SwisObject $SWISConnection -Uri $AppUri -Properties $customProps
           break
       }
   }
}

After editing the above script, execute it.  It will automatically populate the custom property with the SqlSentryGUID(s) needed to identify specific SQL instance(s) from the Orion Server.  This can either be scheduled, or just run when you add new SQL instances to SQL Sentry and/or Orion.

In the Orion Web Console, navigate to the Applications SQL Server view. 

From the left navigation select Customize PagePage Settings

  1. Choose Add Tab.
  2. Name the tab whatever you like, I called mine SQL Sentry Summary.
  3. Click the Update button to save the name.
  4. Remove Column 2 & 3 (if applicable)
  5. Click Add
  6. Search for “Custom Html Resource” and add it to Column 1.

After saving, Edit the Custom HTML Resource...



...and add the following iframe code, substituting your SQLSentry server information by replacing SQL_SENTRY_WEBPORTAL_URL with text appropriate to your SQL Sentry Server hostname or IP address and Port, and adjusting width/height as you see fit.

<iframe src="http://SQL_SENTRY_WEBPORTAL_URL" width="100%" height="1000" title="SQLSentry Health"></iframe>


Next, you add the SQL instance specific performance view. From the Main SQL Server Summary page, navigate to an individual AppInsight for SQL page and edit the left navigation following the instructions above.

Here we are creating an SQL instance specific details view so name the subview accordingly.  Since this will be instance-specific, the Custom HTML will be a little more complicated than the basic iframe above and requires Javascript to query the Orion API for the GUID that was copied in with the previous powershell script.

When you navigate to your SQL Sentry Web Portal you will notice it automatically adds a GUID similar to the highlighted one below after YOUR_SERVER_IP.

http://<YOUR_SERVER_IP>/2c96c4ef-85b7-45a3-fdf3-a480935f64da/health

You will want to replace the highlighted section of the iframe.src assignment with your server specific details...

iframe.src = "http://<SqlSentry Web Portal IP>/<Server specific GUID>/targets/" + guid + "/performance";

... but do it in the code below, on line 10:

<script type="text/javascript">
var swql = 'select [acp].[SQLSentryGUID] from Orion.APM.Application as [app], Orion.APM.SqlServerApplication as [sql], Orion.APM.ApplicationCustomProperties as [acp] where [app].[ApplicationID] = [sql].[ApplicationID] and [app].[ApplicationID] = [acp].[ApplicationId] and [app].[ApplicationID] = @appid';
var appid = ${ApplicationID};
var customHTMLSelector = '#ctl00_ctl00_ctl00_BodyContent_ContentPlaceHolder1_ApmMainContentPlaceHolder_resHost_resContainer_rptContainers_ctl00_rptColumn1_ctl00_ctl01_ResourceWrapper_HTML_DIV';

function buildIframe(guid) {
  var iframe = document.createElement('iframe');
  iframe.width = 1293;
  iframe.height = 1233;
  iframe.src = “http://<SqlSentry Web Portal IP>/<Server specific GUID>/targets/” + guid + "/performance";
  iframe.name = "sentry-one"
  document.querySelector(customHTMLSelector).appendChild(iframe);
};

function buildError() {
  var notFoundHeading = document.createElement("h1");
  notFoundHeading.color = "#ff0000";
  notFoundHeading.innerText = "No SQL Sentry Node";
  document.querySelector(customHTMLSelector).appendChild(notFoundHeading);
};

var params = JSON.stringify({
  query: swql,
  parameters: { "appid": appid }
});

$.ajax({
  type: 'POST',
  url: '/Orion/Services/Information.asmx/QueryWithParameters',
  data: params,
  contentType: 'application/json; charset=utf-8',
  dataType: "json",
  success: function(res) {
    if(res.d.Rows[0][0]) {
      buildIframe(res.d.Rows[0][0]);
    } else {
      buildError();
    }
  },
  error: function(err) {
    buildError();
    console.error(err);
  }
});
</script>