MSSQL operations on zabbix

I. Introduction to Zabbix

1.1 Zabbix Introduction

Official Website: http://www.zabbix.com. Zabbix collects data in C/S mode, and displays and configures data on the web end in B/S mode.

1.2 reference resources

Enterprise Application monitoring tool-ZABBIX including zabbix installation method, Chinese font settings and firewall settings, etc.) http://waringid.blog.51cto.com/65148/904201

Zabbix Chinese user manual includes important zabbix monitoring parameters, significance of important monitoring charts, etc.) http://waringid.blog.51cto.com/65148/945352

Zabbix Chinese Configuration Guide includes customization of item parameters, definition of windows monitoring parameters, and OID query and setting of snmp devices. Zabbix user manual focuses on users, which focuses on professional management) http://waringid.blog.51cto.com/65148/955939

Zabbix Chinese Configuration Guide time synchronization, windows, linux, ciscoIOS settings and cisco 2960 network device monitoring) http://waringid.blog.51cto.com/65148/1104627

Zabbix auth login mail alarm by making msmtp zabbix verified mail alarm notification function) http://waringid.blog.51cto.com/65148/1142579

Monitor MySQL with Zabbix

Http://www.badllama.com/content/monitor-mysql-zabbix

Monitor Apache with Zabbix

Http://www.badllama.com/content/monitor-apache-zabbix

Monitor Nginx with Zabbix

Http://www.badllama.com/content/monitor-nginx-zabbix

Partitioning Tables on Zabbix 1.8

Http://zabbixzone.com/zabbix/partitioning-tables/

Heavy MySQL monitoring solution

Https://www.zabbix.com/wiki/howto/monitor/db/mysql/extensive_mysql_monitoring_including_replication

Ii. Problems with zabbix

2.1 Why MSSQL operations

Zabbix's powerful functions do not need to be emphasized too much, and can be well supported for server hardware performance and devices that support the SNMP protocol. With the increase of business complexity, pure performance monitoring is no longer able to meet the actual needs. We hope to increase the monitoring of business data in many aspects. It has the following benefits for business monitoring:

1. The system can be applied from the business perspective to improve the importance of the monitoring system. At the same time, it also provides support for business departments for the later budget ).

2. Performance data combined with business data can better reflect the importance of monitoring and play a good auxiliary role in evaluating the architecture of business systems.

3. Reduce the blind spots of each node on business data to make business data clearer, streamlined, and automated. Reduce manual operations and requirements for professional skills such as SQL operations.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21G447-0.jpg "title =" "/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21IB9-1.png "title =" image001.png "/>

2.2 Problems

Business data exists in the backend MSSQL2008. A large number of conditional statements are required to obtain the required value only through the query statement ). At first, we plan to use "UserParameter" in the zabbix client configuration file to query the database. The following problems are found in the experiment:

1. Software other than the zabbix client cannot be installed on Windows servers. Therefore, it can only be implemented through the built-in commands, scripts, or green software.

Ii. "UserParameter" has limited support for system commands. After testing, as long as the command or script contains ". ,), {} "And other special characters. If the entire command fails to be executed normally, half of the command file is executed.) or direct return is not supported.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21I418-2.png "title =" image002.png "/>

3. "UserParameter" combined with the "osql, sqlcmd" command to query complex query conditions or directly return unsupported results if the second point exists.) All the results are string-type bytes, windows does not have tools like grep and awk), and zabbix cannot be displayed graphically.

4. using automated tools such as autoit3 to integrate SQL query commands, "UserParameter" cannot return normal query results. It is okay to directly execute the compiled program ).

Iii. solution-pymssql

The problem in 2.2 is that it is difficult to directly obtain MSSQL data in windwos or it is costly to implement it). So the alternative method is as follows: can you directly operate MSSQL in linux? At least shell commands in linux are more powerful than those in windows.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21IQ2-3.png "title =" image003.png "/>

As a result, pymssql emerged and can be used to operate the mssql database using python. Http://code.google.com/p/pymssql /. The pymssql component can run in a variety of operating systems: windows, linux, MACos, And FreeBSD. It supports operations on databases including SQL2000, SQL2005, and SQL2008.

3.1 Environment

MSSQL0.172)

The Zabbix client is a 32-bit operating system of win2003 R2 Enterprise Edition. It is installed with the SQL2008 Enterprise Edition database.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21K516-4.png "title =" image004.png "/>

Zabbix_Server0.189)

Zabbix server, CentOS 6.3 64-bit operating system, Zabbix2.05 ..

Zabbix_Agent0.47)

Zabbix client, CentOS 6.3 64-bit operating system, Zabbix2.05.

4. Install the freedts package in pymssql4.1

Freedts is mainly used to directly operate the MSSQL database. It can be automatically installed by compiling or using the yum command. For convenience, the yum method is used for installation. Before using the yum command, we recommend that you set the linux software source as shown in, refer to the http://waringid.blog.51cto.com/65148/904201 ). Note that the epel-release-6-7.noarch.rpm in the epel source has been replaced with the epel-release-6-8.noarch.rpm version, while note the differences between the 32-bit and 64-bit versions ).

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21H4c-5.png "title =" image005.png "/>

yum install freetdsrpm –qa|grep freetds

4.2 Test freetds

After freedts is installed, you can use the tsql command to view the version and setting status, as shown in. Note the storage path and version status of the freetds. conf configuration file.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21I5c-6.png "title =" image006.png "/>

The version of freedts is related to the database version of the operation, when using the tsql command to operate MSSQL errors, you need to check if the TDS version is consistent with the http://www.freetds.org/userguide/choosingtdsprotocol.htm ).

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21M9E-7.png "title =" image007.png "/>

tsql -H 192.168.0.172 -p 1433 -U saTDSVER=7.1 tsql –S 192.168.0.172 –p 1433 –U sa

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21K552-8.png "title =" image008.png "/>

The freetds. conf configuration file of freedts can be set to connect to the SQL server. After setting, you can access the configuration by using the machine name, as shown in.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21G116-9.png "title =" image009.png "/>

4.3 install setuptools

Setuptools is a sub-project of Python Enterprise Application KitPEAK. It is an enhanced tool for a group of Python distutilsde tools (applicable to Python 2.3.5 and later versions, the 64-bit platform is applicable to Python 2.4 and later versions. This allows programmers to easily create and release Python packages, especially those that are dependent on other packages.

wget http://pypi.python.org/packages/source/s/setuptools/setuptools-0.7.2.tar.gztar zxvf setuptools-0.7.2.tar.gzpython setup.py buildpython setup.py install

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21I359-10.png "title =" image010.png "/>

4.4 install Cython

Cython is a separate language used to write the extension library used for import in Python. In fact, the syntax of Cython is basically the same as that of Python, while Cython has a special "compiler" that converts Cython code into C and automatically adds a lot of C-Python APIs ), then, use the C compiler to compile the final Python callable module.

wget http://www.cython.org/release/Cython-0.19.1.tar.gztar zxvf Cython-0.19.1.tar.gzpython setup.py buildpython setup.py install

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21K1M-11.png "title =" image011.png "/>

4.5 install pymssql

wget http://pymssql.googlecode.com/files/pymssql-2.0.0b1-dev-20111019.tar.gztar zxvf pymssql-2.0.0b1-dev-20111019.tar.gzpython setup.py buildpython setup.py install

4.6 use python to operate MSSQL

First, create a python file. For details about the content below, refer to idea .)

vim testmssql.py

# Coding = UTF-8 #! /Usr/bin/env python # ----------------------------------------------------------------------------- # Name: pymssqlTest. py # Purpose: test the pymssql library # Created: 04/02/2012 # Export import pymssqlclass MSSQL: "when you use this library for simple encapsulation of pymssql, you need to enable the TCP/IP protocol in SQL Server Configuration Manager: "def _ init _ (self, host, user, pwd, db): self. host = hostself. user = userself. pwd = pwdself. db = dbdef _ GetConnect (self): "Returned connection information: conn. cursor () "" if not self. db: raise (NameError, "no database information set") self. conn = pymssql. connect (host = self. host, user = self. user, password = self. pwd, database = self. db, charset = "utf8") cur = self. conn. cursor () if not cur: raise (NameError, "failed to connect to Database") else: return curdef ExecQuery (self, SQL ): "execution of the query statement returns a list containing tuple. The list element is the record row, and the tuple element is the example of calling the fields recorded in each row: ms = MSSQL (host = "localhost", user = "sa", pwd = "123456", db = "PythonWeiboStatistics") resList = ms. execQuery ("SELECT id, NickName FROM WeiBoUser") for (id, NickName) in resList: print str (id), NickName "" cur = self.w.getconnect(w.cur.exe cute (SQL) resList = cur. fetchall () # The connection to self must be closed after the query is complete. conn. close () return resListdef ExecNonQuery (self, SQL): "example of executing a non-query statement call: cur = self.20.getconnect()cur.exe cute (SQL) self. conn. commit () self. conn. close () "" cur = self.w.getconnect(+cur.exe cute (SQL) self. conn. commit () self. conn. close () def main (): # MS = MSSQL (host = "localhost", user = "sa", pwd = "123456", db = "PythonWeiboStatistics ") ### the returned result is a list containing tuple. The list element is the record row, and the tuple element is the record field in each row ## ms. execNonQuery ("insert into WeiBoUser values ('2', '3')") MS = MSSQL (host = "192.168.0.172", user = "sa ", pwd = "P @ $ w0rd", db = "master") resList = ms. execQuery ("SELECTcpu_busy, io_busy FROM spt_monitor") for (cpu_busy, io_busy) in resList: printstr (cpu_busy ). decode ("utf8") resList = ms. execQuery ("SELECT * FROMspt_monitor") for r in resList: print str (r ). decode ("utf8") if _ name _ = '_ main _': main ()

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21K4Z-12.png "title =" image012.png "/>

4.7 Zabbix settings

First, set the "UserParameter" parameter in the configuration file of the zabbix client. You can use the following command by default)

vim /usr/local/etc/zabbix_agentd.conf

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21II0-13.png "title =" image013.png "/>

chown zabbix:zabbix /tmp/testsql.pyservice zabbix_agentd restart

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21K120-14.png "title =" image014.png "/>

4.7 Zabbix obtain mssql data

The following operations are performed on the zabbix Server.

/usr/local/zabbix/bin/zabbix_get -s 192.168.0.47 -p 10050 -k"system.test"

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131227/1G21G162-15.png "title =" image015.png "/>

This article is from the "virtual reality" blog. For more information, contact the author!