How to get the version number of sqlserver using mssql of windows_exporter

[ilanni2460]

How to get the version number of sqlserver using mssql of windows_exporter

[DiniFarb]

As it seems it is not possible yet. (I might be wrong)

I am happy to create a PR to implement it but I think it is worth discussing first.

My idea/thoughts would be to scrape the info from the registry right after it gets the instance names. f.e. in the loop here:

windows_exporter/pkg/collector/mssql/mssql.go

Lines 61 to 65 in 0ada09e

	for _, instanceName := range instanceNames {
	if instanceVersion, _, err := k.GetStringValue(instanceName); err == nil {
	sqlInstances[instanceName] = instanceVersion
	}
	}

with the variable instanceVersion a second regkey could be retrieved f.e.
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\Setup
which would be
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancVersion>\Setup The setup folder contains the version and some other base infos:

Some of those values could then be added as base info regardless of which sub modules from the mssql collector are active.

The downside is that this info is only retrieved once, when the exporter is starting. On the other hand, I think for a upgrade of mssql a restart of the machine is needed anyways. It could be moved to the Collect func to retrieve the infos in each cycle but whats the point if the values barely change.

So I am not sure whats best maybe there is even a better way to get the info as from the registry - lets hear if there are any other ideas from the community.

@jkroepke and @breed808 I'd value your opinions.

[jkroepke]

Is that stable? What happens, if multiple versions of MSSQL server are installed? What happens, if an SQL server instance was upgraded and the old version leaves some registry keys?

I feel the best way would be using the mssql_exporter which does a local connect to the instance.

[DiniFarb]

It should create a regkey for each instance with its own values. But yeah "it should" and now when I think of - what if in a future version ms decides to change the behavior of those keys it would probably break or as you say some old unused keys are breaking things.

I get the point with using the mssql exporter but I also like to keep things simple and the mssql module of this exporter is in my case enough, so getting a second one just for keeping track of the version is a bit a overhead.

Maybe there is another way to get the version - if I can manage some time I'll try to look into it a little deeper. But I agree, registry is probably no good way to go.

[jkroepke]

You can also use the textfile collector to provide that information.

[DiniFarb]

Yes in this case the textfile collector is a good solution, here a ps script to generate the prom file

note: I created this very quickly, might be buggy. But can be used as a start.

$workingDir = (pwd).Path
$tempFile = $workingDir + "\temp-mssql.txt"
$promFile = $workingDir + "\mssql.prom"
$errorMessage = $workingDir + "\error-mssql.txt"

$metric = @{
    name = "windows_mssql_version_info"
    type = "gauge"
    help = "MSSQL version info, 1 if collected or 0 if there was an issue"
    lables = @{
        version = ""
        edition = ""
    }
    value = 0
}
try {
    $version = Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('ProductVersion')" -ErrorAction Stop 
    $metric.lables.version = $version.Column1
    $edition = Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('Edition')" -ErrorAction Stop
    $metric.lables.edition = $edition.Column1
    $metric.value = 1
} catch {
    Set-Content -Path $errorMessage -Value $_
}
try {
    Set-Content -Path $tempFile -Encoding Ascii -NoNewline -Value ""
    Add-Content -Path $tempFile -Encoding Ascii -NoNewline -Value "# HELP $($metric.name) ($($metric.help))`n"
    Add-Content -Path $tempFile -Encoding Ascii -NoNewline -Value "# TYPE $($metric.name) $($metric.type)`n"
    Add-Content -Path $tempFile -Encoding Ascii -NoNewline -Value "$($metric.name){version=`"$($metric.lables.version)`",edition=`"$($metric.lables.edition)`"} $($metric.value)`n"
    Move-Item -Path $tempFile -Destination $promFile -Force
} catch {
    Set-Content -Path $errorMessage -Value $_
}

[jkroepke]

Hi, it seems like the info is available via WMI, could someone confirm it?

https://medium.com/@rakesh.mr.0341/utilizing-wmi-provider-for-configuration-management-in-sql-server-909df4721c5b

[DiniFarb]

The required PS module SqlServer is not necessarily installed. I have testen on:
win11 with MSSQL2022 -> not there
win2016 with MSSQL2019 -> is there

to be checked on:
C:\Program Files\WindowsPowerShell\Modules
or with:
Get-Module -ListAvailable

and also the WMI class SqlServiceAdvancedProperty did not exist in my case, there was a SQL_Version though.

But all in all I think it is not stable to use.

[jkroepke]

Someone interested into testing #1741?

https://github.com/prometheus-community/windows_exporter/actions/runs/11882244279/artifacts/2198830587

[ilanni2460]

@jkroepke I very much look forward to this PR being merged into the main branch.

[jkroepke]

At least, I need some confirmations, that it works in other scenarios.