Windows_exporter: multiple classes on single collector

[wizardmatas]

hello, i am working on mssql collector an wanted ask if it is possible to have multiple classes on single collector ? the same applies collector generation tool.

[carlpett]

Hi @wizardmatas,
Very nice, looking forward to seeing it! In short, yes, you can have many classes. The generator doesn't handle that case, but you can look at for example the hyperv collector for inspiration. Here is the collect func. Basically all you need to do is a bit of copy/paste from the generator output and merge the collect calls.

[wizardmatas]

Ok I will try, I am not an go expert, but I know which metrics are important, so with some help we can make it! @carlpett another question can I expect mssql collector merged into wmi exporter with possibility to enable it with flag ? where have been some talks about sql in this thread https://github.com/martinlindhe/wmi_exporter/issues/11 however other sql exporters are a bit overkill, cause run sql queries to get counters which are available by default on wmi. wdyt?

[carlpett]

Yes, I think a WMI-based collector for SQL Server would be very worth having! The previous work was mainly abandoned due to lack of time in combination with existing options, not due to lack of value.

So please go ahead, and let us know if you need support!

[wizardmatas]

Ok, thanks, closing this one!

[pete-leese]

This sounds great!

I am very interested in getting some sql metrics from my instances.

Interested to see how well it scales as I have several instances per physical server.

If you need any help testing just tip me the wink.

I have sql instances ranging from 2008 - 2016.

[wizardmatas]

hi, @VR6Pete, I have created first steps on sql metrics exporter : https://github.com/wizardmatas/wmi_exporter (Commit)
I noticed that MS SQL Express and MS SQL Std ( and posible Ent), have different WMI classes so I have build collector based on MS SQL Std. Can you check if you get it working ?

[pete-leese]

I think standard edition is a good shout. How do I compile this?

[wizardmatas]

hi, @VR6Pete try go build in root folder of exporter, it will generate executable file.

[pete-leese]

I will setup a windows environment to compile the exe.

maybe worth while re-opening this issue as it actively being discussed @carlpett ?

[carlpett]

@VR6Pete While I think it is great that there is a discussion around testing this new collector, I don't think the issue itself (how to have multiple wmi classes in the same collector) is really open any more.
But feel free to continue here, or in a PR when @wizardmatas considers the code ready!

[pete-leese]

Roger that.

I've compiled a new .exe but cant see it collecting any stats.

Tried to set --collectors.enabled="sql" and "sqlstatistics" but doesn't appear to be loading the collector?

[31mFATA[0000] Couldn't load collectors: collector 'sql' not available

[wizardmatas]

@VR6Pete please pull again and try: .\wmi_exporter.exe --collectors.enabled "sqlservergeneralstatistics" maybe i messed up with names ...

[pete-leese]

ah, yes OK that works now, however...

wmi_exporter_collector_success{collector="sqlservergeneralstatistics"} 0

ERRO[0066] collector sqlservergeneralstatistics failed after 0.011856s: Exception occurred. (Invalid class ) source="exporter.go:105"

This is on a SQL server 2016 standard instance.

Microsoft SQL Server Standard (64-bit)
13.0.4435.0

[pete-leese]

Running on SQL Server 2008 R2 Standard (10.0.5520.0);

wmi_exporter_collector_success{collector="sqlservergeneralstatistics"} 1

Good, but all the stats return 0

no errors in console window I'm afraid.

Note that I have several SQL instances installed on each server.

I will try a SQL Server 2012 instance next.

[pete-leese]

Same result with SQL Server 2012.

[pete-leese]

SQL Server 2014:

wmi_exporter_collector_success{collector="sqlservergeneralstatistics"} 0

wmi_exporter_collector_success{collector="sqlservergeneralstatistics"} 0

Exception occurred. (Invalid class )

[wizardmatas]

Hi, @VR6Pete I will try to dig it in next week. It really works on my test sql server:

[pete-leese]

Happy to do some testing if you have any queries etc.. you need me to run to see what is being passed back.

Like I say, all my servers have multiple instances installed and that is my gut feeling at this point.

[wizardmatas]

Hello, @VR6Pete can you double check name of your SQL instances ? it seems that WMI hardcodes SQL instance name into WMI class query so exporter should be adopted to your specific case. I tried to install secondary instances on my test SQL server and find out that now I have two WMI classes:

PS Get-WmiObject -query "SELECT * FROM meta_class" | Select-String -Pattern "SQLStatistics"

Win32_PerfFormattedData_MSSQLMSSQLSERVER2_MSSQLMSSQLSERVER2SQLStatistics
Win32_PerfRawData_MSSQLMSSQLSERVER2_MSSQLMSSQLSERVER2SQLStatistics
Win32_PerfFormattedData_MSSQLSERVER_SQLServerSQLStatistics
Win32_PerfRawData_MSSQLSERVER_SQLServerSQLStatistics

My SQL instance names are MSSQLSERVER and MSSQLSERVER2

[pete-leese]

I’ll check this tomorrow but I can Gurantee that there will be around 10 instances per physical server and that none will exist with the default instance name

[pete-leese]

Yes I can confirm the same behavior from running that WMI query.

[szook]

Figured I'd comment that I'm also working on an mssql collector.

I got something put together yesterday that works for us, but is not a general enough solution that I don't think a PR is in order:
https://github.com/szook/wmi_exporter/commit/919e627c575ad5773329b8ec6a6469f8b0cc68cf

I'm thinking about a more general solution where we can programatically get a list of WMI classes and fields. If I can do that, iterating over multiple mssql server instances on a given box should be pretty trivial.

[carlpett]

Chiming in a bit from the sidelines here, if you go down this road, consider enumerating the classes on start-up rather than query time. I think it could add a non-trivial amount of runtime to the queries to do this on every scrape (although this does mean that you'll have to restart the exporter after installing a new instance).
You should be able to create a query similar to this SELECT * FROM meta_class WHERE __this ISA 'Win32_PerfRawData' AND Name LIKE "SQLServer%", I believe (don't have a Windows machine handy here to test).

[szook]

Swing... and a miss.

It seems that the return from the meta_class query is different enough (compared to other WMI queries) that the wmi.Query() function sees the "Name" field as an ole type "VT_NULL" rather than "VT_BSTR" and does not put it my struct.

PoC Code:

package main

import (
    "encoding/json"
    "fmt"
    "github.com/StackExchange/wmi"
)

type wmiMetaClass struct {
    Name           *string
}

func wmiToJson(q string) {
    var wmiMetaResults []wmiMetaClass
    if err := wmi.Query(q, &wmiMetaResults); err != nil {
        fmt.Println("failed collecting SQL Server:", err)
    }
    fmt.Println("Queried: "+q, len(wmiMetaResults))
    mrjson, _ := json.Marshal(wmiMetaResults)
    fmt.Println(string(mrjson))
}

func main()  {
    wmiToJson(`select * from Meta_Class WHERE __Class LIKE "Win32_PerfRawData_PerfProc_Process"`)
    // returns: [{"Name":""}]
    // same query via `Get-WmiObject -Query 'select...'` returns `Win32_PerfRawData_PerfProc_Process``

    wmiToJson(`select * from Win32_PerfRawData_PerfProc_Process where Name = "_Total"`)
    // returns: [{"Name":"_Total"}]
}

I spent the better part of the day digging into the internals of the github.com/StackExchange/wmi and github.com/go-ole/go-ole to try to figure out why one works and the other doesn't and I'm no closer to solving this little riddle.

I'm going to sleep on it, but I have a feeling trying to use the meta_class wmi query from with in golang might not work (at least not work as easily) as I originally hoped. I might need to abandon that route and think of another method.

[carlpett]

A much simpler method would of course be to use a flag to list the instances. I'd consider that sufficient as well, if the alternatives are too complex.

[szook]

That's my backup plan, but first I'm going to see if I can get that list out of the registry.

[szook]

I have something that should iterate over installed SQL Server instances, but the only SQL servers I have access to are v2016 using the default "MSSQLSERVER" instance so I haven't been able to test it extensively:
https://github.com/szook/wmi_exporter/tree/add-mssql-collector-take1

@VR6Pete: when you get a chance, can you give that a spin and see how that works for you?

[carlpett]

Since there have been two parallel efforts, it would be great to know if there's any non-overlapping parts between the two? @wizardmatas and @szook, did you both find the same classes etc in your work?

[szook]

It looks like @wizardmatas only implemented the Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics class thus far. Mine has that plus other classes.

since both of us used the collector-generator tool, our mutual code for that class looks pretty much identical :grin:

[wizardmatas]

hi, @carlpett and @szook yes indeed, I just tried to generate collector using collector-generator tool. So @szook please continue. I will test it on my test instances. Thanks!

[pete-leese]

I have literally just gone on paternity leave so will check when I’m back in the office in w couple of weeks 😀