SQL Server 2000 Connections via WMI

Templates, scripts for templates, scripts and requests for templates.

Moderators: Moderators, Developers

Author
Message
DevilSun
Cacti User
Posts: 166
Joined: Fri Jul 16, 2004 1:59 pm
Location: Oregon

SQL Server 2000 Connections via WMI

#1 Post by DevilSun » Mon Oct 18, 2004 11:16 am

As the title says, SQL Server 2000 connections...

Usage:

Code: Select all

perl <path_cacti>/scripts/w32_sql_query_connections.pl <hostname> get UserConnections,LoginsPersec,LogoutsPersec
*NOTE - I haven't found Logins/Logouts per sec to be very useful in my scenario so I am not graphing them.

Addendum - If you're using SQL 2000 on Windows 2000, you need to change the script class to "Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics" to make it work. Thanks to chewy009 for this info.
Attachments
screen.gif
Screenshot
screen.gif (6.47 KiB) Viewed 48272 times
w32_sql_query_connections.pl.txt
Perl script
(5.17 KiB) Downloaded 3902 times
cacti_graph_template_sql_connections.xml
Graph template
(4.04 KiB) Downloaded 3351 times
Last edited by DevilSun on Mon Nov 28, 2005 2:12 pm, edited 1 time in total.

JVandermark
Posts: 14
Joined: Tue Dec 14, 2004 9:54 am

#2 Post by JVandermark » Thu Dec 30, 2004 3:18 pm

I imported this template into cacti and go the following:
[success] SQL - Connections [update]
+ Unmet Dependency: (Data Template Item)
+ Found Dependency: (GPRINT Preset) Normal
+ Unmet Dependency: (Data Template Item)
+ Found Dependency: (GPRINT Preset) Exact Numbers
+ Unmet Dependency: (Data Template Item)
+ Unmet Dependency: (Data Template Item)

Looking in the data input methods section, there is nothing for SQL- Connections. I'm going to try and figure it out, but if you get a chance can you look into it?

User avatar
pshankland
Cacti User
Posts: 247
Joined: Fri Dec 31, 2004 7:07 am
Location: UK

#3 Post by pshankland » Tue Jan 04, 2005 3:02 pm

Think me thick but I have managed to import the XML via 'Import Templates' and I have copied the *.pl files into <cacti_install>\scripts\*.*

My question is.... what do I have to do then??

Thanks.

DevilSun
Cacti User
Posts: 166
Joined: Fri Jul 16, 2004 1:59 pm
Location: Oregon

#4 Post by DevilSun » Tue Jan 04, 2005 5:50 pm

Here is the data template, it should be included in the graph template though so I dunno...
Attachments
cacti_data_template_sql_connections.xml
Data template for SQL connections...
(5.48 KiB) Downloaded 2485 times

DevilSun
Cacti User
Posts: 166
Joined: Fri Jul 16, 2004 1:59 pm
Location: Oregon

#5 Post by DevilSun » Tue Jan 04, 2005 5:57 pm

pshankland wrote:Think me thick but I have managed to import the XML via 'Import Templates' and I have copied the *.pl files into <cacti_install>\scripts\*.*

My question is.... what do I have to do then??

Thanks.
Go create a Data Input Method, of type "Script/Command", input string like

Code: Select all

perl <path_cacti>/scripts/w32_sql_query_connections.pl <hostname> get UserConnections,LoginsPersec,LogoutsPersec
and setup the fields as shown. You gotta match up the fields and what not...
Attachments
sql_di.gif
Data Input screenshot
sql_di.gif (10.76 KiB) Viewed 47895 times

User avatar
pshankland
Cacti User
Posts: 247
Joined: Fri Dec 31, 2004 7:07 am
Location: UK

#6 Post by pshankland » Wed Jan 05, 2005 4:11 am

Cheers for the reply. I have done what you have said but the graph never gets generated - I just get the red-cross thingy.

I have imported the Graph Template OK and I have then created a Data Input Method as per your instructions but still get no graph. Any suggestions??

Thanks.

Code: Select all

perl <path_cacti>/scripts/w32_sql_query_connections.pl <hostname> get UserConnections,LoginsPersec,LogoutsPersec
Attachments
WMI SQL - Graph Template.jpg
Graph Template Screen-Shot
WMI SQL - Graph Template.jpg (164.69 KiB) Viewed 47872 times
WMI SQL - Data Input Methods.jpg
Data Input Method Screen-Shot
WMI SQL - Data Input Methods.jpg (91.05 KiB) Viewed 47872 times

DevilSun
Cacti User
Posts: 166
Joined: Fri Jul 16, 2004 1:59 pm
Location: Oregon

#7 Post by DevilSun » Wed Jan 05, 2005 4:21 pm

I see the problem. This is that data source disconnect problem that happened to me a version or two ago, a lot of my templates got hosted in that nature, and I've rebuilt quite a few, but some don't want to be fixed.

Here's what I believe you need to do. Go edit the graph template. See where it says "(No task)" next to each graph item under the Data Source column? That means, that graph item is not associated with the data source. My suggestion would be to delete the existing Graph Item input data source, and then go to each graph item itself, and select the Data Source. The reason I say to delete the existing Graph Item Input data source ("Data Source [user_count]") is because as soon as you add it again to the graph item, it'll duplicate the existing one and you won't know which to delete. For each graph item, set the data source to "SQL - Connections - (user_count)". That should work unless there is something else going on.

pbulteel
Cacti User
Posts: 150
Joined: Fri Sep 05, 2003 9:20 am
Location: London
Contact:

This only works if cacti is on windows -right?

#8 Post by pbulteel » Thu Jan 06, 2005 9:09 am

I'd like to use this, but I'd be polling from a linux host...

Would that mean I'd install activeperl on the sql server and ....

Hmm... I might have to do something like what's mentioned in

http://forums.cacti.net/about4670.html

-P
uname -a

Thomas
Cacti User
Posts: 112
Joined: Thu Dec 09, 2004 1:51 pm
Location: Austria

#9 Post by Thomas » Fri Jan 07, 2005 5:59 am

If you want to monitor Windows Servers using WMI from Unix/Linux please have a look at http://forums.cacti.net/viewtopic.php?t=5842.
I have summarized all possible ways (as far as I know them) to get WMI information from Unix/Linux.
Thomas Rupp

Riceman
Posts: 21
Joined: Thu Oct 14, 2004 2:39 pm

#10 Post by Riceman » Mon Jan 10, 2005 7:58 pm

I have a set of clustered MSSQL 2000 \ Win2k servers that both respond to WMI queries for cpu and mem etc, but when I query using this perl script I get this.

Any ideas?

Check the computer and class name.
No information was found on the specified class!

Thanks in advance,


Nick

User avatar
pshankland
Cacti User
Posts: 247
Joined: Fri Dec 31, 2004 7:07 am
Location: UK

#11 Post by pshankland » Tue Jan 11, 2005 5:16 am

I know this script is for WMI but just as a word of caution if you want to monitor Microsoft SQL Servers that are clustered using SNMP...... DON'T!! It seems that the Intel SNMP agent can cause a load of problems that you really don't want in a clustered environment!

http://support.microsoft.com/default.as ... -us;811876

Cheers.

Pete.

Riceman
Posts: 21
Joined: Thu Oct 14, 2004 2:39 pm

#12 Post by Riceman » Tue Jan 11, 2005 10:27 am

Roger that, I am just running the windows SNMP service on these machines. Thanks for the heads up.

I was wondering if I need to install the mof files for SQL since my machines think they don't have this class needed for this application.


Thanks,


Nick

DevilSun
Cacti User
Posts: 166
Joined: Fri Jul 16, 2004 1:59 pm
Location: Oregon

#13 Post by DevilSun » Tue Jan 11, 2005 4:24 pm


Riceman
Posts: 21
Joined: Thu Oct 14, 2004 2:39 pm

#14 Post by Riceman » Tue Jan 11, 2005 4:46 pm

Thanks for the help bro. Looks like it might be my problem.


Thanks again,


Nick

Jvona
Posts: 3
Joined: Fri May 27, 2005 11:39 am

#15 Post by Jvona » Fri Aug 12, 2005 8:08 pm

Did you ever figure anything out with this, niether of these solutions helped, I even reinstalled sql making sure snmp was enabled before install.

When running the script I get the same error as Riceman. Any suggestions are much appreciated.

Thanks!

Jason

Post Reply