ZenPacks : MS SQL Database Monitor : Comments

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 19 Jan 2010 21:01:50 GMT

Is MS SQL Server 2008 supported?

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 12 Mar 2010 20:39:59 GMT

I restarted after I installed the zenpack and I installed the requisite zenpacks in proper order,

however anytime I try to test a query from the template, i simply get nothing back, not even an

error message? I checked the zenperfodbc log, but it also shows nothing out of the ordinary?
Anybody have any ideas?

-m

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 24 Aug 2010 07:26:20 GMT

Hi,

I've just install this zenpack but I cannot see MsSql80Database and MsSql90Database when I try to bind templates, please could you help?

Thanks!!!

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 24 Aug 2010 18:46:49 GMT

Hi francisco.rossi@partech.it,

don't try to bind this templates to device. Simply add community.odbc.MsSqlDatabaseMap plugin to zCollectorPlugins list and remodel your device. On Software tab you can find Databases and after 15 mins (3 collector cycles) you will see performance data.

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 30 Sep 2010 09:23:11 GMT

Hello,

after spending 2 days on making it work, here is how I did it on Debian (works with Ubuntu) x64 :

Install dependencies : apt-get install unixodbc libmyodbc unixodbc-dev freetds-dev sqsh tdsodbc
Configure conf files as follow :

/etc/odbcinst.ini :

[FreeTDS]

Description = TDS driver (Sybase/MS SQL)

Driver = /usr/lib/odbc/libtdsodbc.so

Setup = /usr/lib/odbc/libtdsS.so

FileUsage = 1

CPTimeout =

CPReuse =

Trace = No

TDS_Version = 7.0

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

CPTimeout =

CPReuse =

Install ZenPack on the following order :

ZenPacks.community.deviceAdvDetail
ZenPacks.community.ZenODBC
ZenPacks.community.SQLDataSource
ZenPacks.community.RDBMS
ZenPacks.community.MsSQLMon_ODBC

Assign the modeler on the devices

It works !

I used the latest version and it works fine with 2008.

Thanks a lot bigegor !!

Regards,

Richard

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 30 Sep 2010 13:52:21 GMT

Works fine with 2008 and 2005 on my setup.

Doesn't work with 2000.

Regards,

Richard

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 30 Sep 2010 14:29:52 GMT

Hello Richard,

Thanks for pointing that out. I've add this to Known issues. I don't have SQL 2000 in my environment, and i can not test this ZenPack against SQL 2000. But if someone can reimplement SQL queries from this ZenPack in SQL 200X compatible style, thats would be realy nice.

Requirements:

the same query for MS SQL 2000, 2005 and 2008
results must contains all components in one table with single WHERE statement at the end of query.

This needed by SQLDataSource query optimization to work (o.e. this ZenPack sent only 3 SQL queries per SQL Server Instance to fetch performance data for Server Instance and all Databases)

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 30 Sep 2010 14:54:47 GMT

Hello Egor,

we have 75% SQL 2000 server for the moment. I'm looking at it to find cross compatible queries!

I'll send them to you if I manage to do it.

The modeler requests are fine. Only the performance requests doesn't work with 2000.

Again, very great work!!!

Regards,

Richard

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 11 Oct 2010 14:20:33 GMT

I am running Zenoss 2.5.2 with the following (applicable) ZenPacks / Versions:

ZenPacks.community.MsSQLMon_ODBC community Egor Puzanov 3.1

ZenPacks.community.RDBMS community Egor Puzanov 2.0

ZenPacks.community.SQLDataSource community Egor Puzanov 1.3

ZenPacks.community.ZenODBC community Egor Puzanov 3.2

I have confirmed that FreeTDS is functioning as I have executed tsql statements from the Zenoss box.

I have associated the community.odbc.MsSqlDatabaseMap Collector Plugin to a host and, when I go to the Software tab on that host, I see the databases listed however the Total Bytes, Used Bytes and Free Bytes are all 0.0B and the % Util is "Unknown". When I drill into any database, there is no performance data, Size allocated is 0.0B...

This is a SQL 2008 server on Windows 2008 (64-bit).

Any ideas?

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 11 Oct 2010 17:02:33 GMT

wait 15 min (3 performance collecting cycles)

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 11 Oct 2010 17:12:46 GMT

It has been overnight.

Any other ideas?

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 11 Oct 2010 17:30:49 GMT

check zenperfsql log.

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 11 Oct 2010 17:32:47 GMT

Polling is 5 minutes:

2010-10-11 12:24:00,261 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 12:24:00,261 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0
2010-10-11 12:24:00,263 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 12:24:00,263 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0
2010-10-11 12:29:00,284 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 12:29:00,284 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0
2010-10-11 12:29:00,285 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 12:29:00,286 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 11 Oct 2010 17:39:29 GMT

check zenping log

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 11 Oct 2010 18:52:23 GMT

OK...was having other issues (this is a dev instance of Zenoss). I restarted and re-modeled the device. Now I see:

2010-10-11 13:18:24,176 ERROR zen.zencommand: [Failure instance: Traceback (failure with no frames): twisted.internet.error.ConnectionDone: Connection was closed cleanly.

]
Traceback (most recent call last):
  File "/usr/local/zenoss/zenoss/Products/ZenHub/PBDaemon.py", line 379, in pushEvents
    driver.next()
  File "/usr/local/zenoss/zenoss/Products/ZenUtils/Driver.py", line 64, in result
    raise ex
PBConnectionLost: [Failure instance: Traceback (failure with no frames): twisted.internet.error.ConnectionDone: Connection was closed cleanly.
]
2010-10-11 13:22:28,019 INFO zen.zencommand: Connected to ZenHub
2010-10-11 13:23:45,589 INFO zen.zencommand: Deleting PID file /usr/local/zenoss/zenoss/var/zencommand-localhost.pid ...
2010-10-11 13:23:45,590 INFO zen.zencommand: Daemon zencommand shutting down
2010-10-11 13:24:05,073 INFO zen.zencommand: Connecting to localhost:8789
2010-10-11 13:24:05,078 INFO zen.zencommand: Connected to ZenHub

2010-10-11 13:42:29,330 INFO zen.ZenHub: Worker reports 2010-10-11 13:42:29,329 ERROR zen.Events: (1205, 'Lock wait timeout exceeded; try restarting transaction')

2010-10-11 13:42:29,330 INFO zen.ZenHub: Worker reports 2010-10-11 13:42:29,330 ERROR zen.Events: (1205, 'Lock wait timeout exceeded; try restarting transaction')
Traceback (most recent call last):
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/MySqlSendEvent.py", line 146, in sendEvent
    evid = self.doSendEvent(event)
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/MySqlSendEvent.py", line 199, in doSendEvent
    rescount = execute(curs, stmt)
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/MySqlSendEvent.py", line 51, in execute
    raise ex
OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

2010-10-11 13:42:29,330 INFO zen.ZenHub: Worker reports 2010-10-11 13:42:29,330 ERROR zen.Events: (1205, 'Lock wait timeout exceeded; try restarting transaction')
Traceback (most recent call last):
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/EventManagerBase.py", line 1387, in sendEvents
    self.sendEvent(event)
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/MySqlSendEvent.py", line 151, in sendEvent
    raise ZenBackendFailure(str(e))
ZenBackendFailure: (1205, 'Lock wait timeout exceeded; try restarting transaction')


2010-10-11 13:43:20,339 INFO zen.ZenHub: Worker reports 2010-10-11 13:43:20,339 ERROR zen.Events: (1205, 'Lock wait timeout exceeded; try restarting transaction')

2010-10-11 13:43:20,339 INFO zen.ZenHub: Worker reports 2010-10-11 13:43:20,339 ERROR zen.Events: (1205, 'Lock wait timeout exceeded; try restarting transaction')
Traceback (most recent call last):
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/MySqlSendEvent.py", line 146, in sendEvent
    evid = self.doSendEvent(event)
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/MySqlSendEvent.py", line 199, in doSendEvent
    rescount = execute(curs, stmt)
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/MySqlSendEvent.py", line 51, in execute
    raise ex
OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

2010-10-11 13:43:20,339 INFO zen.ZenHub: Worker reports 2010-10-11 13:43:20,339 ERROR zen.Events: (1205, 'Lock wait timeout exceeded; try restarting transaction')
Traceback (most recent call last):
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/EventManagerBase.py", line 1387, in sendEvents
    self.sendEvent(event)
  File "/usr/local/zenoss/zenoss/Products/ZenEvents/MySqlSendEvent.py", line 151, in sendEvent
    raise ZenBackendFailure(str(e))
ZenBackendFailure: (1205, 'Lock wait timeout exceeded; try restarting transaction')

2010-10-11 13:18:24,177 ERROR zen.zenperfsql: [Failure instance: Traceback (failure with no frames): twisted.internet.error.ConnectionDone: Connection was closed cleanly.
]
Traceback (most recent call last):
  File "/usr/local/zenoss/zenoss/Products/ZenHub/PBDaemon.py", line 379, in pushEvents
    driver.next()
  File "/usr/local/zenoss/zenoss/Products/ZenUtils/Driver.py", line 64, in result
    raise ex
PBConnectionLost: [Failure instance: Traceback (failure with no frames): twisted.internet.error.ConnectionDone: Connection was closed cleanly.
]
2010-10-11 13:18:24,179 ERROR zen.zenperfsql: Maintenance failed: 'NoneType' object has no attribute 'dataPoints'
2010-10-11 13:21:56,359 INFO zen.zenperfsql: Connected to ZenHub
2010-10-11 13:22:53,757 ERROR zen.zenperfsql: Maintenance failed: 'NoneType' object has no attribute 'dataPoints'
2010-10-11 13:23:24,182 ERROR zen.zenperfsql: Maintenance failed: 'NoneType' object has no attribute 'dataPoints'
2010-10-11 13:23:45,428 INFO zen.zenperfsql: Deleting PID file /usr/local/zenoss/zenoss/var/zenperfsql-localhost.pid ...
2010-10-11 13:23:45,429 INFO zen.zenperfsql: Daemon CollectorDaemon shutting down
2010-10-11 13:24:13,522 INFO zen.zenperfsql: Connecting to localhost:8789
2010-10-11 13:24:13,529 INFO zen.zenperfsql: Connected to ZenHub
2010-10-11 13:25:12,897 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 13:25:12,898 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0
2010-10-11 13:30:13,070 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 13:30:13,070 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0
2010-10-11 13:36:59,599 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 13:36:59,600 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0
2010-10-11 13:41:59,607 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 13:41:59,607 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0
2010-10-11 13:47:03,287 INFO zen.zenperfsql: 0 devices processed (0 datapoints)
2010-10-11 13:47:03,287 INFO zen.collector.scheduler: Tasks: 0 Successful: 0 Failed: 0 Missed: 0

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 11 Oct 2010 19:01:36 GMT

I think, you still have a problem with zenhub.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 17 Nov 2010 22:11:45 GMT

When I model my device I'm getting this:

2010-11-17 17:06:34,633 ERROR zen.SQLClient: Received error (('00000', '[ISQL]ERROR: Could not SQLConnect')) from query: sp_helpdb

2010-11-17 17:06:34,662 ERROR zen.SQLClient: Received error ([Errno 32] Broken pipe) from query: SELECT CONVERT(Char(128), SERVERPROPERTY('InstanceName')) AS InstanceName,

CONVERT(Char(128), SERVERPROPERTY('Edition')) AS Edition,

CONVERT(Char(128), SERVERPROPERTY('LicenseType')) AS LicenseType,

CONVERT(Int, SERVERPROPERTY('NumLicenses')) AS NumLicenses,

CONVERT(Int, SERVERPROPERTY('ProcessID')) AS ProcessID,

CONVERT(Char(128), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(Char(128), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

RTRIM((CASE WHEN SERVERPROPERTY('isClustered') = 1 THEN 'isClustered ' ELSE '' END) + (CASE WHEN SERVERPROPERTY('IsFullTextInstalled') = 1 THEN 'IsFullTextInstalled ' ELSE '' END) + (CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'IsIntegratedSecurityOnly ' ELSE '' END) + (CASE WHEN SERVERPROPERTY('IsSingleUser') = 1 THEN 'IsSingleUser ' ELSE '' END)) AS dbsiproperties,

@@version AS Version

2010-11-17 17:06:34,664 INFO zen.PythonClient: Python client finished collection for aco01.rs-us.com

2010-11-17 17:06:34,665 ERROR zen.ZenModeler: Received error (('00000', '[ISQL]ERROR: Could not SQLConnect')) from query: sp_helpdb

2010-11-17 17:06:34,665 ERROR zen.ZenModeler: Received error ([Errno 32] Broken pipe) from query: SELECT CONVERT(Char(128), SERVERPROPERTY('InstanceName')) AS InstanceName,

CONVERT(Char(128), SERVERPROPERTY('Edition')) AS Edition,

CONVERT(Char(128), SERVERPROPERTY('LicenseType')) AS LicenseType,

CONVERT(Int, SERVERPROPERTY('NumLicenses')) AS NumLicenses,

CONVERT(Int, SERVERPROPERTY('ProcessID')) AS ProcessID,

CONVERT(Char(128), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(Char(128), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

@@version AS Version

Any thoughts?

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Nov 2010 16:07:00 GMT

Hi T_R_J,

for debugging your connection problem:

1. as root user, rename /usr/bin/isql to /usr/bin/isql.bin

2. create bash script /usr/bin/isql

#!/bin/sh
echo $1 $2 $3 > /tmp/isqlauth.log
/usr/bin/isql.bin $1 $2 $3 -c -w -b

3. set execution permisions to this file

4. restart zenoss

5. check /isqlauth.log file for used DSN name (DSNs you can find in /home/zenoss/.odbc.ini), username and password

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Nov 2010 20:07:00 GMT

Hi Egor,

Thanks for getting back to me. Here is what I see in the isqlauth.log

4b83a10c95a28ebec3ebe93902ee6885 <user> <password>

Here is what I see in my modeling now:

2010-11-18 15:02:06,643 ERROR zen.SQLClient: Received error (('00000', '[ISQL]ERROR: Could not SQLConnect')) from query: sp_helpdb

2010-11-18 15:02:06,820 ERROR zen.SQLClient: Received error ([Errno 32] Broken pipe) from query: SELECT CONVERT(Char(128), SERVERPROPERTY('InstanceName')) AS InstanceName,

CONVERT(Char(128), SERVERPROPERTY('Edition')) AS Edition,

CONVERT(Char(128), SERVERPROPERTY('LicenseType')) AS LicenseType,

CONVERT(Int, SERVERPROPERTY('NumLicenses')) AS NumLicenses,

CONVERT(Int, SERVERPROPERTY('ProcessID')) AS ProcessID,

CONVERT(Char(128), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(Char(128), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

@@version AS Version

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Nov 2010 21:25:41 GMT

Hi T_R_J,

can you connect to you SQL server with isql?

as zenoss user:

isql 4b83a10c95a28ebec3ebe93902ee6885 username password

if no:

1. in /home/zenoss/.odbc.ini file check section [4b83a10c95a28ebec3ebe93902ee6885]

2. check if you user has rights to make connection with SQL server (windows authentication with DOMAIN\username is preferred)

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 00:29:12 GMT

Hi Egor,

[ISQL]ERROR: Could not SQLConnect is the error I receieve and the user does have access to connect to the server and specific access to the master db. Is there a way to manually connect using isql without using the DSN name?

Ideas?

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 00:43:20 GMT

isql --help

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 05:01:32 GMT

If you are using domain\username format on the command line you might have to do:

isql 4b83a10c95a28ebec3ebe93902ee6885 DOMAIN\\username password

(note the double '\')

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 05:04:25 GMT

I'm having a similar problem. When I model a device it finds the database names and instance (this particular server is SQL Server 2005 with only the default MSSQLSERVER instance). After the model the I get this:

localhost|ZenPacks.community.SQLDataSource.services.SqlPerfConfig.SqlPerfConfig|||5|Unhandled exception in zenhub service ZenPacks.community.SQLDataSource.services.SqlPerfConfig.SqlPerfConfig: \'dbname\'

Traceback (most recent call last):

File "/opt/zenoss/Products/ZenCollector/services/config.py", line 84, in _wrapFunction

return functor(*args, **kwargs)

File "/opt/zenoss/ZenPacks/ZenPacks.community.SQLDataSource-1.3.egg/ZenPacks/community/SQLDataSource/services/SqlPerfConfig.py", line 112, in _createDeviceProxy

device)

File "/opt/zenoss/ZenPacks/ZenPacks.community.SQLDataSource-1.3.egg/ZenPacks/community/SQLDataSource/services/SqlPerfConfig.py", line 95, in getSqlDeviceConfig

threshs = getSqlComponentConfig(device, queries, datapoints)

File "/opt/zenoss/ZenPacks/ZenPacks.community.SQLDataSource-1.3.egg/ZenPacks/community/SQLDataSource/services/SqlPerfConfig.py", line 82, in getSqlComponentConfig

queries = sortQuery(queries, qid, ds.getQueryInfo(comp)+(columns,))

File "/opt/zenoss/ZenPacks/ZenPacks.community.SQLDataSource-1.3.egg/ZenPacks/community/SQLDataSource/datasources/SQLDataSource.py", line 126, in getQueryInfo

', '.join(self.sqlkb.values())).split(', ')))

File "/opt/zenoss/Products/ZenModel/RRDDataSource.py", line 206, in getCommand

res = compiled(getEngine().getContext(environ))

File "/opt/zenoss/lib/python/zope/tales/expressions.py", line 261, in __call__

v = var(econtext)

File "/opt/zenoss/lib/python/zope/tales/expressions.py", line 217, in __call__

return self._eval(econtext)

File "/opt/zenoss/lib/python/Products/PageTemplates/Expressions.py", line 127, in _eval

ob = self._subexprs[-1](econtext)

File "/opt/zenoss/lib/python/zope/tales/expressions.py", line 124, in _eval

ob = self._traverser(ob, element, econtext)

File "/opt/zenoss/lib/python/Products/PageTemplates/Expressions.py", line 76, in boboAwareZopeTraverse

object = object.restrictedTraverse(name)

File "/opt/zenoss/lib/python/OFS/Traversable.py", line 310, in restrictedTraverse

return self.unrestrictedTraverse(path, default, restricted=True)

File "/opt/zenoss/lib/python/OFS/Traversable.py", line 293, in unrestrictedTraverse

raise e

KeyError: 'dbname'

I can't seem to figure out how to resolve this. No data is obtained from the database other then the names of the databases.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 12:55:35 GMT

I got past that stack trace by modifying the sysperfinfo on MsSQLDatabase. I changed the last line from

WHERE instance_name = '${here/dbname}'

WHERE instance_name = '${here/zMsSqlSrvInstances}'

However, if I set the zMSSQLSrvInstances to 'MSSQLSERVER' and try to model the device I get this:

2010-11-19 07:54:05,836 ERROR zen.ZenModeler: Received error ([Errno 32] Broken pipe) from query: SELECT CONVERT(Char(128), SERVERPROPERTY('InstanceName')) AS InstanceName,

CONVERT(Char(128), SERVERPROPERTY('Edition')) AS Edition,

CONVERT(Char(128), SERVERPROPERTY('LicenseType')) AS LicenseType,

CONVERT(Int, SERVERPROPERTY('NumLicenses')) AS NumLicenses,

CONVERT(Int, SERVERPROPERTY('ProcessID')) AS ProcessID,

CONVERT(Char(128), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(Char(128), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

@@version AS Version

If I clear out the zMsSQLServerInstances and remodel I do not get any errors. I will try to find a server that has multiple instances and just see if this is a problem with single instance server or not.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 17:14:19 GMT

isql --help produces this:

unixODBC 2.2.11

if I man isql it doesn't look like I can manually define a connection.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 18:47:18 GMT

ok T_R_J, i do it for you...

[zenoss@localhost ~]# isql

**********************************************

* unixODBC - isql                            *

**********************************************

* Syntax                                     *

*                                            *

*      isql DSN [UID [PWD]] [options]        *

*                                            *

* Options                                    *

*                                            *

* -b         batch.(no prompting etc)        *

* -dx        delimit columns with x          *

* -x0xXX     delimit columns with XX, where *

*            x is in hex, ie 0x09 is tab     *

* -w         wrap results in an HTML table   *

* -c         column names on first row.      *

*            (only used when -d)             *

* -mn        limit column display width to n *

* -v         verbose.                        *

* -lx        set locale to x                 *

* --version version                         *

*                                            *

* Notes                                      *

*                                            *

*      isql supports redirection and piping *

*      for batch processing.                 *

*                                            *

* Examples                                   *

*                                            *

*      cat My.sql | isql WebDB MyID MyPWD -w *

*                                            *

*      Each line in My.sql must contain      *

*      exactly 1 SQL command except for the *

*      last line which must be blank.        *

*                                            *

* Please visit;                              *

*                                            *

*      http://www.unixodbc.org               *

*      pharvey@codebydesign.com              *

*      nick@easysoft.com                     *

**********************************************

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 19:02:50 GMT

Hi Egor,

I think you misunderstood me. I know how to get the syntax for the command. My concern was how to use isql and manually define a connection without using a preconfigured DSN from the odbc.ini file. Right now I can enter any information in the command and get the same output. It doesn't seem to either be reading the odbc.ini file or even trying to connect to the SQL server.

Example:

correct syntax: isql 0f664b98c9a3b4f9abef92b76f2ee143 <user> <pass>

Result: [ISQL]ERROR: Could not SQLConnect

bogus syntax: isql thisisnotadsn fakeuser fakepass

Result: [ISQL]ERROR: Could not SQLConnect

I would assume (my fault for assuming) that if the syntax was bogus it would tell me it couldn't find the DSN entry in my .odbc.ini file, yet it does not. This is why I wanted to attempt to manually connect using isql and skip the odbc file entirely. It seems from the manpage that this is not possible.

Thanks,

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 19:20:22 GMT

Hi T_R_J,

yes, isql need DSN.

so you have to check 3 things.

credentials and it permisions on SQL server. (tested and works with Windows authentication and DOMAIN user)
FreeTDS configuration
1. /etc/freetds.conf
unixODBC configurations
1. Drives configuration(FreeTDS section in /etc/odbcinst.ini)
2. DSN (/home/zenoss/.odbc.ini)

try first to make connection with SQL server with tsql from FreeTDS. And after tsql successful connection you can try to connect server with isql.

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 20:23:44 GMT

I figured out why I have to escape the isql command. My domain user name is dhopp. and when I do:

isql dsn DOMAIN\dhopp password

it tries to connect as DOMAINdhopp. It is interpreting '\d' as a regular expression. From a command prompt I can simply escape using '\\' however I can't do this in zenoss (and right now I'm in a testing phase and I don't want to go through the process of setting up a service account user because there is more to it then just me creating the account)

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 20:30:53 GMT

Hi dhopp,

try to install pyodbc (as zenoss user run: easy_install pyodbc ). After pyodbc installation you must restart zenoss.

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 20:44:46 GMT

Wow that was easier then I thought to install pyodbc (I had been hesitant). However now I get this error:

ERROR zen.SQLClient: Received error (('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnectW)')) from query: SET NOCOUNT ON

My /etc/odbcinst.ini includes:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver      = /usr/lib/libtdsodbc.so
Setup       = /usr/lib/libtdsS.so
FileUsage   = 1
CPTimeout   =
CPReuse     =
Trace       = No
TDS_Version = 8.0

[SQL Server]
Description = TDS driver (Sybase/MS SQL)
Driver      = /usr/lib/libtdsodbc.so.0
Setup       = /usr/lib/libtdsS.so
#FileUsage   = 1
CPTimeout   =
CPReuse     =
Trace       = No
TDS_Version = 8.0

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 22:24:01 GMT

Hi Egor,

Great news, I seem to have been successful in getting isql to communicate with my MSSQL server. I basically went back through and wiped everything out and started over and started from scratch. At first my failures continued, then I removed the SQLInstance name from my configuration properties and it connected. I'm not sure why this would have an impact on it, but it did. Now when I model the server I get the list of my DBs but I'm still getting this:

2010-11-19 17:22:54,025 ERROR zen.ZenModeler: Received error (('00000', '[ISQL]ERROR: Could not SQLPrepare')) from query: SELECT CONVERT(Char(128), SERVERPROPERTY('InstanceName')) AS InstanceName,

CONVERT(Char(128), SERVERPROPERTY('Edition')) AS Edition,

CONVERT(Char(128), SERVERPROPERTY('LicenseType')) AS LicenseType,

CONVERT(Int, SERVERPROPERTY('NumLicenses')) AS NumLicenses,

CONVERT(Int, SERVERPROPERTY('ProcessID')) AS ProcessID,

CONVERT(Char(128), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(Char(128), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

@@version AS Version

Thanks again for all your help in the matter, it's much appreciated!

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 22:26:59 GMT

And to add a final note to my last post. It seems changing to version 7.0 instead of the default version 8.0 cleared up the last of the errors. I'm going to let things run overnight to see what data I collect.

Thank you,

T_R_J

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 19 Nov 2010 22:35:31 GMT

to All,

for now exist one issue with actual version of this ZenPack and Zenoss 3.0.3. Zenoss 3.0.3 doesn't show Database Server Instances under server 'Components'. I'm working on this problem.

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 20 Nov 2010 06:26:33 GMT

Well I finally got it to work with pyodbc. I reverted back to zenoss 3.0.2 because of the problem you listed above and still had the problem of not being able to pull any data. After a lot of different things I decided to recompile freetds and unixodbc (I'm using centos 5.5 and the latest rpms available are quite old). I don't know if I needed to recompile both or not but I'm now polling stats for my sql databases. Still trying to find a db server that has multiple instances (the only two I know of are protected by a firewall and I'm not going to be able to open up the ports just for testing).

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 20 Nov 2010 22:31:19 GMT

the problem with displaying Database Instances in Zenoss 3.0.3 is solved in RDBMS Monitoring ZenPack 2.1.

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 22 Nov 2010 14:55:06 GMT

Sweet. I'll upgrade to 3.0.3 and give it a try.

Can I make a feature request? It would be nice to have a zProperty that can be used to easily override the IP address that this uses to monitor the database. We have some sql servers (for reasons unknown to me) that are not listening on 1433 on the main IP which is what I use to monitor pretty much every other aspect of the server. A zProperty that could be used to override it would be awesome.

--Dennis

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 22 Nov 2010 15:13:46 GMT

Hi Dennis,

you can add ';SERVER=sqlserversipaddress' option to zMsSqlConnectionString, modeler will be use that value instread of manageIp. You must also change connection strings in templates.

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 22 Nov 2010 20:27:14 GMT

I didn't think about changing the zMSSqlConnectionString. That could work...

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 26 Nov 2010 14:57:37 GMT

The first MS SQL instance has been monitored successfully in host A.

But the 2th and 3rd MS SQL instances are failed in hostB and hostC, And get the error messages continuously:

/Status/PyDBAPI Could not fetch data from source.

Both tsql and isql work.

I don't know why. All of them have the same configuration.

Thank you in advance!

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 26 Nov 2010 16:00:14 GMT

Are you using pyodbc?

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 26 Nov 2010 20:02:28 GMT

Hi Gavin_Shen,

are you trying to monitor SQL Server cluster?

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 26 Nov 2010 23:53:08 GMT

To dhopp, both pyodbc and unixodbc have been installed, but it should be unixodbc used. I configured /etc/freetds/freetds.conf, /etc/odbc.ini, /etc/odbcinst.ini files.

to bigegor, not SQL Server cluster, it's standalone.

Thank you!!!

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 11:05:58 GMT

to Gavin_Shen, i'm not realy understend your config. Why do you have multiple hosts (hostA, hostB, hostC)? Do you have configured every SQL instance with its own IP address?

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 11:25:18 GMT

Hi bigegor, I have 3 MS SQL Server 2008 instances(MSSQLSERVER) in host A, B, C separately. And each SQL instance is configured with its own IP address.

For the two instances located in hostB and hostC, after "model device", I can see DB Server Instances and Databases in the components, but only (master,msdb, ReportServer, ReportServerTempDB, tempdb) default databases in the list, no the customized databases showed. And no performance data can be received.

Error message: /Status/PyDBAPI Could not fetch data from source.

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 11:55:38 GMT

Hi Gavin_Shen,

so you have two options:

1. You can allow access to you named instances on single IP address. (on SQL Server)

2. you can register hostB and hostC as devices in Zenoss (you can do that with Virtual IP ZenPack). Note that you must replace zenoss.snmp.InterfaceMap with community.snmp.VirtualInterfaceMap collector plugin on hostA, hostB and hostC. zInterfaceMapIgnoreIpAddresses zProperty must contain IP addresses of hostB and hostC. On hostB and hostC you can also remove all other collector plugins from plugins list except community.snmp.VirtualInterfaceMap and community.odbc.MsSqlDatabaseMap.

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 12:04:45 GMT

have you specifyed instances names in zMsSqlSrvInstances?

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 12:48:39 GMT

Hi Bigegor, I don't specify instance name in zMsSqlSrvInstances.

For option 1, could you please tell me how to do ?

For option 2, I don't know why I need do like that. They're not cluster. The windows servers are stand-alone mode. and SQL Server instances are also stand-alone in different windows system.

Thank you very much!

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 13:03:16 GMT

Hi Gavin_Shen,

For option 1, look here: http://support.microsoft.com/kb/265808/en-us . You must be able to connect to all instances in form (test this on your SQL Server):

sqlcmd -S hostA\instance1
sqlcmd -S hostA\instance2
sqlcmd -S hostA\instance3

For option 2, you can not add new Device to Zenoss if IP address of this device (o.e. hostB) registered with other device (o.e hostA).

PS. but try first to specify all instances names to zMsSqlSrvInstances.

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 13:10:27 GMT

Hi bigegor,

I did option 2 as you advised.

HostA instance1 - still successful

HostB instance2 - still failed

HostC instance3 - still failed

BTW , SQLCMD works in the three servers

When "model device", I got the following error for instance 2/3:

2010-11-27 21:06:57,235 ERROR zen.SQLClient: Received error (('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnectW)')) from query: sp_helpdb

2010-11-27 21:07:13,256 ERROR zen.SQLClient: Received error (('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnectW)')) from query: SELECT CONVERT(Char(128), SERVERPROPERTY('InstanceName')) AS InstanceName,

CONVERT(Char(128), SERVERPROPERTY('Edition')) AS Edition,

CONVERT(Char(128), SERVERPROPERTY('LicenseType')) AS LicenseType,

CONVERT(Int, SERVERPROPERTY('NumLicenses')) AS NumLicenses,

CONVERT(Int, SERVERPROPERTY('ProcessID')) AS ProcessID,

CONVERT(Char(128), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(Char(128), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

@@version AS Version

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 13:41:34 GMT

check SQL Server instances network configuration (IP addresses, ports, etc) with "SQL Server Configuration Manager".

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 14:31:21 GMT

Hi bigegor,

The user does the connection to SQL Server as SA via windows authentication, But when I execute sp_helpdb in isql, it prompts:

[37000][unixODBC][FreeTDS][SQL Server]No permission to access database 'xxx'

However, the query can be executed successfully in SQL Server Management Studio. So I believe it is a SQL server problem, but not sure how to solve it now.

Thank you ...

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 14:45:07 GMT

try http://community.zenoss.org/servlet/JiveServlet/download/3391-20-3351/sqlpermissions.zip privided by tiredofme. It will set minimal needed permission on all databases.

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 27 Nov 2010 15:29:38 GMT

Fixed the permission problem ...

And checked the connection of SQL server configuration, but is still getting the errors above.

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 14 Dec 2010 17:05:25 GMT

Hello,

I have the following error :

Could not fetch data from source (Received error (('00000', '[ISQL]ERROR: Could not SQLConnect')) from query: SELECT [Info Errors] AS InfoErrors, [Kill Connection Errors] AS KillConnectionErrors, [User Errors] AS UserErrors, [AllocUnit], [Application], [Database], [Extent], [File], [HoBT], [Key], [Metadata], [Object], [Page], [RID], [_Total] AS Total, RTRIM(counter_name) AS counter_name FROM (SELECT instance_name, counter_name, cntr_value FROM sysperfinfo WHERE object_name like '%:Locks%' OR object_name like '%:SQL Errors%') P PIVOT (SUM(cntr_value) FOR [instance_name] IN ( [DB Offline Errors], [Kill Connection Errors], [User Errors], [Info Errors], [_Total], [AllocUnit], [Application], [Database], [Extent], [File], [HoBT], [Key], [Metadata], [Object], [Page], [RID] )) AS PVT )

I have this error for every server. Except from that, everything is working fine.

Databases are listed, instances are listed, graphs are graphed with correct values...

I can't find from where this message is comming from.

I'm using Zenoss 3.0.3 and the following versions of your ZenPacks :

ZenODBC : 3.2

SQLDataSource : 1.4

MsSQLMon_ODBC : 3.0

RDBMS : 2.1

The only place where I could find this query is on the MsSqlSrvInst Monitoring Templates, but each one of them en with a "where" statement... unlike the query on the error message.

Thanks.

Richard

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 14 Dec 2010 18:00:47 GMT

as zenoss user run command: zenperfsql run -v10 -d yourserver

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 14 Dec 2010 19:11:43 GMT

I'm sending you the result file.

Thanks.

Richard

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 22 Dec 2010 16:22:18 GMT

Using ubuntu 8.04 and zenoss 2.5

I almost having it working but even using isql sp_databases; gives the error

[37000][unixODBC][FreeTDS][SQL Server]Could not find stored procedure 'sp_databases'.
[ISQL]ERROR: Could not SQLExecute

sp_databases ; works but it appears when I update the code the space is been stripped out

I tried sp_databases\n use master which from the debug log returns the required data but then I get an error

File "/usr/local/zenoss/zenoss/ZenPacks/ZenPacks.community.ZenODBC-2.1.2-py2.4.egg/ZenPacks/community/ZenODBC/isqlClient.py", line 59, in outConnectionLost table,columns = self.tmap.pop(0) exceptions.IndexError: pop from empty list

"sp_databases\n "on its own fails with the first error

Any idea on a work arround for this, or where the whiteline removal code is so I can disable it.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 22 Dec 2010 17:42:30 GMT

Hi Robert,

is there a reason not to use the latest version of this ZenPack?

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 23 Dec 2010 10:07:24 GMT

I was looking for the python 2.4 versions and didn't notice that the later versions of this zenpack wern't 2.6 dependent. Now upgraded and all working well. Thanks

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 23 Feb 2011 22:07:36 GMT

Got this setup and working although I'm not getting any graphs on the databases or the total and used bytes are 0. It looked like it connected to the SQL server as it got all the databases and populate that information. Also not seeing any errors. From reading this and a few other posts I gather I need to do a

easy_install pyodbc

however everytime i run that command it just tells me

zenoss@alice:~$ easy_install pyodbc
/usr/bin/env: /usr/bin/env /usr/local/zenoss/python/bin/python: No such file or directory

Still looking to see if I'm missing any prerequisists to get this working running off a Ubuntu 10.04 64bit and connecting to a SQL2008 64bit server.

I have also waited 15 minutes for things to populate.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 23 Feb 2011 22:17:52 GMT

if you are using Zenoss 3.x, try: easy_install-2.6 pyodbc

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 03 Mar 2011 22:40:20 GMT

I'm also using CentOS 5.5. For anyone else who does this, here is what I did to make the install work.

Install all neccessary Zenpacks and Zenpack dependencies

yum install freetds

yum install unixODBC-devel

Follow the below walkthrough which basically describes how to compile FreeTDS and UNIXOdbc on CentOS 5.5.

http://www.opusinteractive.com/blogs/Connect-CentOS-5.5-64bit-to-a-Microsoft-SQL-Server-database-using-unixODBC-and-FreeTDS/1068.html

Confirm that you put the Driver file exists in /usr/local/lib/libtdsodbc.so

Confirm that the Setup file exists in /usr/lib/libtdsS.so

Add the following lines to /etc/odbcinst.ini

[FreeTDS]

Driver = /usr/local/lib/libtdsodbc.so

Setup = /usr/lib/libtdsS.so

Make sure you run the permissions script. I needed to give Domain Admin rights to the Windows Zenoss user anyways because it also does WMI queries. Domain Admins by default have DB Admin rights on a SQL Server therefore the permissions script is redundant.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 11 Mar 2011 19:11:06 GMT

If you just read my previous post, ignore it. I'm kinda new to linux so I did not realize that the odbc drivers for freetds were located in /usr/lib64 since it is a 64 bit CentOS install. Much easier than compiling new ODBC drivers for the install. I figured it out when I tried connecting to a MySQL server via ODBC. (Same issue.)

My new /etc/odbcinst.ini looks like this

[FreeTDS]

Driver = /usr/lib64/libtdsodbc.so

Setup = /usr/lib/libtdsS.so

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 15 Mar 2011 16:44:41 GMT

I don't see any databases on my SQL 2008 device in Zenoss after a modelling. Don't know what's wrong.
Here's what I did:
Installed Debian 5.0.7 and Zenoss 3.0.3.
apt-get install unixodbc libmyodbc unixodbc-dev freetds-dev sqsh tdsodbc
Configured /etc/odbcinst.ini and /etc/freetds/freetds.conf, I think correctly.
/home/zenoss/.odbc.ini looks correct also.

Installed these Zenpacks in order and with a 'zenoss restart' in between steps:
1. ZenPacks.community.deviceAdvDetail 2.5
2. ZenPacks.community.ZenODBC 3.2
3. ZenPacks.community.SQLDataSource version 1.6
4. ZenPacks.community.RDBMS version 2.2
5. ZenPacks.community.MsSQLMon_ODBC version 3.1

I have configured the zWinUser and zWinPassword correctly for the device. The user can list the databases using SQL Management Studio.

I have added community.odbc.MsSqlDatabaseMap zCollector plugin to the device. Then did a Model Device.

No databases or database graphs showed up.
So I tried:
apt-get install g++
/usr/local/zenoss/python/bin/easy_install-2.6 pyodbc
zenoss restart

But this did not help.

So I tried this as described above by bigegor:
1. as root user, rename /usr/bin/isql to /usr/bin/isql.bin
2. create bash script /usr/bin/isql
#!/bin/sh
echo $1 $2 $3 > /tmp/isqlauth.log
/usr/bin/isql.bin $1 $2 $3 -c -w -b
3. set execution permissions to this file
4. restart zenoss

But the /tmp/isqlauth.log is empty, and still, no databases or database graphs are listed on the device.

What's wrong?

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 15 Mar 2011 16:49:08 GMT

I would confirm that you can actually connect to the database from the linux box. Try using isql to connect to the database.

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 15 Mar 2011 16:55:30 GMT

Yes, I can connect to the SQL Server and run SQL commands using:

isql DSN DOMAIN\\UID PWD

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 15 Mar 2011 16:57:34 GMT

Hello,

try changing the user in the zProperty.

Try domain\user and domain/user (note the slash side). I had the same problem on my MSSQL 2008 servers.

Regards,

Richard

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 15 Mar 2011 17:15:31 GMT

Thanks for your replies.

I've tried some different zProperty names, and this is the modelling output:

DOMAIN\user
No SQL messages are shown.

DOMAIN/user
2011-03-15 18:01:27,828 ERROR zen.ZenModeler: Received error (('42000', "[42000] [unixODBC][FreeTDS][SQL Server]Login failed for user 'DOMAIN/user'.

DOMAIN//user
2011-03-15 18:01:27,828 ERROR zen.ZenModeler: Received error (('42000', "[42000] [unixODBC][FreeTDS][SQL Server]Login failed for user 'DOMAIN//user'.

DOMAIN\\user
2011-03-15 18:02:19,591 ERROR zen.ZenModeler: Received error (('42000', '[42000] [unixODBC][FreeTDS][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I will talk to the DBA tomorrow about this. Might be a problem on the SQL side. I'll get back to you.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 16 Mar 2011 17:29:19 GMT

Ok, we've given the DOMAIN\user the correct permissions using the sqlpermissions.sql script. But still, when modelling, no SQL messages appear, and I cannot see any databases on the device.

We also tried with a local SQL user (and overriding UID and PWD in zMsSqlConnectionString, but that did not work either.

However, both users can run SQL commands to the SQL 2008 server if I use isql DSN UID PWD from the shell.

I added the performance templates MsSqlSrvInst and MsSqlDatabase and at least I get some performance graphs with data on it now, but that's pretty uninteresting for us. I would really like to see the individual databases on the device page.

Any input would be appreciated.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 17 Mar 2011 09:15:05 GMT

Hello,

is the zMsSqlSrvInstances property field filled? Do you use name instance?

Regards,

Richard

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 17 Mar 2011 10:16:33 GMT

Yes, the field zMsSqlSrvInstances contains the name of the database instance.

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 28 Mar 2011 09:42:37 GMT

Hello,

MSSQL2005 on W2003

Modeling device without errors, all databases are in list but three days no any performance data.

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 28 Mar 2011 18:49:23 GMT

try to restart zenperfsql daemon.

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 29 Mar 2011 05:41:56 GMT

zenperfsql was restarted twice

then, I try

zenperfsql run -v10 -d mssql.domain.ru

2011-03-29 09:26:22,290 DEBUG zen.zenperfsql: Starting PBDaemon initialization

2011-03-29 09:26:22,299 INFO zen.zenperfsql: Connecting to localhost:8789

2011-03-29 09:26:22,300 DEBUG zen.zenperfsql: Logging in as admin

2011-03-29 09:26:22,310 INFO zen.zenperfsql: Connected to ZenHub

2011-03-29 09:26:22,310 DEBUG zen.zenperfsql: Setting up initial services: EventService, ZenPacks.community.SQLDataSource.services.SqlPerfConfig

2011-03-29 09:26:22,310 DEBUG zen.zenperfsql: Chaining getInitialServices with d2

2011-03-29 09:26:22,312 DEBUG zen.zenperfsql: Loaded service EventService from zenhub

2011-03-29 09:26:22,312 DEBUG zen.zenperfsql: Loaded service ZenPacks.community.SQLDataSource.services.SqlPerfConfig from zenhub

2011-03-29 09:26:22,313 DEBUG zen.zenperfsql: Queueing event {'severity': 0, 'component': 'zenperfsql', 'agent': 'zenperfsql', 'summary': 'started', 'manager': 'zenoss.domain.ru', 'device': 'localhost', 'eventClass': '/App/Start', 'monitor': 'localhost'}

2011-03-29 09:26:22,313 DEBUG zen.zenperfsql: Total of 1 queued events

2011-03-29 09:26:22,314 DEBUG zen.zenperfsql: Calling connected.

2011-03-29 09:26:22,314 DEBUG zen.zenperfsql: _configCycle fetching configuration

2011-03-29 09:26:22,314 DEBUG zen.collector.config: Fetching daemon configuration properties

2011-03-29 09:26:22,324 DEBUG zen.zenperfsql: Updated configCycleInterval preference to 360

2011-03-29 09:26:22,324 DEBUG zen.zenperfsql: Preferences object does not have attribute zenProcessParallelJobs

2011-03-29 09:26:22,324 DEBUG zen.zenperfsql: Preferences object does not have attribute renderurl

2011-03-29 09:26:22,324 DEBUG zen.zenperfsql: Preferences object does not have attribute discoveryNetworks

2011-03-29 09:26:22,325 DEBUG zen.zenperfsql: Preferences object does not have attribute modelerCycleInterval

2011-03-29 09:26:22,325 DEBUG zen.zenperfsql: Preferences object does not have attribute processCycleInterval

2011-03-29 09:26:22,325 DEBUG zen.zenperfsql: Preferences object does not have attribute wmiqueryTimeout

2011-03-29 09:26:22,325 DEBUG zen.zenperfsql: Preferences object does not have attribute statusCycleInterval

2011-03-29 09:26:22,325 DEBUG zen.zenperfsql: Preferences object does not have attribute pingTimeOut

2011-03-29 09:26:22,325 DEBUG zen.zenperfsql: Preferences object does not have attribute pingCycleInterval

2011-03-29 09:26:22,325 DEBUG zen.zenperfsql: Updated defaultRRDCreateCommand preference to ('RRA:AVERAGE:0.5:1:600', 'RRA:AVERAGE:0.5:6:600', 'RRA:AVERAGE:0.5:24:600', 'RRA:AVERAGE:0.5:288:600', 'RRA:MAX:0.5:6:600', 'RRA:MAX:0.5:24:600', 'RRA:MAX:0.5:288:600')

2011-03-29 09:26:22,326 DEBUG zen.zenperfsql: Preferences object does not have attribute maxPingFailures

2011-03-29 09:26:22,326 DEBUG zen.zenperfsql: Preferences object does not have attribute wmibatchSize

2011-03-29 09:26:22,326 DEBUG zen.zenperfsql: Preferences object does not have attribute eventlogCycleInterval

2011-03-29 09:26:22,326 DEBUG zen.zenperfsql: Preferences object does not have attribute winCycleInterval

2011-03-29 09:26:22,326 DEBUG zen.zenperfsql: Preferences object does not have attribute pingTries

2011-03-29 09:26:22,326 DEBUG zen.zenperfsql: Preferences object does not have attribute renderpass

2011-03-29 09:26:22,326 DEBUG zen.zenperfsql: Preferences object does not have attribute renderuser

2011-03-29 09:26:22,326 DEBUG zen.zenperfsql: Preferences object does not have attribute pingChunk

2011-03-29 09:26:22,327 DEBUG zen.zenperfsql: Preferences object does not have attribute perfsnmpCycleInterval

2011-03-29 09:26:22,327 DEBUG zen.collector.config: Fetching threshold classes

2011-03-29 09:26:22,328 DEBUG zen.zenperfsql: Loading classes ['Products.ZenModel.MinMaxThreshold', 'ZenPacks.community.deviceAdvDetail.thresholds.StatusThreshold']

2011-03-29 09:26:22,332 DEBUG zen.collector.config: Fetching collector thresholds

2011-03-29 09:26:22,343 DEBUG zen.thresholds: Updating threshold ('high event queue', ('localhost', ''))

2011-03-29 09:26:22,344 DEBUG zen.thresholds: Updating threshold ('zenmodeler cycle time', ('localhost', ''))

2011-03-29 09:26:22,344 DEBUG zen.thresholds: Updating threshold ('zenperfsnmp cycle time', ('localhost', ''))

2011-03-29 09:26:22,344 DEBUG zen.thresholds: Updating threshold ('zenping cycle time', ('localhost', ''))

2011-03-29 09:26:22,344 DEBUG zen.thresholds: Updating threshold ('zenprocess cycle time', ('localhost', ''))

2011-03-29 09:26:22,344 DEBUG zen.collector.config: Fetching configurations

2011-03-29 09:26:22,723 DEBUG zen.zenperfsql: Heartbeat timeout set to 900s

2011-03-29 09:26:22,723 DEBUG zen.zenperfsql: updateDeviceConfigs: updatedConfigs=[<Products.ZenCollector.services.config.DeviceProxy instance at 0xaf8612c>]

2011-03-29 09:26:22,723 DEBUG zen.zenperfsql: Processing configuration for mssql.domain.ru

2011-03-29 09:26:22,723 DEBUG zen.daemon: DummyListener: configuration <Products.ZenCollector.services.config.DeviceProxy instance at 0xaf8612c> added

2011-03-29 09:26:22,723 DEBUG zen.collector.tasks: splitting config <Products.ZenCollector.services.config.DeviceProxy instance at 0xaf8612c>

2011-03-29 09:26:22,724 DEBUG zen.zenperfsql: Tasks for config mssql.domain.ru: {'mssql.domain.ru': <__main__.ZenPerfSqlTask object at 0xaf861ec>}

2011-03-29 09:26:22,724 DEBUG zen.thresholds: Updating threshold ('Deadlocks', ('mssql.domain.ru', 'MSSQLSERVER'))

2011-03-29 09:26:22,724 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'AdventureWorks'))

2011-03-29 09:26:22,724 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'AdventureWorksDW'))

2011-03-29 09:26:22,725 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'ARMFM'))

2011-03-29 09:26:22,725 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'master'))

2011-03-29 09:26:22,725 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'model'))

2011-03-29 09:26:22,725 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'msdb'))

2011-03-29 09:26:22,725 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'ReportServer'))

2011-03-29 09:26:22,725 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'ReportServerTempDB'))

2011-03-29 09:26:22,726 DEBUG zen.thresholds: Updating threshold ('MsSql90Database status', ('mssql.domain.ru', 'tempdb'))

2011-03-29 09:26:22,726 DEBUG zen.zenperfsql: Rescheduling configuration check in 21600 seconds

2011-03-29 09:26:22,727 DEBUG zen.collector.scheduler: Task mssql.domain.ru starting on 300 second intervals

2011-03-29 09:26:22,727 DEBUG zen.collector.scheduler: Task mssql.domain.ru changing state from IDLE to RUNNING

2011-03-29 09:26:22,727 DEBUG zen.zenperfsql: Scanning device mssql.domain.ru [XX.XX.XX.XX]

2011-03-29 09:26:22,728 DEBUG zen.zenperfsql: Polling for SQL data from mssql.domain.ru [XX.XX.XX.XX]

2011-03-29 09:26:22,728 DEBUG zen.collector.scheduler: Task mssql.domain.ru changing state from RUNNING to SQLC_QUERY

2011-03-29 09:26:22,733 DEBUG zen.SQLClient: SQL Query: SET NOCOUNT ON

DECLARE @tt TABLE (dbname varchar(50), UnallocatedSpace bigint, Status int)

DECLARE @DBName varchar(50)

DECLARE @DBState int

DECLARE cursr INSENSITIVE CURSOR FOR SELECT name, state FROM sys.databases

OPEN cursr

FETCH NEXT FROM cursr INTO @DBName, @DBState

WHILE @@FETCH_STATUS = 0

BEGIN

IF (has_dbaccess(@DBName) = 1)

INSERT @tt EXEC('select dbname = ''' + @DBName + ''', UnallocatedSpace = (SELECT sum(total_pages) FROM [' + @DBName + '].sys.allocation_units) * 8192, Status= ''' + @DBState + '''')

ELSE

INSERT @tt VALUES (@DBName, 0, @DBState)

FETCH NEXT FROM cursr INTO @DBName, @DBState

END

CLOSE cursr

DEALLOCATE cursr

SELECT [Active Transactions] AS ActiveTransactions,

[Backup/Restore Throughput/sec] AS BackupRestoreThroughput,

[Bulk Copy Throughput/sec] AS BulkCopyThroughput,

([Data File(s) Size (KB)] * 1024) AS DataFilesSize,

[DBCC Logical Scan Bytes/sec] AS DBCCLogicalScanBytes,

[Log Bytes Flushed/sec] AS LogBytesFlushed,

([Log File(s) Size (KB)] * 1024) AS LogFilesSize,

([Log File(s) Used Size (KB)] * 1024) AS LogFilesUsedSize,

[Log Flushes/sec] AS LogFlushes,

[Log Flush Waits/sec] AS LogFlushWaits,

[Log Flush Wait Time] AS LogFlushWaitTime,

[Percent Log Used] AS PercentLogUsed,

[Repl. Trans. Rate] AS ReplTransRate,

[Shrink Data Movement Bytes/sec] AS ShrinkDataMovementBytes,

(CASE WHEN [Data File(s) Size (KB)] IS NULL THEN NULL WHEN (([Data File(s) Size (KB)] * 1024) >= tt.UnallocatedSpace) THEN (([Data File(s) Size (KB)] * 1024) - tt.UnallocatedSpace) ELSE 0 END) AS SpaceAvailable,

tt.Status AS Status,

[Transactions/sec] AS Transactions,

RTRIM(tt.dbname) AS instance_name

FROM @tt AS tt LEFT JOIN

(SELECT instance_name, counter_name, cntr_value FROM sysperfinfo WHERE object_name like '%:Databases%') P

PIVOT (SUM(cntr_value) FOR [counter_name] IN (

[Active Transactions],

[Backup/Restore Throughput/sec],

[Bulk Copy Throughput/sec],

[Data File(s) Size (KB)],

[DBCC Logical Scan Bytes/sec],

[Log Bytes Flushed/sec],

[Log File(s) Size (KB)],

[Log File(s) Used Size (KB)],

[Log Flushes/sec],

[Log Flush Waits/sec],

[Log Flush Wait Time],

[Percent Log Used],

[Repl. Trans. Rate],

[Shrink Data Movement Bytes/sec],

[Transactions/sec])) AS PVT

ON tt.dbname = PVT.instance_name

2011-03-29 09:26:22,733 DEBUG zen.zenperfsql: Performing periodic maintenance

2011-03-29 09:26:22,734 DEBUG zen.zenperfsql: Rescheduling maintenance in 300s

2011-03-29 09:26:22,889 DEBUG zen.SQLClient: SQL Query: SELECT [Active Temp Tables] AS ActiveTempTables,

[Batch Requests/sec] AS BatchRequests,

[Buffer cache hit ratio] / [Buffer cache hit ratio base] * 100 AS BufferCacheHitRatio,

[Checkpoint pages/sec] AS CheckpointPages,

[Free pages] * 8192 AS FreeServerMemory,

[FreeSpace Scans/sec] AS FreeSpaceScans,

[Full Scans/sec] AS FullScans,

[Index Searches/sec] AS IndexSearches,

[Lazy writes/sec] AS LazyWrites,

[Page life expectancy] AS PageLifeExpectancy,

[Page reads/sec] AS PageReads,

[Page Splits/sec] AS PageSplits,

[Page writes/sec] AS PageWrites,

[Probe Scans/sec] AS ProbeScans,

[Processes blocked] AS ProcessesBlocked,

[Range Scans/sec] AS RangeScans,

[Readahead pages/sec] AS ReadaheadPages,

[SQL Cache Memory (KB)] * 1024 AS SQLCacheMemory,

[SQL Compilations/sec] AS SQLCompilations,

[Target pages] * 8192 AS TargetServerMemory,

[Total pages] * 8192 AS TotalServerMemory,

[User Connections] AS UserConnections

FROM

(SELECT counter_name, cntr_value FROM sysperfinfo WHERE object_name LIKE '%:Access Methods%' OR object_name LIKE '% Manager%' OR object_name LIKE '% Statistics%') P

PIVOT (SUM(cntr_value) FOR [counter_name] IN (

[Active Temp Tables],

[Batch Requests/sec],

[Buffer cache hit ratio],

[Buffer cache hit ratio base],

[Checkpoint pages/sec],

[Free pages],

[FreeSpace Scans/sec],

[Full Scans/sec],

[Index Searches/sec],

[Lazy writes/sec],

[Page life expectancy],

[Page reads/sec],

[Page Splits/sec],

[Page writes/sec],

[Probe Scans/sec],

[Processes blocked],

[Range Scans/sec],

[Readahead pages/sec],

[SQL Cache Memory (KB)],

[SQL Compilations/sec],

[Target pages],

[Total pages],

[User Connections]

)) AS PVT

2011-03-29 09:26:23,003 DEBUG zen.SQLClient: SQL Query: SELECT [Info Errors] AS InfoErrors,

[Kill Connection Errors] AS KillConnectionErrors,

[User Errors] AS UserErrors,

[AllocUnit],

[Application],

[Database],

[Extent],

[File],

[HoBT],

[Key],

[Metadata],

[Object],

[Page],

[RID],

[_Total] AS Total,

RTRIM(counter_name) AS counter_name

FROM

(SELECT instance_name, counter_name, cntr_value FROM sysperfinfo WHERE object_name like '%:Locks%' OR object_name like '%:SQL Errors%') P

PIVOT (SUM(cntr_value) FOR [instance_name] IN (

[DB Offline Errors],

[Kill Connection Errors],

[User Errors],

[Info Errors],

[_Total],

[AllocUnit],

[Application],

[Database],

[Extent],

[File],

[HoBT],

[Key],

[Metadata],

[Object],

[Page],

[RID]

)) AS PVT

2011-03-29 09:26:23,103 DEBUG zen.collector.scheduler: Task mssql.domain.ru changing state from SQLC_QUERY to SQLC_PROCESS

2011-03-29 09:26:23,104 DEBUG zen.zenperfsql: Successful collection from mssql.domain.ru [XX.XX.XX.XX], results={'MSSQLSERVER_ReportServer_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_LockRequests': [], 'MSSQLSERVER_MsSqlSrvInst_sysperfinfo': [], 'MSSQLSERVER_model_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_AdventureWorksDW_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_SQL Errors': [], 'MSSQLSERVER_tempdb_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_NumberOfDeadlocks': [], 'MSSQLSERVER_AdventureWorks_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_LockWaitTime': [], 'MSSQLSERVER_ARMFM_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_LockTimeouts': [], 'MSSQLSERVER_master_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_msdb_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_ReportServerTempDB_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}]}

2011-03-29 09:26:23,107 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_ReportServer/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,107 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_ReportServer/sysperfinfo_Status

2011-03-29 09:26:23,109 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,109 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'ReportServer', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,109 DEBUG zen.zenperfsql: Total of 1 queued events

2011-03-29 09:26:23,110 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_model/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,110 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_model/sysperfinfo_Status

2011-03-29 09:26:23,111 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,111 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'model', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,111 DEBUG zen.zenperfsql: Total of 2 queued events

2011-03-29 09:26:23,111 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_AdventureWorksDW/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,112 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_AdventureWorksDW/sysperfinfo_Status

2011-03-29 09:26:23,112 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,112 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'AdventureWorksDW', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,112 DEBUG zen.zenperfsql: Total of 3 queued events

2011-03-29 09:26:23,113 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_tempdb/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,113 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_tempdb/sysperfinfo_Status

2011-03-29 09:26:23,113 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,114 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'tempdb', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,114 DEBUG zen.zenperfsql: Total of 4 queued events

2011-03-29 09:26:23,114 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_AdventureWorks/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,114 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_AdventureWorks/sysperfinfo_Status

2011-03-29 09:26:23,115 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,115 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'AdventureWorks', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,115 DEBUG zen.zenperfsql: Total of 5 queued events

2011-03-29 09:26:23,115 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_ARMFM/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,116 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_ARMFM/sysperfinfo_Status

2011-03-29 09:26:23,116 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,116 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'ARMFM', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,116 DEBUG zen.zenperfsql: Total of 6 queued events

2011-03-29 09:26:23,117 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_master/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,117 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_master/sysperfinfo_Status

2011-03-29 09:26:23,117 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,118 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'master', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,118 DEBUG zen.zenperfsql: Total of 7 queued events

2011-03-29 09:26:23,118 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_msdb/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,119 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_msdb/sysperfinfo_Status

2011-03-29 09:26:23,119 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,119 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'msdb', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,119 DEBUG zen.zenperfsql: Total of 8 queued events

2011-03-29 09:26:23,120 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_ReportServerTempDB/sysperfinfo_Status.rrd: 0.0

2011-03-29 09:26:23,120 DEBUG zen.thresholds: Checking value 0.0 on Devices/mssql.domain.ru/os/softwaredatabases/MSSQLSERVER_ReportServerTempDB/sysperfinfo_Status

2011-03-29 09:26:23,120 DEBUG zen.StatusThreshold: Checking sysperfinfo_Status 0.0 in {0: ('green', 0, 'Online'), 1: ('red', 5, 'Offline'), 2: ('yellow', 3, 'Restoring'), 3: ('yellow', 3, 'Recovering'), 4: ('yellow', 3, 'Recovery Pending'), 5: ('orange', 4, 'Suspect'), 6: ('orange', 4, 'Emergency')}

2011-03-29 09:26:23,120 DEBUG zen.zenperfsql: Queueing event {'compStatus': 0.0, 'severity': 0, 'compClass': 'Database', 'component': 'ReportServerTempDB', 'agent': 'zenperfsql', 'summary': 'threshold of MsSql90Database status exceeded: current status Online', 'manager': 'zenoss.domain.ru', 'eventKey': 'sysperfinfo_Status', 'device': 'mssql.domain.ru', 'eventClass': '/Change/Set/Status', 'monitor': 'localhost'}

2011-03-29 09:26:23,121 DEBUG zen.zenperfsql: Total of 9 queued events

2011-03-29 09:26:23,121 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_tempdb', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,121 DEBUG zen.zenperfsql: Total of 10 queued events

2011-03-29 09:26:23,121 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'zenperfsql', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,121 DEBUG zen.zenperfsql: Total of 11 queued events

2011-03-29 09:26:23,121 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,122 DEBUG zen.zenperfsql: Total of 12 queued events

2011-03-29 09:26:23,122 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_AdventureWorks', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,122 DEBUG zen.zenperfsql: Total of 13 queued events

2011-03-29 09:26:23,122 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_ReportServerTempDB', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,122 DEBUG zen.zenperfsql: Total of 14 queued events

2011-03-29 09:26:23,122 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_ReportServer', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,123 DEBUG zen.zenperfsql: Total of 15 queued events

2011-03-29 09:26:23,123 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_ARMFM', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,123 DEBUG zen.zenperfsql: Total of 16 queued events

2011-03-29 09:26:23,123 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_msdb', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,123 DEBUG zen.zenperfsql: Total of 17 queued events

2011-03-29 09:26:23,123 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_AdventureWorksDW', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,124 DEBUG zen.zenperfsql: Total of 18 queued events

2011-03-29 09:26:23,124 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_master', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,124 DEBUG zen.zenperfsql: Total of 19 queued events

2011-03-29 09:26:23,124 DEBUG zen.zenperfsql: Queueing event {'manager': 'zenoss.domain.ru', 'severity': 0, 'device': 'mssql.domain.ru', 'eventClass': '/Status/PyDBAPI', 'component': 'MSSQLSERVER_model', 'monitor': 'localhost', 'agent': 'zenperfsql', 'summary': 'Could not fetch data from source'}

2011-03-29 09:26:23,124 DEBUG zen.zenperfsql: Total of 20 queued events

2011-03-29 09:26:23,125 DEBUG zen.zenperfsql: Device mssql.domain.ru [XX.XX.XX.XX] scanned successfully

2011-03-29 09:26:23,125 DEBUG zen.collector.scheduler: Task mssql.domain.ru finished, result: {'MSSQLSERVER_ReportServer_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_LockRequests': [], 'MSSQLSERVER_MsSqlSrvInst_sysperfinfo': [], 'MSSQLSERVER_model_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_AdventureWorksDW_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_SQL Errors': [], 'MSSQLSERVER_tempdb_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_NumberOfDeadlocks': [], 'MSSQLSERVER_AdventureWorks_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_LockWaitTime': [], 'MSSQLSERVER_ARMFM_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_MsSqlSrvInst_LockTimeouts': [], 'MSSQLSERVER_master_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_msdb_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}], 'MSSQLSERVER_ReportServerTempDB_MsSqlDatabase_sysperfinfo': [{'Status': 0, 'SpaceAvailable': '', 'LogFlushWaits': '', 'PercentLogUsed': '', 'DataFilesSize': '', 'LogFilesSize': '', 'Transactions': '', 'LogFlushes': '', 'BulkCopyThroughput': '', 'LogFlushWaitTime': '', 'DBCCLogicalScanBytes': '', 'BackupRestoreThroughput': '', 'LogFilesUsedSize': '', 'ReplTransRate': '', 'ShrinkDataMovementBytes': '', 'ActiveTransactions': '', 'LogBytesFlushed': ''}]}

2011-03-29 09:26:23,125 DEBUG zen.collector.scheduler: Task mssql.domain.ru changing state from SQLC_PROCESS to IDLE

2011-03-29 09:26:23,126 INFO zen.zenperfsql: 1 devices processed (9 datapoints)

2011-03-29 09:26:23,126 INFO zen.collector.scheduler: Tasks: 1 Successful_Runs: 1 Failed_Runs: 0 Missed_Runs: 0

2011-03-29 09:26:23,132 DEBUG zen.zenperfsql: Sent a 'stop' event

2011-03-29 09:26:23,285 DEBUG zen.zenperfsql: stop() called when not running

2011-03-29 09:26:23,285 INFO zen.zenperfsql: Daemon CollectorDaemon shutting down

2011-03-29 09:26:23,286 DEBUG zen.zenperfsql: Removing service EventService

2011-03-29 09:26:23,286 DEBUG zen.zenperfsql: Removing service ZenPacks.community.SQLDataSource.services.SqlPerfConfig

Any things??

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 07 Apr 2011 20:12:13 GMT

After reading, re-reading and reading again, i feel dyslexic...... if anyone can help, i would appreciate it.

OS is cent 5.5

1st. tsql and isql login works

2nd. I have tried driver version7 and 8

3rd just for shiz i get the same error with the SA account

I believe this to be my issue

2011-04-07 16:01:11,967 ERROR zen.zenperfsql: Device CLserver2: Received error (('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnectW)')) from query: SET NOCOUNT ON

[root@localhost ~]# cat /home/zenoss/.odbc.ini

[912b148d854f536b6d94bf0c41b24d48]

TDS_Version = 8

DATABASE = clogic

DRIVER = FreeTDS

PORT = 1433

SERVER = 192.168.0.6

[root@localhost ~]# cat /etc/freetds.conf

# $Id: freetds.conf,v 1.11 2005/12/05 21:34:12 freddy77 Exp $

# The freetds.conf file is a replacement for the original interfaces

# file developed by Sybase. You may use either this or the interfaces

# file, but not both.

# FreeTDS will search for a conf file in the following order:

# 1) check if a file was set programatically via dbsetifile() and

# is in .conf format, if so use that,

# 2) otherwise, if env variable FREETDSCONF specifies a properly

# formatted config file, use it,

# 3) otherwise, look in ~/.freetds.conf,

# 4) otherwise, look in @sysconfdir@/freetds.conf

# If FreeTDS has found no suitable conf file it will then search for

# an interfaces file in the following order:

# 1) check if a file was set programatically via dbsetifile() and

# is in interfaces format, if so use that,

# 2) look in ~/.interfaces

# 3) look in $SYBASE/interfaces (where $SYBASE is an environment

# variable)

# Only hostname, port number, and protocol version can be specified

# using the interfaces format.

# The conf file format follows a modified Samba style layout. There

# is a [global] section which will affect all database servers and

# basic program behaviour, and a section headed with the database

# server's name which will have settings which override the global

# ones.

# Note that environment variables TDSVER, TDSDUMP, TDSPORT, TDSQUERY,

# and TDSHOST will override values set by a .conf or .interfaces file.

# To review the processing of the above, set env variable TDSDUMPCONFIG

# to a file name to log configuration processing.

# Global settings, any value here may be overridden by a database

# server specific section

[global]

# TDS protocol version

tds version = 4.2

; initial block size = 512

# uses some fixes required for some bugged MSSQL 7.0 server that

# return invalid data to big endian clients

# NOTE TDS version 7.0 or 8.0 should be used instead

; swap broken dates = no

; swap broken money = no

# Whether to write a TDSDUMP file for diagnostic purposes

# (setting this to /tmp is insecure on a multi-user system)

; dump file = /tmp/freetds.log

; debug flags = 0xffff

# Command and connection timeouts

; timeout = 10

; connect timeout = 10

# If you get out of memory errors, it may mean that your client

# is trying to allocate a huge buffer for a TEXT field.

# (Microsoft servers sometimes pretend TEXT columns are

# 4 GB wide!) If you have this problem, try setting

# 'text size' to a more reasonable limit

text size = 64512

# This is a Sybase hosted database server, if you are directly on the

# net you can use it to test.

[JDBC]

host = 192.138.151.39

port = 4444

tds version = 5.0

# The same server, using TDS 4.2. Used in configuration examples for the

# pool server, since the pool server supports only TDS 4.2.

[JDBC_42]

host = 192.138.151.39

port = 4444

tds version = 4.2

# The client connecting to the pool server will use this to find its

# listening socket. This entry assumes that the client is on the same

# system as the pool server.

[mypool]

host = 127.0.0.1

port = 5000

tds version = 4.2

# A typical Microsoft SQL Server 7.0 configuration

[SQL Server]

port = 1433

tds version = 8.0

# A typical Microsoft SQL Server 2000 configuration

;[MyServer2k]

; host = ntmachine.domain.com

; port = 1433

; tds version = 8.0

# A typical Microsoft SQL Server 6.x configuration

;[MyServer65]

; host = ntmachine.domain.com

; port = 1433

; tds version = 4.2

[root@localhost ~]# cat /etc/odbcinst.ini

# Example driver definitinions

# Included in the unixODBC package

[PostgreSQL]

Description = ODBC for PostgreSQL

Driver = /usr/lib/libodbcpsql.so

Setup = /usr/lib/libodbcpsqlS.so

FileUsage = 1

# Driver from the MyODBC package

# Setup from the unixODBC package

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib/libmyodbc.so

Setup = /usr/lib/libodbcmyS.so

FileUsage = 1

[FreeTDS]

Description = TDS driver (Sybase/MS SQL)

Driver = /usr/lib/libtdsodbc.so.0

Setup = /usr/lib/libtdsS.so

FileUsage = 1

CPTimeout =

CPReuse =

Trace = No

TDS_Version = 7.0

Threading = 1

[MySQL]

Description = MySQL driver

Driver = /usr/lib/libmyodbc.so

Setup = /usr/lib/libodbcmyS.so

CPTimeout =

CPReuse =

[root@localhost ~]#

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 06:46:44 GMT

Hello,

I find the error.

The trouble was not with plugin but with my SQL server. This one is not shows any perfomance counters.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 06:49:35 GMT

Try in /home/zenoss/.odbc.ini

set

DATABASE = master

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 11:40:02 GMT

Hey,

Thaks for the reply. I'll try it its worth a shot, but the default instance of master is disabled. The olny active db is clogic.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 11:45:37 GMT

BTW the server is win 2003 with sql 2008R1, currently the zenoss user has the same perms as SA

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 13:36:06 GMT

Tried it, restarted services. Still no luck, it looks like more of an error with pyodbc. I'm looking into seeing if there is a way to run pyodbc from command line with to see if that works

but anyideas are welcome

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 13:40:25 GMT

2011-04-08 09:35:19,196 ERROR zen.zenperfsql: Device CLserver2: Received error (('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnectW)')) from query: SET NOCOUNT ON

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 14:30:39 GMT

also using this string in zproperties

DRIVER={FreeTDS};TDS_Version=8.0;PORT=1433;UID=zenoss;PWD=**********

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 15:59:36 GMT

Zenhub
MySQLConnectionError: (2002, "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)")
2011-04-08 11:02:23,067 INFO zen.hub: ZenPing 'localhost.localdomain' not found, ignoring network topology.
2011-04-08 11:07:23,320 INFO zen.hub: ZenPing 'localhost.localdomain' not found, ignoring network topology.
2011-04-08 11:52:55,075 INFO zen.hub: ZenPing 'localhost.localdomain' not found, ignoring network topology.

[zenoss@localhost root]$ zenping run localhost.localdomain

2011-04-08 11:52:54,955 INFO zen.ZenPing: started

2011-04-08 11:52:54,959 INFO zen.ZenPing: Connecting to localhost:8789

2011-04-08 11:52:55,001 INFO zen.ZenPing: Connected to ZenHub

2011-04-08 11:52:55,005 INFO zen.ZenPing: _configCycle fetching configuration

2011-04-08 11:52:55,005 INFO zen.ZenPing: fetching monitor properties

2011-04-08 11:52:55,041 INFO zen.ZenPing: completed "fetching monitoring propert ies" request in 0.04 seconds

2011-04-08 11:52:55,042 INFO zen.ZenPing: getting threshold classes

2011-04-08 11:52:55,043 INFO zen.ZenPing: completed "getting threshold classes" request in 0.00 seconds

2011-04-08 11:52:55,045 INFO zen.ZenPing: getting collector thresholds

2011-04-08 11:52:55,062 INFO zen.ZenPing: completed "getting collector threshold s" request in 0.02 seconds

2011-04-08 11:52:55,062 INFO zen.ZenPing: fetching default RRDCreateCommand

2011-04-08 11:52:55,064 INFO zen.ZenPing: getting ping tree

2011-04-08 11:52:55,153 INFO zen.ZenPing: completed "getting ping tree" request in 0.09 seconds

2011-04-08 11:52:55,163 INFO zen.ZenPing: Rescheduling configuration check in 21 600 seconds

2011-04-08 11:52:55,173 INFO zen.ZenPing: Finished pinging 13 jobs in 0.01 secon ds

2011-04-08 11:52:55,173 INFO zen.ZenPing: Daemon ZenPing shutting down

[zenoss@localhost root]$ [zenoss@localhost root]$ zenping run localhost.localdomain

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 08 Apr 2011 19:31:34 GMT

pyodbc.connect('DRIVER={FreeTDS};Server=192.168.0.6;Database=clogic;UID=zenoss;PWD=*********;TDS_Version=8.0;Port=1433;')


passes

now i'm even more lost

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 12 Apr 2011 14:55:48 GMT

Have been struggling with this for a week now but I am so close!

I have installed the pack, setup freetds on the zenoss server and used isql to connect to a mssql2005 database and run queries on it, so far great!

I have also got zenoss to model the msssql2005 server correctly and it sees all the database and is only failing on actually pulling stats from these databases. Does thsi zenpack use something else to query this? I just assumed it still used isql which is why I am so confused as to why it wasnt working.

One thing that did catch me out was that because the windows account that zenoss uses (its a domain admin) has a password with special characters in it, when I was trying to connect using isql I had to escape these with backspace..for example:

If the username was domain\zenoss and the password was jam$poon I would have to connect using isql like this:

isql -v [connection_id_string] domain\\zenoss jam\$poon

Anything else would fail.

It is so tantalising seeing the databases being modeled correctly but not being able to get data out. I have run the permissions script with no errors and can see that it has had its effect. I am stumped here. I will probably have a look at the python code next to see if there is anything I can glean but I'm not too hopefull. If anyone could shed some light here I would be so grateful.

The errors I am getting are shown below, I have one for each database I am trying to monitor.

Thanks to all for the info in other posts which got me this far!

Could not fetch data from source (Received error (('00000', '[ISQL]ERROR: Could not SQLConnect')) from query: SET NOCOUNT ON DECLARE @tt TABLE (dbname varchar(50), UnallocatedSpace bigint, Status int) DECLARE @DBName varchar(50) DECLARE @DBState int DECLARE cursr INSENSITIVE CURSOR FOR SELECT name, state FROM sys.databases OPEN cursr FETCH NEXT FROM cursr INTO @DBName, @DBState WHILE @@FETCH_STATUS = 0 BEGIN IF (has_dbaccess(@DBName) = 1) INSERT @tt EXEC('select dbname = ''' + @DBName + ''', UnallocatedSpace = (SELECT sum(total_pages) FROM [' + @DBName + '].sys.allocation_units) * 8192, Status= ''' + @DBState + '''') ELSE INSERT @tt VALUES (@DBName, 0, @DBState) FETCH NEXT FROM cursr INTO @DBName, @DBState END CLOSE cursr DEALLOCATE cursr SELECT [Active Transactions] AS ActiveTransactions, [Backup/Restore Throughput/sec] AS BackupRestoreThroughput, [Bulk Copy Throughput/sec] AS BulkCopyThroughput, ([Data File(s) Size (KB)] * 1024) AS DataFilesSize, [DBCC Logical Scan Bytes/sec] AS DBCCLogicalScanBytes, [Log Bytes Flushed/sec] AS LogBytesFlushed, ([Log File(s) Size (KB)] * 1024) AS LogFilesSize, ([Log File(s) Used Size (KB)] * 1024) AS LogFilesUsedSize, [Log Flushes/sec] AS LogFlushes, [Log Flush Waits/sec] AS LogFlushWaits, [Log Flush Wait Time] AS LogFlushWaitTime, [Percent Log Used] AS PercentLogUsed, [Repl. Trans. Rate] AS ReplTransRate, [Shrink Data Movement Bytes/sec] AS ShrinkDataMovementBytes, (CASE WHEN [Data File(s) Size (KB)] IS NULL THEN NULL WHEN (([Data File(s) Size (KB)] * 1024) >= tt.UnallocatedSpace) THEN (([Data File(s) Size (KB)] * 1024) - tt.UnallocatedSpace) ELSE 0 END) AS SpaceAvailable, tt.Status AS Status, [Transactions/sec] AS Transactions, RTRIM(tt.dbname) AS instance_name FROM @tt AS tt LEFT JOIN (SELECT instance_name, counter_name, cntr_value FROM sysperfinfo WHERE object_name like '%:Databases%') P PIVOT (SUM(cntr_value) FOR [counter_name] IN ( [Active Transactions], [Backup/Restore Throughput/sec], [Bulk Copy Throughput/sec], [Data File(s) Size (KB)], [DBCC Logical Scan Bytes/sec], [Log Bytes Flushed/sec], [Log File(s) Size (KB)], [Log File(s) Used Size (KB)], [Log Flushes/sec], [Log Flush Waits/sec], [Log Flush Wait Time], [Percent Log Used], [Repl. Trans. Rate], [Shrink Data Movement Bytes/sec], [Transactions/sec])) AS PVT ON tt.dbname = PVT.instance_name )

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 12 Apr 2011 15:11:20 GMT

Even better is that if I connect to the server using isql and run the query above IT WORKS!

SO why does it work if I run it manually and not if zenoss is doing it?

[Edit]

Ok I am in the same position as jiml302...even pyodbc connect works.

[Edit]

Ahh feck....its not the password format either.

I can take the mssql connection string thats works manually and paste it into zenoss and then it fails...I need to dig into the code then.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 14 Apr 2011 15:16:20 GMT

I obervered something interesting, on the intial install of all the zenpacks, when I modified anything in the zconfig, it would update /home/user/.odbc.ini. After reinstalling all the zenpack and perfromfing dbmaintenance. I can modify the same staements, yet they DO NOT update .odbc.ini ? Verified ownership and perms.......

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 19 Apr 2011 12:59:22 GMT

Hello again!

I see very strange issure.

I delete one DB on my MSSQL server but zenoss show me everything OK.

1) I see no data on graph on this database last hour

2) after executing zenperfsql run -v10 -d MSSQL.mydomain

I see

'MSSQLSERVER_MyDatabase_MsSqlDatabase_sysperfinfo': [],

but I see this database online.

Why?

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 19 Apr 2011 20:54:07 GMT

I have tried getting this to work numerous times with no success. Here's what I've done, I'm using the appliance version of Zenoss (3.1) which is running Centos 5.4 x64:

- yum install freetds-devel unixODBC unixODBC-devel

- installed all the dependencies for this zenpack making sure to run zenoss restart after each zenpack installation

- Added the community.odbc.MsSQLDatabaseMap modeler plugin to my device

- Set the connection string to the following: DRIVER={FreeTDS};TDS_Version=8.0;PORT=1433;UID=MYDOMAIN\sqlviewuser;PWD=*******

- Left the zMsSqlSrvInstances field blank because this particular database server uses the default MSSQLSERVER instance

When I model the device it grabs the databases and instances with no issues, however no matter what I do nothing ever shows up in the performance graphs.

Here's the contents of /etc/odbcinst.ini:

# Example driver definitinions
#
#

# Included in the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib64/libtdsodbc.so
Setup           = /usr/lib64/libtdsS.so.1
FileUsage       = 1
CPTimeout        =
CPReuse         =
Trace           = No
TDS_Version     = 7.0
# Driver from the MyODBC package
# Setup from the unixODBC package
#[MySQL]
#Description    = ODBC for MySQL
#Driver         = /usr/lib/libmyodbc.so
#Setup          = /usr/lib/libodbcmyS.so
#FileUsage      = 1

Any idea what I'm doing wrong here?

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 07 Jun 2011 14:25:10 GMT

I'm having some issue with pulling data from MS SQL Server 2008 installed on Windows Server 2008 R2 (x64).

I've installed all of the dependencies and have my /etc/odbcinst.ini configured correctly.

I have a Windows 2005 SQL Express box that I got working, so I know that the drivers are setup correctly and able to make a connection.

I have also tested the connection to my windows 2008 SQL servers via the code below, which seems to work. When I use the same code with an incorrect password I get an error, so I am under the assumption its working.

import pyodbc

pyodbc.connect('DRIVER={FreeTDS};Server=10.1.22.35;UID=<username>;PWD=<password>;TDS_Version=8.0;Port=1433;')

I breifly talk to bigegor yesterday via IRC and he recommended that I modify the following files and lines. He believes its an issue with unixODBC and ansi

bigegor_: St3v3o: ZenPacks/community/ZenODBC/datasources/OdbcDataSource.py

bigegor_: St3v3o: line 52: return "findodbc, '%s', ansi=1"%SQLDataSource.SQLDataSource.getCommand(self,

bigegor_: St3v3o: ZenPacks/community/ZenODBC/OdbcPlugin.py

2:55:37 PM bigegor_: St3v3o: line 46: queries[tname] = (sql, {}, "findodbc, '" + cs + "', ansi=1", columns)

There errors I am seeing in the event log for one of my devices are here http://pastebin.com/ybA0DMPs

and I am seeing the following error in the zenperfsql.log http://pastebin.com/K4FrT1ve

Has anyone run into this issue and come up with a solution?

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 07 Jun 2011 20:19:23 GMT

FIXED!!!!!

This issue was with SQL Server Broswer service on my Windows 2008 R2 w/ SQL 2008 x64 bit installations. It was disabled (by deafult?) once I enabled and started this service I was able to connect.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 08 Jun 2011 11:56:14 GMT

Did you make the initial changes to the python script per Egor, or did you just enable the service ?

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 08 Jun 2011 12:00:42 GMT

I made the changes per Egor ..but the issue still presisted. I then backed out the changes and removed pyodbc. Still nothing, so I looked to see that from my reads by default in SQL 2008 the SQL Browser Serivce is disabled. Once enabled and started I was able to connect. I'll test with pyodbc hopefully today if I can find some cycles and report back.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 15 Jun 2011 13:26:10 GMT

Any news ? is it graphing ?

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 15 Jun 2011 13:43:45 GMT

Yes I have it graphing..I just had to enable the SQL Browser Service ...and I removed the inital changes that egor had suggested and went with what was default in the zenpack.

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 16 Aug 2011 15:16:35 GMT

hi Egor,

tsql and isql could both connect to the SQL 2005 instance. But I still could not get the database info. I tried to run the zenperfsql to debug and found the followings error "Configuration for host Unavailable". What does this mean?

2011-08-16 22:52:09,143 DEBUG zen.zenperfsql: Starting PBDaemon initialization
2011-08-16 22:52:09,175 INFO zen.zenperfsql: Connecting to localhost:8789
2011-08-16 22:52:09,176 DEBUG zen.zenperfsql: Logging in as admin
2011-08-16 22:52:09,199 INFO zen.zenperfsql: Connected to ZenHub
2011-08-16 22:52:09,200 DEBUG zen.zenperfsql: Setting up initial services: EventService, ZenPacks.community.SQLDataSource.services.SqlPerfConfig
2011-08-16 22:52:09,201 DEBUG zen.zenperfsql: Chaining getInitialServices with d2
2011-08-16 22:52:09,204 DEBUG zen.zenperfsql: Loaded service EventService from zenhub
2011-08-16 22:52:09,206 DEBUG zen.zenperfsql: Loaded service ZenPacks.community.SQLDataSource.services.SqlPerfConfig from zenhub
2011-08-16 22:52:09,206 DEBUG zen.zenperfsql: Queueing event {'severity': 0, 'component': 'zenperfsql', 'agent': 'zenperfsql', 'summary': 'started', 'manager': 'zenoss-tb.macausjm.com', 'device': 'localhost', 'eventClass': '/App/Start', 'monitor': 'localhost'}
2011-08-16 22:52:09,206 DEBUG zen.zenperfsql: Total of 1 queued events
2011-08-16 22:52:09,207 DEBUG zen.zenperfsql: Calling connected.
2011-08-16 22:52:09,208 DEBUG zen.zenperfsql: _configCycle fetching configuration
2011-08-16 22:52:09,208 DEBUG zen.collector.config: Fetching daemon configuration properties
2011-08-16 22:52:09,260 DEBUG zen.zenperfsql: Updated configCycleInterval preference to 360
2011-08-16 22:52:09,260 DEBUG zen.zenperfsql: Preferences object does not have attribute zenProcessParallelJobs
2011-08-16 22:52:09,260 DEBUG zen.zenperfsql: Preferences object does not have attribute renderurl
2011-08-16 22:52:09,260 DEBUG zen.zenperfsql: Preferences object does not have attribute discoveryNetworks
2011-08-16 22:52:09,260 DEBUG zen.zenperfsql: Preferences object does not have attribute modelerCycleInterval
2011-08-16 22:52:09,261 DEBUG zen.zenperfsql: Preferences object does not have attribute processCycleInterval
2011-08-16 22:52:09,261 DEBUG zen.zenperfsql: Preferences object does not have attribute wmiqueryTimeout
2011-08-16 22:52:09,261 DEBUG zen.zenperfsql: Preferences object does not have attribute statusCycleInterval
2011-08-16 22:52:09,261 DEBUG zen.zenperfsql: Preferences object does not have attribute pingTimeOut
2011-08-16 22:52:09,261 DEBUG zen.zenperfsql: Preferences object does not have attribute pingCycleInterval
2011-08-16 22:52:09,261 DEBUG zen.zenperfsql: Updated defaultRRDCreateCommand preference to ('RRA:AVERAGE:0.5:1:600', 'RRA:AVERAGE:0.5:6:600', 'RRA:AVERAGE:0.5:24:600', 'RRA:AVERAGE:0.5:288:600', 'RRA:MAX:0.5:6:600', 'RRA:MAX:0.5:24:600', 'RRA:MAX:0.5:288:600')
2011-08-16 22:52:09,261 DEBUG zen.zenperfsql: Preferences object does not have attribute maxPingFailures
2011-08-16 22:52:09,262 DEBUG zen.zenperfsql: Preferences object does not have attribute wmibatchSize
2011-08-16 22:52:09,262 DEBUG zen.zenperfsql: Preferences object does not have attribute eventlogCycleInterval
2011-08-16 22:52:09,262 DEBUG zen.zenperfsql: Preferences object does not have attribute winCycleInterval
2011-08-16 22:52:09,262 DEBUG zen.zenperfsql: Preferences object does not have attribute pingTries
2011-08-16 22:52:09,262 DEBUG zen.zenperfsql: Preferences object does not have attribute renderpass
2011-08-16 22:52:09,262 DEBUG zen.zenperfsql: Preferences object does not have attribute renderuser
2011-08-16 22:52:09,263 DEBUG zen.zenperfsql: Preferences object does not have attribute pingChunk
2011-08-16 22:52:09,263 DEBUG zen.zenperfsql: Preferences object does not have attribute perfsnmpCycleInterval
2011-08-16 22:52:09,263 DEBUG zen.collector.config: Fetching threshold classes
2011-08-16 22:52:09,264 DEBUG zen.zenperfsql: Loading classes ['Products.ZenModel.MinMaxThreshold', 'ZenPacks.community.deviceAdvDetail.thresholds.StatusThreshold']
2011-08-16 22:52:09,286 DEBUG zen.collector.config: Fetching collector thresholds
2011-08-16 22:52:09,300 DEBUG zen.thresholds: Updating threshold ('high event queue', ('localhost', ''))
2011-08-16 22:52:09,301 DEBUG zen.thresholds: Updating threshold ('zenmodeler cycle time', ('localhost', ''))
2011-08-16 22:52:09,301 DEBUG zen.thresholds: Updating threshold ('zenperfsnmp cycle time', ('localhost', ''))
2011-08-16 22:52:09,301 DEBUG zen.thresholds: Updating threshold ('zenping cycle time', ('localhost', ''))
2011-08-16 22:52:09,301 DEBUG zen.thresholds: Updating threshold ('zenprocess cycle time', ('localhost', ''))
2011-08-16 22:52:09,302 DEBUG zen.collector.config: Fetching configurations
2011-08-16 22:52:09,307 ERROR zen.zenperfsql: Configuration for 10.0.13.77 unavailable
2011-08-16 22:52:09,308 DEBUG zen.zenperfsql: Sent a 'stop' event
2011-08-16 22:52:09,308 DEBUG zen.zenperfsql: Heartbeat timeout set to 900s
2011-08-16 22:52:09,308 DEBUG zen.zenperfsql: updateDeviceConfigs: updatedConfigs=[]
2011-08-16 22:52:09,321 DEBUG zen.zenperfsql: Rescheduling configuration check in 21600 seconds
2011-08-16 22:52:09,322 DEBUG zen.zenperfsql: stop() called when not running
2011-08-16 22:52:09,322 INFO zen.zenperfsql: Daemon CollectorDaemon shutting down
2011-08-16 22:52:09,323 DEBUG zen.zenperfsql: Removing service EventService
2011-08-16 22:52:09,323 DEBUG zen.zenperfsql: Removing service ZenPacks.community.SQLDataSource.services.SqlPerfConfig
2011-08-16 22:52:09,325 DEBUG zen.zenperfsql: Performing periodic maintenance
2011-08-16 22:52:09,326 DEBUG zen.zenperfsql: Rescheduling maintenance in 300s

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 16 Aug 2011 19:47:32 GMT

Hi wilsonlai,

have you modeled your server with community.odbc.MsSqlDatabaseMap collector plugin?

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 17 Aug 2011 10:29:29 GMT

I actually added the community.odbc.MsSqlDatabaseMap to the collector plugin.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 17 Aug 2011 16:27:03 GMT

try to run as zenoss user:

zenmodeler run -v10 -d yoursqlserver --collect=community.odbc.MsSqlDatabaseMap

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 17 Aug 2011 17:26:49 GMT

here is the output. Keep running 1 clients and then timeout.

011-08-18 01:19:21,808 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:22,808 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:23,808 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:24,808 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:25,808 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:26,808 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:27,808 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:28,808 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:29,809 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:30,809 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:31,809 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:32,809 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:33,811 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:34,811 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:35,811 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:36,811 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:37,811 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:38,811 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:39,812 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:40,812 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:41,812 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:42,812 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:43,812 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:44,812 DEBUG zen.ZenModeler: Running 1 clients
2011-08-18 01:19:45,812 WARNING zen.ZenModeler: Client 10.0.13.77 timeout
2011-08-18 01:19:45,812 DEBUG zen.ZenModeler: Running 0 clients
2011-08-18 01:19:45,812 INFO zen.ZenModeler: Scan time: 180.82 seconds
2011-08-18 01:19:45,813 DEBUG zen.thresholds: Checking value 180.824164867 on Daemons/localhost/zenmodeler_cycleTime
2011-08-18 01:19:45,814 DEBUG zen.MinMaxCheck: Checking zenmodeler_cycleTime 180.824164867 against min None and max 34560.0
2011-08-18 01:19:45,814 DEBUG zen.ZenModeler: Queueing event {'manager': 'zenoss-tb', 'eventKey': 'zenmodeler cycle time', 'device': 'localhost', 'eventClass': '/Perf', 'summary': 'threshold of zenmodeler cycle time restored: current value 180.82', 'component': '', 'monitor': 'localhost', 'agent': 'zenmodeler', 'severity': 0}
2011-08-18 01:19:45,815 DEBUG zen.ZenModeler: Total of 1 queued events
2011-08-18 01:19:45,815 DEBUG zen.ZenModeler: Sent a 'stop' event
2011-08-18 01:19:45,821 DEBUG zen.ZenModeler: stop() called when not running
2011-08-18 01:19:45,821 INFO zen.ZenModeler: Daemon ZenModeler shutting down
2011-08-18 01:19:45,823 DEBUG zen.ZenModeler: Removing service EventService
2011-08-18 01:19:45,823 DEBUG zen.ZenModeler: Removing service ModelerService

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 17 Aug 2011 18:13:15 GMT

wich ZenPacks versions are you using?

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Aug 2011 05:11:54 GMT

Here are the zenpack info.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Aug 2011 16:08:57 GMT

have you installed pyodbc?

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Aug 2011 16:49:42 GMT

yes.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Aug 2011 16:54:39 GMT

run zendmd as zenoss user and try to import pyodbc.

zendmd
import pyodbc

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Aug 2011 17:11:05 GMT

import Error.

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 22 Aug 2011 16:13:29 GMT

Hi bigegor,

How could the pyodbc module be imported to zenoss?

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 22 Aug 2011 18:11:27 GMT

you must compile and install it as zenoss user. Run as zenoss user:

easy_install-2.6 pyodbc

Note, that you must have gcc and some development libraries (o.e. odbc-devel) installed on your zenoss server.

Egor

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 23 Aug 2011 16:24:10 GMT

When I try to run easy_install-2.6 pyodbc, found error

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 23 Aug 2011 16:52:19 GMT

have you read my previous post?

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 23 Aug 2011 17:02:16 GMT

Hi bigegor,

Yes, definitely. Finally I found that I need to manually download the pyodbc-2.1.8 version and install it on my zenoss server. Now the SQL host could be modeled and I could see that instance and databases. Just wait if perormance data could collected.

Many thanks.

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 30 Aug 2011 06:58:53 GMT

Hi Bigegor,

I found that the SQL Buffer Cache Hit Ratio returns "0" value from the SQL command. I tried to change the SQL command as follows but still get no hope.

Select CONVERT(Int, ([Buffer cache hit ratio]*1.0 / [Buffer cache hit ratio base] *100)) AS BufferCacheHitRatio,

....

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 19 Sep 2011 13:30:23 GMT

Hi Egor,

I have found that there are features that could be able to add, delete and lock the database. How could these features be disabled/removed in Zenoss and we are using domain account access as the SQL sysdba?

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 10 Oct 2011 10:16:34 GMT

Hi everyone

I have been struggling to get this Zenpack working on one of our SQL 2008 boxes and get an error similiar to others in this post.

ERROR zen.SQLClient: Received error (('00000', '[ISQL]ERROR: Could not SQLConnect')) from query

After much tearing of hair we have found that for some reason the default public permissions on MS SQL Build 10.0.2531 do not seem to work. We have now given Zenoss Sa access and it is working well....

just wanted to give someone a heads up if they have the same error...

maybe Egor may be able to comment on this one, has Microsoft changed something and as we are on SP2 now does it need changing.

hope this helps

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 13 Oct 2011 14:43:15 GMT

Dear all

We are using the TDS connection string which has been fine until the default instance name has changed we have fixed this by chaning the string to

DRIVER={FreeTDS};TDS_Version=8.0;UID=USERNAME;PWD=Password;SERVER=Nxx040\WTEST

this works wel for one instance but we have two instances on this server, can two instances be monitored from the DRIVER string?

hope someone can help !

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 13 Oct 2011 16:28:17 GMT

use zMsSqlSrvInstances zProperty to specify blankspace separated list of instances names.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 14 Oct 2011 08:12:54 GMT

Working a treat! Thankyou, just for other users for this to work take the username and password out of zMsSqlConnectionString property and use zMsSqlUserrname and ZMsSqlPassword property boxes

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 19 Oct 2011 13:22:44 GMT

So after a few months of opennms, i'm back. Rebuilt the server. Have wmi working perfectly, snmp. Reinstalled everything for odbc. Can connect through ISQL.PYODBC is intalled. No errors modeling device. NO GRAPHING. Run

zenperfsql run -v10 -d clserver3

2011-10-19 09:17:49,200 ERROR zen.collector.config: Configuration for clserver3 unavailable -- is that the correct name?

WTF ???

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 13 Dec 2011 23:29:41 GMT

If your SQL devices at a location other than /Devices/Windows, ensure you override the MsSqlDatabase and MsSqlSrvInst templates there.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 14 Dec 2011 13:48:00 GMT

Thanks whytee, that was the trick

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 01 May 2012 07:26:34 GMT

With the newest MsSQLMon, there is a segfault when modeling!

@InstanceName AS InstanceName,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('Edition'))) AS Edition,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('LicenseType'))) AS LicenseType,

RTRIM(CONVERT(Int, SERVERPROPERTY('NumLicenses'))) AS NumLicenses,

RTRIM(CONVERT(Int, SERVERPROPERTY('ProcessID'))) AS ProcessID,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('ProductVersion'))) AS ProductVersion,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('ProductLevel'))) AS ProductLevel,

CONVERT(Int, isnull(@value, 1433)) as PortNumber,

@@version AS Version": [{'processid': '1408', 'licensetype': 'DISABLED', 'numlicenses': '', 'edition': 'Standard Edition (64-bit)', 'version': 'Microsoft SQL Server 2005 - 9.00.5057.00 (X64) \n\tMar 25 2011 13:33:31 \n\tCopyright (c) 1988-2005 Microsoft Corporation\n\tStandard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)', 'portnumber': 1433.0, 'productversion': '9.00.5057.00', 'productlevel': 'SP4', 'instancename': 'MSSQLSERVER', 'dbsiproperties': 'IsFullTextInstalled'}]

2012-05-01 09:24:49,630 DEBUG zen.pysamba: OK: 192.168.XX.XX - Connect

2012-05-01 09:24:49,633 DEBUG zen.pysamba: OK: 192.168.XX.XX - ExecQuery

2012-05-01 09:24:49,638 DEBUG zen.pysamba: OK: 192.168.XX.XX - Retrieve result data.

2012-05-01 09:24:49,642 DEBUG zen.pysamba: OK: 192.168.XX.XX - Retrieve result data.

Segmentation fault

zenoss@zenoss:/usr/local/zenoss/zenoss/ZenPacks/ZenPacks.community.WMIDataSource-3.0-py2.6.egg$

Thanks,

Fred

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 02 May 2012 18:33:01 GMT

Hi Fred,

MsSQLMon doesn't used pysamba. Please check other modeler plugins (community.wmi.*)

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 27 Jun 2012 21:11:48 GMT

Hi everyone,

i have spent alot of time making this template work, and seriously the result are very nice!

Thx to you bigegor!

Below is what i have done to make this work on a ubuntu 12.04 with zenoss 3.2.1 and a sql 2008.

Install zenpack dependencies : RDBMS monitoring and SQL data source.

Install zenpack MSSQL database monitor.

Restart zenoss: as zenoss user (su zenoss) zenoss restart

Override template MsSqlDatabase and MsSqlSrvInst where your device are installed.

On your device in configuration properties, edit : zMsSqlSrvInstances and add your Sqlserver instances (ex MSSQLSERVER)

Optional : In properties too, edit zMsSqlConnectionString and add : 'pyisqldb',DRIVER='{FreeTDS}',ansi=True,TDS_Version='7.0',SERVER='${here/manageIp}',DATABASE='master',UID='domain\\user',PWD='password'

That’s override the zwinuser / zwinpassword

In command line for Ubuntu/debian user:

sudo apt-get install unixodbc libmyodbc unixodbc-dev freetds-dev sqsh tdsodbc gcc g++

in /etc/freetds/freetds.conf add :

[MyServer]

host = hostname or ipaddr

port = 1433

tds version = 7.0 ( depend of the sql server version you use , but for sql 2008 I use 7.0 and that works fine )

in /etc/odbc.ini add:

[YourDataSourceName]

driver = FreeTDS

Servername = MyServer #( the same name you use in freetds.conf )

DATABASE = Master

In /etc/odbcinst.ini add:

[FreeTDS]

Description = TDS driver (Sybase/MS SQL)

Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so #( the path may be different )

Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so #( the path may be different )

CPTimeout =

CPReuse =

FileUsage = 1

[ODBC]

DEBUG = 1

TraceFile = /home/yourprofilename/sqltrace.log #log information for debugging

Trace = Yes

Now you can try to test the connection with the name you enter in freetds.conf :

sqsh -S MyServer -U username -P password

you can select from a table in your SQL Server database and display it on screen

1> select * from dbname.dbo.tablename
2> go

If everything goes well , try with isql with the datasource name you enter in odbc.ini

isql -v YourDataSourceName username password

If something goes wrong, go in /home/yourprofilename/sqltrace.log and take a look at your log for debugging.

Install pyodbc as zenoss user (su zenoss):

If you use zenoss 3.x.x : easy_install-2.6 pyodbc

Else : easy_install pyodbc

After that you go on your Device in zenoss and do a remodel.

P.S. sorry for my bad english!

M-A

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 28 Jun 2012 16:18:19 GMT

Hi all,

i'm coming back on my yesterday installation of this zenpack. Everything seems to going well but this morning i recieved an event with this error. Somebody can see what's goes wrong ?

All my graph are populated and everything seems to be ok... but this:

Traceback (most recent call last):
File "/usr/local/zenoss/zenoss/Products/DataCollector/zenmodeler.py", line 623, in processClient
datamaps = plugin.process(device, results, self.log)
File "/usr/local/zenoss/zenoss/ZenPacks/ZenPacks.community.MsSQLMon-3.2.egg/ZenPacks/community/MsSQLMon/modeler/plugins/community/sql/MsSqlDatabaseMap.py", line 201, in process
om.type = TYPES.get(int(getattr(om, 'type' , 1)), TYPES[1])
ValueError: invalid literal for int() with base 10: ''

Thanks,

Marc

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 03 Jul 2012 19:16:06 GMT

Hi all,

I upgraded Zenoss Stack 3.2.1 to the newest versions of SQLDataSource, RDBMS, MySQLMon and MsSQLMon and everything works just fine again except for two problems:

1.) zenperfwmi is reported under "Zenoss Issues" even though it is no longer needed and the daemon does not even start. (seems to be cosmetic but annoying anyway)

2.) There are errors when modelling a windows server with MS-SQL resulting in no MS-Databases showing up:

2012-07-03 21:11:33,847 WARNING zen.SQLClient: Error in community.sql.MsSqlDatabaseMap query "SET NOCOUNT ON

DECLARE @InstanceName nvarchar(50)

DECLARE @value VARCHAR(100)

DECLARE @RegKey nvarchar(500)

SET @InstanceName=RTRIM(CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER')))

IF (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8

BEGIN

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',

@value_name = @InstanceName,

@value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll'

END

ELSE

BEGIN

IF @InstanceName='MSSQLSERVER'

BEGIN

SET @RegKey='SOFTWARE\Microsoft'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP'

END

ELSE

BEGIN

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP'

END

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'TcpPort',

@value = @value OUTPUT

IF (@value IS NULL)

BEGIN

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'TcpDynamicPorts',

@value = @value OUTPUT

END

SELECT

@InstanceName AS InstanceName,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('Edition'))) AS Edition,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('LicenseType'))) AS LicenseType,

RTRIM(CONVERT(Int, SERVERPROPERTY('NumLicenses'))) AS NumLicenses,

RTRIM(CONVERT(Int, SERVERPROPERTY('ProcessID'))) AS ProcessID,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('ProductVersion'))) AS ProductVersion,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('ProductLevel'))) AS ProductLevel,

CONVERT(Int, isnull(@value, 1433)) as PortNumber,

@@version AS Version": [ISQL]ERROR: Could not SQLConnect

2012-07-03 21:11:33,877 WARNING zen.SQLClient: Error in community.sql.MsSqlDatabaseMap query "sp_helpdb": [ISQL]ERROR: Could not SQLConnect

2012-07-03 21:11:33,877 WARNING zen.PythonClient: Error in community.sql.MsSqlDatabaseMap: [ISQL]ERROR: Could not SQLConnect

2012-07-03 21:11:53,358 ERROR zen.ZenModeler: Problem while executing plugin community.wmi.RouteMap

2012-07-03 21:11:53,358 ERROR zen.ZenModeler: Traceback (most recent call last):

File "/usr/local/zenoss/zenoss/Products/DataCollector/zenmodeler.py", line 623, in processClient

datamaps = plugin.process(device, results, self.log)

File "/usr/local/zenoss/zenoss/ZenPacks/ZenPacks.community.WMIPerf_Windows-2.5.80.egg/ZenPacks/community/WMIPerf_Windows/modeler/plugins/community/wmi/RouteMap.py", line 75, in process

om.routeproto = self.mapSnmpVal(routeproto, self.routeProtoMap)

File "/usr/local/zenoss/zenoss/ZenPacks/ZenPacks.community.WMIPerf_Windows-2.5.80.egg/ZenPacks/community/WMIPerf_Windows/modeler/plugins/community/wmi/RouteMap.py", line 88, in mapSnmpVal

value = map[value-1]

TypeError: tuple indices must be integers, not float

I am completely stuck. Any ideas where to look further?

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 03 Jul 2012 21:18:00 GMT

Hi geotek,

1. i'm pretty sure you have a problem with old daemons heartbeats. Solution was many time published here in forums, and once again:

mysql -u zenoss -p password events -e "delete from heartbeat where component in ('zenperfwmi', 'zenperfwbem')"

2. your Zenoss server can't connect to MS SQL database, read previous post from Marc-Andre Tousignant

3. RouteMap.py modeler is fixed in my github repository

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 03 Jul 2012 21:21:56 GMT

Hi Marc,

thanks for your posts, fill free to extend README for this ZenPack. I've fixed modeler error in my github repository.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 04 Jul 2012 14:12:00 GMT

Hi,

after installing MS SQL Database Monitor 3.2 and many, many tries, I'm pretty much stuck. Basically I get the same errors like geotek. However,

I can connect to the remote server from the comman - line with isql. I can even execute commands like sp_helpdb on the isql command-line. Modeling a device from ZenOSS gives:

2012-07-04 15:56:55,079 WARNING zen.SQLClient: Error in community.sql.MsSqlDatabaseMap query "SET NOCOUNT ON

DECLARE @InstanceName nvarchar(50)

DECLARE @value VARCHAR(100)

DECLARE @RegKey nvarchar(500)

SET @InstanceName=RTRIM(CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER')))

IF (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8

BEGIN

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',

@value_name = @InstanceName,

@value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll'

END

ELSE

BEGIN

IF @InstanceName='MSSQLSERVER'

BEGIN

SET @RegKey='SOFTWARE\Microsoft'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP'

END

ELSE

BEGIN

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP'

END

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'TcpPort',

@value = @value OUTPUT

IF (@value IS NULL)

BEGIN

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'TcpDynamicPorts',

@value = @value OUTPUT

END

SELECT

@InstanceName AS InstanceName,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('Edition'))) AS Edition,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('LicenseType'))) AS LicenseType,

RTRIM(CONVERT(Int, SERVERPROPERTY('NumLicenses'))) AS NumLicenses,

RTRIM(CONVERT(Int, SERVERPROPERTY('ProcessID'))) AS ProcessID,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('ProductVersion'))) AS ProductVersion,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('ProductLevel'))) AS ProductLevel,

CONVERT(Int, isnull(@value, 1433)) as PortNumber,

@@version AS Version": [ISQL]ERROR: Could not SQLConnect

2012-07-04 15:56:57,153 WARNING zen.SQLClient: Error in community.sql.MsSqlDatabaseMap query "sp_helpdb": [ISQL]ERROR: Could not SQLConnect

2012-07-04 15:56:57,157 INFO zen.SQLClient: SQL client finished collection for 10.208.8.5

2012-07-04 15:56:57,157 WARNING zen.PythonClient: Error in community.sql.MsSqlDatabaseMap: [ISQL]ERROR: Could not SQLConnect

...and yes, I already tried version 3.3 form the git-repository. Same result. I have not the slightest idea, how to track down the problem and any hep would be appreciated.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 06 Jul 2012 15:54:38 GMT

O.K., I gave up on pyisqldb and switched to pymssql which seems to work at least on some SQL servers. Installing pymssql on the Zenoss 3.2.1 OpenSuse Stack wasn't straightforward either, so here is what I did:

/usr/local/zenoss/python/bin/easy_install-2.6 Cython

/usr/local/zenoss/python/bin/easy_install-2.6 pyrex

/usr/local/zenoss/python/bin/easy_install-2.6 pymssql

(One of these needed to be executed as root and did not work as zenoss user)

Then changed zMsSqlConnectionString to

'pymssql',host='${dev/manageIp}:${here/port}',database='master',user='${here/zWinUser}',password='${here/zWinPassword}',timeout=10

RE: MS SQL Database Monitor

community@zenoss.org — Sat, 07 Jul 2012 19:50:13 GMT

you can try to install pyodbc

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 10 Jul 2012 10:07:02 GMT

Hello,

I treid pyodbc, but basically the result is the same. I can connect to the database from the command-line using isql or tsql. I completely uninstalled the MS SQL DB Monitor -ZenPack and reinstalled it. Modelling the device still results in the error messages posted above (...SET NOCOUNT OFF....). The only difference I can see is related to the use of pyisqldb or pyodbc during the access of sp_helpdb:

a. using pyisqldb

CONVERT(Int, isnull(@value, 1433)) as PortNumber,

@@version AS Version": [ISQL]ERROR: Could not SQLConnect

2012-07-10 11:52:51,107 WARNING zen.SQLClient: Error in community.sql.MsSqlDatabaseMap query "sp_helpdb": [ISQL]ERROR: Could not SQLConnect

2012-07-10 11:52:51,108 INFO zen.SQLClient: SQL client finished collection for ....

b. using pyodbc

@@version AS Version": ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

2012-07-10 11:55:40,254 WARNING zen.SQLClient: Error in community.sql.MsSqlDatabaseMap query "sp_helpdb": ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

Any idea?

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 12 Jul 2012 13:12:24 GMT

Hi abdcore,

1. try to change "ansi" parameter to "False" in connection string.

2. run: zenperfsql run -v10 --showconnectionstring -d yourserverhere

3. check in zenperfsql.log correctness of used credentials (some special symbols in password)

4. try to use another sql or windows domain account.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 13 Jul 2012 13:49:42 GMT

Hi bigegor,

thank you very much for your input. However, the error is still the same. I would be happy to share the output of the zenperfsql command. Let me know where to put it (it's a rather long output). Please keep in mind that I can connect from the commanline using isql with the same credentials.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 13 Jul 2012 14:39:27 GMT

Hi adbcore,

check this: http://stackoverflow.com/questions/9723656/whats-causing-unable-to-connect-to-data-source-for-pyodbc

try another TDS_Version values:

4.2 - Sybase SQL Server < 10 and Microsoft SQL Server 6.5

5.0 - Sybase SQL Server >= 10

7.0 - Microsoft SQL Server 7.0

7.1 - Microsoft SQL Server 2000

7.2 - Microsoft SQL Server 2005

PS: all you tests you must run as zenoss user.

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 17 Jul 2012 20:35:04 GMT

Hi bigegor,

thx for the fix, it's really appreciated !

If i can add anything usefull to the readme, i will do it!

Marc

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 18 Jul 2012 07:44:42 GMT

Hi bigegor,

following your general idea about TDS-version and the link you mentioned revealed a couple of interesting details. I somehow found a solution, but not the real source of the problem:

tsql -C showed that tsql was compiled only with TDS_Version 5.0 support. After recompiling tsql the tool is working now.
Checking freetds.conf, odbc.ini, odbcinst.ini showed no (obvious) problems
running "isql <DSN> <username> <password> " works fine (with DSN configured in odbc.ini )
running "tsql -S <DB-Server> -U <username> -P <password> works fine (with DB-Server configured in freetds.conf)
running pyodbc from zendmd:

pyodbc.connect( 'DRIVER={FreeTDS};Server=<ip-address>;Database=<DSN>;UID=<username>;PWD=<password>;TDS_Version=7.2')

failed with "unable to connect to data source".

After reading many articles on the net related to configuring pyodbc I recognized that some users where putting the port-number into the connect-string, so I tried

pyodbc.connect( 'DRIVER={FreeTDS};Server=<ip-address>;Database=<DSN>;UID=<username>;PWD=<password>;TDS_Version=7.2;Port=1433;')

*BINGO* I was connected to the database.

After that I added the port to zMsSqlConnectionString I remodeling the device worked without a problem. I entries in Components for 'Databases' and 'DB Server Instances'.

So, there're some questions left:

First, Why do I have to add the port-number? The port number is defined in freetds.conf and odbc.ini.
The database-parameter in zMsSqlConnectionString seems to be ignored somehow since I'm seeing all databases on the server under 'Databases'
While I can see details ("Display -> Details") about each database-instance there's no performance data being displayed, like "total bytes" and "used bytes" all remain "0" and so on.
The performance graphs do not show up. All my Windows Devices are under /CIM/WMI and the performance graphs are only displayed when overriding the template binding so that MsSqlSrvInst - template is also bound to the device. This has already be mentioned by whytee and it would be good have this stated somewhere ("Installation Notes".....) However, even when overriding the template binding, the graphs remain empty.
I can see events '42S02', "[42S02] [FreeTDS][SQL Server]Invalid object name 'sysperfinfo'. (208) (SQLExecDirectW)")

Any idea how to fix this and get the monitoring fully working?

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 18 Jul 2012 21:55:15 GMT

Hi adbcore,

1. first of all search for kb265808 from M$ (How to connect to a named instance)

2. you must add instance names to zMsSqlSrvInstances zProperty (one per line) OR add port option to connection string.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 19 Jul 2012 11:51:44 GMT

Hi bigegor,

maybe my database knowledge is just not sufficient. I think that I messed up the meaning of the 'Database' parameter an zMsSqlServerInstance. I put "master" into the connection string and immediately I got performance data. So that's all working now :-)

Just a suggestion: Perhaps you could provide a document with configuration examples. That would help a lot.

Thanks for your help.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 16 Aug 2012 11:04:30 GMT

what's the compatibility with Zenoss core 4 on this package? ready to install?

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 03 Sep 2012 12:03:06 GMT

Hi.

I installed zenpack.

model device getting error Error in community.sql.MsSqlDatabaseMap query "sp_helpdb": No data available. execute() first.

help me fix it please.

Graphics are not created

RE: MS SQL Database Monitor

community@zenoss.org — Mon, 03 Sep 2012 15:39:42 GMT

Hi Evgeny,

can you run 'sp_helpdb' from MS SQL Server Management Studio?

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 04 Sep 2012 08:57:14 GMT

Hi, bigegor.

Yes, Query executed successfully.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 20 Sep 2012 02:48:21 GMT

Hi Egor,

Thanks for your ongoing work on this zenpack, I've got a slightly different problem that I hope you might be able to help with...

I'm able to model my database server just fine, with all of my databases appearing correctly as components. I seem to be collecting performance data ok also, I just don't seem to be able to store it. On your earlier advice I ran zenperfsql in debug:

zenperfsql run -v10 --showconnectionstring -d <myserver>

DEBUG zen.collector.scheduler: Task <myserver> 300 -880621480146280204 changing state from IDLE to RUNNING

DEBUG zen.collector.scheduler: Task <myserver> 300 -880621480146280204 changing state from RUNNING to CONNECTING

DEBUG zen.zenperfsql: Creating pyodbc executor object

DEBUG zen.zenperfsql: Connected to <myserver> [10.x.x.x]

DEBUG zen.collector.scheduler: Task <myserver> 300 -880621480146280204 changing state from CONNECTING to FETCH_DATA

...

...

DEBUG zen.collector.scheduler: Task <myserver> 300 -880621480146280204 changing state from FETCH_DATA to PARSING_DATA

DEBUG zen.collector.scheduler: Task <myserver> 300 -880621480146280204 changing state from PARSING_DATA to STORE_PERF_DATA

DEBUG zen.collector.scheduler: Task <myserver> 300 -880621480146280204 finished, result: <twisted.python.failure.Failure <type 'exceptions.TypeError'>>

DEBUG zen.collector.scheduler: Task <myserver> 300 -880621480146280204 changing state from STORE_PERF_DATA to IDLE

As you can see, the collection of data seems ok, but I get a TypeError exception back from Twisted when the perf data is stored.

I'm on Zenoss 3.1.0, (unable to upgrade to 3.2.x due to process monitoring bugs), I'm running the latest version of the various ZenPacks, (MsSQLMon, RDBMS, SQLDataSource) and I've restarted zenoss a number of times. This behaviour is the same using pyodbc and pyisqldb.

I noticed in version 2.2 of the SQLDataSource zenpack you fixed 'parsing of empty results' - not sure if that's related to this problem?

Thanks, James.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 20 Sep 2012 12:01:47 GMT

I too am having problems with storing data. I was on 2.2 but upgraded to 2.4 a couple of days ago. Since then graphs have not been updated reliably (occasionally I get a little blip on graphs but they are hours apart). For now I'm going to roll back to 2.2.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 20 Sep 2012 15:14:17 GMT

It actually looks like my problem is different. For some reason zenperfsql is not getting through all of the devices. This is happening with 2.2 and 2.4. Still trying to figure out why.

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 20 Sep 2012 16:53:57 GMT

Hi James,

which SQL Data Source version (number) are you using?

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 20 Sep 2012 22:28:41 GMT

Hi Egor, I'm on the following versions:

Zenoss 3.1.0

ZenPacks.community.MsSQLMon 3.3

ZenPacks.community.RDBMS 2.4

ZenPacks.community.SQLDataSource 2.4

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 25 Sep 2012 17:34:46 GMT

Hi James,

try to change line 482 in zenperfsql.py from:

if int(ZVERSION[0]) > 2:

to:

if int(ZVERSION[0]) > 4:

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 26 Sep 2012 00:28:46 GMT

Brilliant Egor, that did the trick!...:-). Thanks as always for your help.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 17 Oct 2012 07:08:25 GMT

I am using Zenoss core 3.1 and ubuntu 64bit Desktop version. I want to monitor MS SQL 2008 databases so i have install below zenpacks along with their dependencies i -e FreeTDS and ODBC driver as by below.

etc/freetds/freetds.conf

[SQLTEST]

host = SQLTEST

port = 1433

instance =

tds version = 8.0

etc/odbcinst.ini

[FreeTDS]

Description = TDS driver (Sybase/MS SQL)

Driver = libtdsodbc.so

Setup = libtdsS.so

CPTimeout =

CPReuse =

UsageCount = 1

etc/odbc.ini

[SQLTEST]

Driver = FreeTDS

Servername = SQLTEST

Port = 1433

and able to connect sql via isql on zenoss machine as well.

root@NMS:~# isql -v SQLTEST Domain/User Password

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

and install mention below zenpacks

ADVANCED DEVICE DETAILS

RDBMS MONITORING

SQL DATA SOURCE

MS SQL DATABASE MONITOR

Then i configure zenoss server properties as below.

ZWinuser = User Password

ZWinpassword = password

zMsSqlSrvInstances = Servername

String =default

But i unable to get the desire graphs for SQL Server.

RE: MS SQL Database Monitor

community@zenoss.org — Wed, 17 Oct 2012 16:43:43 GMT

Hi arshadkhan20,

1. here is ZenPacks Documentation: https://github.com/epuzanov/ZenPacks.community.MsSQLMon/blob/master/README.rst

2. zMsSqlSrvInstances must contain the list of MS SQL Server instances names or TCP ports not a servername (please read MS SQL documentations: "Named instances") or it can be empty string too (try empty string first).

3. zWinUser must contain AD or SQL user name

RE: MS SQL Database Monitor

community@zenoss.org — Thu, 18 Oct 2012 07:45:07 GMT

hi begegor,

I have set credential as under.

ZWinsuer = Domain\username

ZWinpassword = password

zMsSqlSrvInstances = SQLTEST \ MSSQLSERVER

String = string is remove.

Then following log massage will generate as i am pasting you for your reference.

2012-10-18 12:40:10,574 INFO zen.ZenModeler: Connecting to localhost:8789

2012-10-18 12:40:10,586 INFO zen.ZenModeler: Connected to ZenHub

2012-10-18 12:40:10,621 INFO zen.ZenModeler: Collecting for device 10.10.0.74

2012-10-18 12:40:10,716 INFO zen.ZenModeler: WMI collector method for device 10.10.0.74

2012-10-18 12:40:10,717 INFO zen.ZenModeler: plugins: zenoss.wmi.WinServiceMap

2012-10-18 12:40:10,722 INFO zen.ZenModeler: Python collection device 10.10.0.74

2012-10-18 12:40:10,723 INFO zen.ZenModeler: plugins: community.sql.MsSqlDatabaseMap

2012-10-18 12:40:10,728 INFO zen.ZenModeler: No command plugins found for 10.10.0.74

2012-10-18 12:40:10,739 INFO zen.ZenModeler: SNMP collection device 10.10.0.74

2012-10-18 12:40:10,739 INFO zen.ZenModeler: plugins: community.snmp.DatabaseMap, zenoss.snmp.NewDeviceMap, zenoss.snmp.DeviceMap, zenoss.snmp.InterfaceMap, zenoss.snmp.RouteMap, zenoss.snmp.IpServiceMap, zenoss.snmp.HRFileSystemMap, zenoss.snmp.HRSWInstalledMap, zenoss.snmp.HRSWRunMap, zenoss.snmp.InformantHardDiskMap

2012-10-18 12:40:10,798 INFO zen.ZenModeler: No portscan plugins found for 10.10.0.74

2012-10-18 12:40:10,799 INFO zen.ZenModeler: Running 3 clients

2012-10-18 12:40:11,153 INFO zen.ZenModeler: Processing WinServices for device 10.10.0.74

2012-10-18 12:40:11,904 INFO zen.SnmpClient: snmp client finished collection for 10.10.0.74

2012-10-18 12:40:11,906 INFO zen.ZenModeler: processing community.snmp.DatabaseMap for device 10.10.0.74

2012-10-18 12:40:11,907 INFO zen.ZenModeler: Processing zenoss.snmp.NewDeviceMap for device 10.10.0.74

2012-10-18 12:40:11,908 INFO zen.ZenModeler: processing zenoss.snmp.DeviceMap for device 10.10.0.74

2012-10-18 12:40:11,910 INFO zen.ZenModeler: Modeler zenoss.snmp.InterfaceMap processing data for device 10.10.0.74

2012-10-18 12:40:11,911 INFO zen.ZenModeler: processing zenoss.snmp.RouteMap for device 10.10.0.74

2012-10-18 12:40:11,913 INFO zen.ZenModeler: processing zenoss.snmp.IpServiceMap for device 10.10.0.74

2012-10-18 12:40:11,914 INFO zen.ZenModeler: Modeler zenoss.snmp.HRFileSystemMap processing data for device 10.10.0.74

2012-10-18 12:40:11,915 INFO zen.ZenModeler: Skipping Physical Memory (ram) as it matches zFileSystemMapIgnoreTypes.

2012-10-18 12:40:11,916 INFO zen.ZenModeler: processing zenoss.snmp.HRSWInstalledMap for device 10.10.0.74

2012-10-18 12:40:11,922 INFO zen.ZenModeler: Processing zenoss.snmp.HRSWRunMap for device 10.10.0.74

2012-10-18 12:40:11,926 INFO zen.ZenModeler: processing zenoss.snmp.InformantHardDiskMap for device 10.10.0.74

2012-10-18 12:40:11,973 INFO zen.ZenModeler: No change in configuration detected

2012-10-18 12:40:12,311 INFO zen.ZenModeler: No change in configuration detected

2012-10-18 12:40:12,730 WARNING zen.SQLClient: Error in community.sql.MsSqlDatabaseMap query "SET NOCOUNT ON

DECLARE @InstanceName nvarchar(50)

DECLARE @value VARCHAR(100)

DECLARE @RegKey nvarchar(500)

SET @InstanceName=RTRIM(CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER')))

IF (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8

BEGIN

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',

@value_name = @InstanceName,

@value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll'

END

ELSE

BEGIN

IF @InstanceName='MSSQLSERVER'

BEGIN

SET @RegKey='SOFTWARE\Microsoft'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP'

END

ELSE

BEGIN

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP'

END

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'TcpPort',

@value = @value OUTPUT

IF (@value IS NULL)

BEGIN

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'TcpDynamicPorts',

@value = @value OUTPUT

END

SELECT

@InstanceName AS InstanceName,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('Edition'))) AS Edition,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('LicenseType'))) AS LicenseType,

RTRIM(CONVERT(Int, SERVERPROPERTY('NumLicenses'))) AS NumLicenses,

RTRIM(CONVERT(Int, SERVERPROPERTY('ProcessID'))) AS ProcessID,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('ProductVersion'))) AS ProductVersion,

RTRIM(CONVERT(Char(128), SERVERPROPERTY('ProductLevel'))) AS ProductLevel,

CONVERT(Int, isnull(@value, 1433)) as PortNumber,

@@version AS Version": __init__() takes at least 2 arguments (1 given)

2012-10-18 12:40:12,734 WARNING zen.SQLClient: Error in community.sql.MsSqlDatabaseMap query "sp_helpdb": __init__() takes at least 2 arguments (1 given)

2012-10-18 12:40:12,735 INFO zen.SQLClient: SQL client finished collection for 10.10.0.74

2012-10-18 12:40:12,735 WARNING zen.PythonClient: Error in community.sql.MsSqlDatabaseMap: __init__() takes at least 2 arguments (1 given)

2012-10-18 12:40:12,736 INFO zen.PythonClient: Python client finished collection for 10.10.0.74

2012-10-18 12:40:12,736 INFO zen.ZenModeler: No change in configuration detected

2012-10-18 12:40:12,737 INFO zen.ZenModeler: Scan time: 2.12 seconds

2012-10-18 12:40:12,747 INFO zen.ZenModeler: Daemon ZenModeler shutting down

RE: MS SQL Database Monitor

community@zenoss.org — Tue, 15 Jan 2013 00:18:53 GMT

Unfortunately, in 2008 xp_regread requires the sql admin role. That kind of ruins the idea of having a low-privilege user to fetch the database information. I wonder if any of the rest of the ZenPack actually needs an sql admin role.

There's not really a good way of getting the instance and port other than writing a CLR function. You could just check to see if the instance name is specified in zMsSqlSrvInstances and maybe have a zMsSqlSrvPort variable for the port. If these are specified, I don't think you need to read anything out of the registry and a non-administrative user could be used (as long as nothing else about the zenpack required admin rights).

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 01 Feb 2013 23:31:02 GMT

Another shortcoming I have run into, though I'm not sure what the fix should be. When I restart a sql server, I get an event like this for every single database:

('08S01', '[08S01] [unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)')

as EventClass = /Status/MsSqlDatabase and six times as /Status/MsSqlSrvInst (one each for sysperfinfo, LockRequests, LockTimeouts, SQL Errors, NumberOfDeadlocks, LockWaitTime). Since we have 56 databases, we got 62 separate events. That's a lot of events.

I'll probably set it up so it drops any "Unable to connect" events for the databases. And probably all "Unable to connect" events for /Status/MsSqlSrvInst for anything but sysperfinfo.

But perhaps there is a better way to handle this in the zenpack.

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 29 Mar 2013 08:20:36 GMT

I'm trying to use this ZenPack to monitor my database.

I tried to search online before posting about my problem.

I'm pretty sure I have configured everything ok: I can connect to my database server using tsql, isql and osql. No error reported even when I model the device

Today, after 2 days, I still can't see part of the data I expeted.

When I click on the Databases menu on the left (34 database on this database server is the correct number), a warning appear at the top of the window "ValueError: cannot convert float NaN to integer", so I can't click on the database name to see its graph

Am I missing something?

Is there something I can check about this error

Thanks for any help

EDIT: /opt/zenoss/log report this error

------
2013-03-29T13:44:50 INFO extdirect Direct request failed: cannot convert float NaN to integer: DeviceRouter.getComponents [{'sort': 'name', 'uid': '/zport/dmd/Devices/Server/Windows/HP/MS SQL Server Fisico/devices/server-edi-es.energeticsource.local', 'keys': ['uid', 'severity', 'status', 'dbSrvInst', 'name', 'type', 'totalBytesString', 'usedBytesString', 'capacity', 'locking', 'usesMonitorAttribute', 'monitored', 'monitor', 'uuid', 'uid', 'meta_type', 'monitor'], 'start': 0, 'meta_type': 'Database', 'limit': 50, 'page': 1, 'dir': 'ASC', 'name': None}]
------
2013-03-29T13:44:50 INFO extdirect DirectRouter suppressed the following exception (Response 6c4ba844-6706-4f0b-9114-7aacc5acabed):
Traceback (most recent call last):
File "/opt/zenoss/Products/ZenUtils/extdirect/router.py", line 179, in _processDirectRequest
    response.result = _targetfn(**data)
File "<string>", line 2, in getComponents
File "/opt/zenoss/Products/Zuul/decorators.py", line 115, in serviceConnectionError
    return func(*args, **kwargs)
File "/opt/zenoss/Products/Zuul/routers/device.py", line 187, in getComponents
    data = Zuul.marshal(comps, keys=keys)
File "/opt/zenoss/Products/Zuul/__init__.py", line 128, in marshal
    marshalled_list.append(marshal(o, keys, marshallerName, objs))
File "/opt/zenoss/Products/Zuul/__init__.py", line 101, in marshal
    return marshal(marshaller.marshal(keys),
File "/opt/zenoss/Products/Zuul/marshalling.py", line 79, in marshal
    data = _marshalExplicitly(self._obj, _keys)
File "/opt/zenoss/Products/Zuul/marshalling.py", line 53, in _marshalExplicitly
    value = getattr(obj, key)
File "/opt/zenoss/ZenPacks/ZenPacks.community.RDBMS-2.4.egg/ZenPacks/community/RDBMS/info.py", line 60, in capacity
    cap = self._object.capacity()
File "/opt/zenoss/ZenPacks/ZenPacks.community.RDBMS-2.4.egg/ZenPacks/community/RDBMS/Database.py", line 206, in capacity
    usedBytes = long(self.usedBytes())
File "/opt/zenoss/ZenPacks/ZenPacks.community.MsSQLMon-3.3.egg/ZenPacks/community/MsSQLMon/MsSqlDatabase.py", line 140, in usedBytes
    return self.totalBytes() - sa
File "/opt/zenoss/ZenPacks/ZenPacks.community.MsSQLMon-3.3.egg/ZenPacks/community/MsSQLMon/MsSqlDatabase.py", line 133, in totalBytes
    return long(datasize + logsize)
ValueError: cannot convert float NaN to integer

Edit: I tried to dig a little deeper into this error, but since I don't know python, I just tried to look at the files reported in the stack error.

I added two lines of code

print datasize

print logsize

just before the end of the funcion totalBytes in $ZENHOME/ZenPacks/ZenPacks.community.MsSQLMon-3.3.egg/ZenPacks/community/MsSQLMon/MsSqlDatabase.py

I stopped zope with zopectl stop and restarted it with zopectl fg to see the output

There is indeed a nan value (datasize nan), but since I wanted to know which database caused that, i tried adding another line

print self.dbservinstance().dbsiname just before my previous debug line print datasize

Stopped and started zope again and this time I don't see the output I expected (the values from the debug lines I added), but I also don't seem to be affected by nan value. The GUI shows me my databases and all their properties.

As I said I'm not a python guru, so I don't know why this is happening, but I think I will leave this debug line inside the code, at least till someone can provide the correct solution (and maybe it will help someone else in the meantime)

RE: MS SQL Database Monitor

community@zenoss.org — Fri, 12 Apr 2013 08:56:17 GMT

Hi,

Thanks for this great Zenpack!

I added an option to trigger an alarm on a capacity percentage of databases that reach 80% and wanted to share this.

I must add that my SQL knowledge is not so good, and I know this could have been done better .

I Added this code to the data source sysperfinfo from the MsSqlDataBaseMap monitoring template under the SpaceAvailable variabele.

(100 - ((CASE WHEN [Data File(s) Size (KB)] IS NULL THEN NULL WHEN (([Data File(s) Size (KB)] * 1024) >= tt.UnallocatedSpace) THEN (([Data File(s) Size (KB)] * 1024) - tt.UnallocatedSpace) ELSE 0 END)/((([Log File(s) Size (KB)] * 1024) + ([Data File(s) Size (KB)] * 1024))/100))) AS UsedSpacePerc,

Added the datapoint UsedSpacePerc under sysperfinfo and created a threshold.

Cheers,

Martijn