How to get MSSQL Query results visible in Zabbix frontend

[vega2511]

Hello,

Recently we started monitoring with Zabbix version 2.2.1. We use Zabbix for the first time. We have an Microsoft SQL Server domain and I made already in Zabbix some nice views with SQL performance counters.
My question is can I make an SQL query and show that results in Zabbix?
What I did for example is to add an user parameter in the agent config file:

UserParameter = SQL_version,sqlcmd -d Master -U -h -1 -W -Q "select SUBSTRING(@@VERSION,10,46)"

What mus i do else in Zabbix to get the results of the above SQL query visible in Zabbix frontend?

I already created an Item bus then I do not see any results.

For us it's very important that we can make our own queries to check some special items.

Many thanks in advance if someone give me some advice how to.

Carlo

[aib]

DO you mind to show us the Item which you created for collecting information from yours UserParameter?

[vega2511]

DO you mind to show us the Item which you created for collecting information from yours UserParameter?

Hello,

I've changed the user parameter a bit not it looks like:
UserParameter = get.sqlversion,sqlcmd -d Master -U ##SQL_USER## -P ##SQL_PASSWORD## -h -1 -W -Q "select substring(@@VERSION,10,46)"

The item I created is
get.sqlversion["sqlversion"]
type of information = text

[aib]

Hello,

I've changed the user parameter a bit not it looks like:
UserParameter = get.sqlversion,sqlcmd -d Master -U sa -P Tdpm1UeExlY -h -1 -W -Q "select substring(@@VERSION,10,46)"

The item I created is
get.sqlversion["sqlversion"]
type of information = text

1) If you created UserParameter without parameters, you have to configure you Item also without any parameters

Item key:get.sqlversion

2) To be on safe side, use the full path in UserParameter

UserParameter = get.sqlversion,/usr/sbin/sqlcmd -d Master -U sa -P Tdpm1UeExlY -h -1 -W -Q "select substring(@@VERSION,10,46)"

3) TO check if you have any errors, increase DebugLevel on agent side, restart agent, and check log file

DebugLevel=4

[vega2511]

Hello,

I followed your steps
here is an output form the log file.
path is not specified.

29188:20140313:165126.349 Requested [get.sqlversion]
29188:20140313:165126.381 Run remote command [/usr/sbin/sqlcmd -d Master -U ##SQL_USER## -P ##SQL_PASSWORD## -h -1 -W -Q "select substring(@@VERSION,10,46)"] Result [42] [The system cannot fi]...
29188:20140313:165126.381 Sending back [The system cannot find the path specified.]

[aib]

Hello,

I followed your steps
here is an output form the log file.
path is not specified.

29188:20140313:165126.349 Requested [get.sqlversion]
29188:20140313:165126.381 Run remote command [/usr/sbin/sqlcmd -d Master -U sa -P Tdpm1UeExlY -h -1 -W -Q "select substring(@@VERSION,10,46)"] Result [42] [The system cannot fi]...
29188:20140313:165126.381 Sending back [The system cannot find the path specified.]

Well, I gave you the path just for example
You have to find the correct path for YOUR system

#whereis sqlcmd

[vega2511]

Hello sorry for the late reply,

Below the output of the logfile (user and password are deleted )
The query is working now.
Run remote command ["C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -d Master -U ##SQL_USER## -P ##SQL_PASSWORD## -h -1 -W -Q "select substring(@@version,10,46)"] Result [67] [ SQL Server 2008 R2 ]...
28884:20140318:161736.989 Sending back [ SQL Server 2008 R2 (SP1) - 10.50.2789.0 (X64)
(1 rows affected)]

I still don't get it visible in the frontend.
I add item get.sqlversion["sqlversion"]
type of information is text.

What do I miss to get it right?

[aib]

Hello sorry for the late reply,

Below the output of the logfile (user and password are deleted )
The query is working now.
Run remote command ["C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -d Master -U ##SQL_USER## -P ##SQL_PASSWORD## -h -1 -W -Q "select substring(@@version,10,46)"] Result [67] [ SQL Server 2008 R2 ]...
28884:20140318:161736.989 Sending back [ SQL Server 2008 R2 (SP1) - 10.50.2789.0 (X64)
(1 rows affected)]

I still don't get it visible in the frontend.
I add item get.sqlversion["sqlversion"]
type of information is text.

What do I miss to get it right?

Good for you that your script is working now!
But I want to ask you again - why you create two(2) different keys:
- get.sqlversion["sqlversion"] in Item Keyname (frontend)
- get.sqlversion in UserParameter (zabbix_agent.conf)

Do you see the difference - one has a parameter, second one doesn't have any parameters.

Be consistent - use the same Keyname in both cases. The easiest way is to remove any parameters from Item Keyname.

[vega2511]

Hi,

I followed all your instruction but still the result is not visible in the frontend.
I'm getting every performance counter working in the frontend but self made queries pff.

agent configfile parameter.
UserParameter = get.sqlversion,"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -d Master -U ##SQL_USER## -P ##SQL_PASSWORD## -h -1 -W -Q "select substring(@@version,10,46)"

Output logfile:
Run remote command ["C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -d Master -U ##SQL_USER## -P ##SQL_PASSWORD## -h -1 -W -Q "select substring(@@version,10,46)"] Result [67] [ SQL Server 2008 R2 ]...
27960:20140319:162516.129 Sending back [ SQL Server 2008 R2 (SP1) - 10.50.2789.0 (X64)

in the frontend the item key:
item key: get.sqlversion (I've also tried sqlversion)
type of information is text

But I'm receiving no results.
Any suggestions?

[aib]

show, please, the result of zabbix_get command

[zabbix@server]# zabbix_get -s {IP_OF_MSSQL_SERVER} -k get.sqlversion

[vega2511]

Hi,

I executed your command on the SQL Server.
Below the output:
zabbix_get [29008]: Get value error: ZBX_TCP_READ() failed: [0x00002746]
An existing connection was forcibly closed by the remote host.

I also enabled 'EnableRemoteCommands' in the zabbix_agentd.conf and restarted the zabbix agent service.

Is it an firewall issue?

[aib]

Looks like Firewall or SELinux is existing somewhere on the road...

[aib]

Hi,

I executed your command on the SQL Server.
Below the output:
zabbix_get [29008]: Get value error: ZBX_TCP_READ() failed: [0x00002746]
An existing connection was forcibly closed by the remote host.

I also enabled 'EnableRemoteCommands' in the zabbix_agentd.conf and restarted the zabbix agent service.

Is it an firewall issue?

Sorry for misunderstanding. I meant that you have to run this command on Zabbix Server to request some data from Zabbix Client (where your MSSQL server installed).
Also do you mind to show the screenshot of Item get.sqlversion in Attached file (when you create a message you can attach files)?

[vega2511]

Hi,

enclosed the requested screenshot.
someone else installed the zabbix server so I sent him the command to run from the zabbix server.

I also sent you a private message.

[aib]

Thank you for screenshot!
Do you know why your information is not saving?

History Storage Period(days): 0

And the next request :
- Do you have any green/red mark next to the Item Name in the list of items of the host?
- Do you mind to show Latest Data about this Host and Item?