There are no SQL Servers in your configuration file. No SQL metrics will be collected.

[rjunified]

I am obviously being a complete fool, but I when I try and collect SQL stats I just get the error "There are no SQL Servers in your configuration file. No SQL metrics will be collected."

I have tweaked the below lines with my SQL details

    <SQLServers>
        <!-- A SQL Server Connection Using SQL Authentication -->
        <SQLServer ServerInstance="MYSERVERNAME" Username="mysauser" Password="PASSWORD1!">

and I have edited the query.

What am I doing wrong?

Thanks

[kasperbrandenburg]

Did you start the Start-SQLStatsToGraphite function? I've fixed some bugs in the code regarding parsing the SQL Server objects in the config file. Try my branch (still waiting on feedback from project maintainer) https://github.com/kasperbrandenburg/Graphite-PowerShell-Functions/tree/Fix-SQLMetricCollector-AddMultirowSupport

[rjunified]

hi, wow thanks for the quick reply. When i try and run it that is when i get the error

PS C:\Windows\system32> Start-SQLStatsToGraphite -testmode WARNING: There are no SQL Servers in your configuration file. No SQL metrics will be collected.

How does it know that I have entered the SQL details and it is not just the sample details in there? Is there a flag or something that I need to change to enable the SQL bit?

I am trying to run this from a remote box to query the SQL server remotely I assume that doesnt matter. Appreciate your help.

Rob

[kasperbrandenburg]

Hi, did you try my branch? Please dump your SQLServer sections (remove password) and try using -Verbose or set Verbose TRUE in config file to get more output. I'm pretty sure i faced the same issue using Matts branch.

Running the scripts on a remote box is just fine, as long as you have installed the cmdlets required to connect to SQL Server. This scripts uses Invoke-SqlCmd cmdlet, which ships with SQL Server and can be installed seperatly. Try doing Get-Help Invoke-SqlCMd in a powershell prompt.

Regardless.. the message you are seeing is the scripts parsing the SQLServer section of your config file. That needs to be fixed first.

[rjunified]

yeah just tried your branch but didnt seem to make any difference, I initially just edited the first few lines but I have stripped the lines I wasnt using in this test so it is now just

<MSSQLMetics>
    <MetricPath>Parent.Child.SQL</MetricPath>
    <MetricSendIntervalSeconds>120</MetricSendIntervalSeconds>
    <SQLConnectionTimeoutSeconds>5</SQLConnectionTimeoutSeconds>
    <SQLQueryTimeoutSeconds>5</SQLQueryTimeoutSeconds>
    <SQLServers>
        <!-- A SQL Server Connection Using SQL Authentication -->
        <SQLServer ServerInstance="S-2K8-SS02\MSSQLSERVER" Username="username" Password="password">
            <Query Database="EDDS" MetricName="edds.activeusercount" TSQL="SELECT COUNT ([UserID]) FROM [EDDS].[eddsdbo].[UserStatus]"/>
        </SQLServer>
    </SQLServers>
</MSSQLMetics>

Thanks again

Rob

[kasperbrandenburg]

In my branch i've added an extra attribute to the element, so that might brake later, since that missing from your Config.

Here an example of mine

<MSSQLMetics>
    <MetricPath>collectd.ops.servers.sqlservers</MetricPath>
    <MetricSendIntervalSeconds>300</MetricSendIntervalSeconds>
    <SQLConnectionTimeoutSeconds>60</SQLConnectionTimeoutSeconds>
    <SQLQueryTimeoutSeconds>60</SQLQueryTimeoutSeconds>
    <SQLServers>
        <!-- A SQL Server Connection Using Windows Authentication. The credentials from the running PowerShell session will be used. -->
        <SQLServer ServerInstance="<database-server>" Username="<user>" Password="<password>">
            <!-- An example query SQL query using a greater-than symbol. The symbol must be replaced with an XML Entity References. List here - http://msdn.microsoft.com/en-us/library/windows/desktop/dd892769%28v=vs.85%29.aspx -->
            <Query Database="SiteOps" MetricType="MultiRow" MetricName="" TSQL="EXEC sp_Get_ServerMonitor_DatabaseCpuPerformance @MetricBase = '<database-server>'"/>
            <Query Database="SiteOps" MetricType="MultiRow" MetricName="" TSQL="EXEC sp_Get_ServerMonitor_DatabaseBufferPoolUsage @MetricBase = '<database-server>'"/>
            <Query Database="SiteOps" MetricType="MultiRow" MetricName="" TSQL="EXEC sp_Get_ServerMonitor_SqlServerWaitStats @MetricBase = '<database-server>'"/>
            <Query Database="SiteOps" MetricType="MultiRow" MetricName="" TSQL="EXEC sp_Get_ServerMonitor_SqlServerPerformanceCounters @MetricBase = '<database-server>'"/>
            <Query Database="SiteOps" MetricType="MultiRow" MetricName="" TSQL="EXEC sp_Get_ServerMonitor_SqlServerMemoryClerks @MetricBase = '<database-server>'"/>
            <Query Database="SiteOps" MetricType="MultiRow" MetricName="" TSQL="EXEC sp_Get_ServerMonitor_DatabaseDiskPerformance @MetricBase = '<database-server>'"/>
        </SQLServer>
    </SQLServers>
</MSSQLMetics>

Did you start by importing the module? I've put mine in C:\Windows\System32\WindowsPowerShell\v1.0\Modules\ in a Graphite-Powershell folder, and i Import the module before i run the function.

I dont know if what would make a difference in all cases. Give it a try. The Config path is configured through loading the module into your powershell session. But newer versions of PS should do the automatically if the module is placed in the powershell path.

Import-Module Graphite-Powershell Start-SQLStatsToGraphite

[rjunified]

thanks will give it a try with all your stuff, will also try it on one of the dev sql boxes itself, see if that makes a difference. Will report back. Thanks for your help.

Rob

[kasperbrandenburg]

alright, happy debug.

[rjunified]

hi kasperbrandenburg, me again.

so good news, Its alive using your branch wooohoooo!

Although, if I use the multirow query it seems to ignore the "MetricName" set in the query line and just use the row names returned by the query itself, is this how yours is working?

Thanks again

Rob

[kasperbrandenburg]

Hi Rob

Great news that its working. Yes... MetricName is the first column of data, and the value is the second column. This is as i intended. It gives the freedom to fabricate multiple metrics in a single query. You you'd rather use Matts, just set the MetricType="SingleRow" and define the MetricName.. then it will use the MetricName attribute.

[rjunified]

Hi, thanks again for replying. Sorry for the delay I have been away from a computer. My query is actually a multi row query so good thing i was using your branch. I have multiple multi row queries though and i wanted different sub folders for them at the level above the metric

I modified the multi row bit in the function instead to add the metric name and then the multi row values. All good now. Thanks so much!

My multirow build metric looks like:

$metricPath = $Config.MSSQLMetricPath + '.' + $query.MetricName + '.' + $sqlresult[0]