MSSQL to Cacti

[ddowdy]

The company I work for has NetIQ already in place for performance collection. NetIQ uses a MSSQL 2000 backend and has terrible web-based reporting. I have been experimenting with a DTS to file then perl script to RRDTOOL process, this is becoming very combersome and the performance is not good. My question is can I query the MSSQL database directly from MYSQL and pull the data I need into Cacti, this would save me several steps and seems like it would be very efficient. Thanks for any responses.

[rony]

You could write a script that would get the information you need out of MSSQL and persent it to cacti in a way it's expecting.

Detail of how to do this, I don't know.

[TheWitness]

Adodb is your friend. However, a script would have to be written. Great project for a contributer.

TheWitness

[ddowdy]

I have a MSSQL query and perl script that formats the data for input to RRDTOOL database. Would the data be arranged the same for Cacti? If so it seems plausible for a DTS that moves the data from MSSQL directly to MYSQL.

[TheWitness]

I guess, you would need to be more specific. DTS is a good example if you are scheduling repeated transfers. I would suggest that you provide a write up as to exactly what you are doing and what you want to accomplish if you want help here.

TheWitness

[ddowdy]

I currently use NetIQ (Non-SNMP) to gather performance stats on NT/AIX/HP-UX servers and it does a good job at that. However, where most monitoring products have an excellent web interface for application groups to do their own performance research, NetIQ falls short. The company has partnered with Netuitive Inc. which has developed a good web product but they are charging $ 250 US dollars per server for it, that is not acceptable, since most products already have this feature built-in. Ok enough background, my thought has been focused on extracting the NetIQ data and use an Open Source product to generate the graphs. I have been open-minded about how to accomplish this, here is what I have tried so far. 1) MSSQL query that writes to a text file, then with a Perl script format the data for RRDTool import. This is proven too cumbersome due to the fact that I have 250 servers and collecting @ 12 performance metrics per machine and because it appears to be a high maintenance process, not too mention I still have to build some type of decent web interface. My next though was to use a product like Cacti because it would at least give me a nice looking web interface with some security framework already in place. Since it uses MYSQL I thought that it might be more efficient if I could figure out how to go directly from MSSQL to MYSQL via ADODB. I have not gone very deep into development at this point, I am more interested in finding the best and most efficient way to accomplish my goals without adding a lot of management overhead. This is still a pretty high level view but this is where I am today, thanks.

[rony]

Problem is that cacti stores the values in RRDtool files. So, you really do have to deal with the poller to effectively get the values in the RRDTool files.

[ddowdy]

Problem is that cacti stores the values in RRDtool files. So, you really do have to deal with the poller to effectively get the values in the RRDTool files.

Thanks for the clarification, from the docs it seemed that I could pump data into MYSQL without even using the snmp poller.

[TheWitness]

This ones actually pretty easy. What you will do, is as follows:

1) Create a Data Input Method (use PHP Script Server) to obtain output from a Text File. As an input parameter, you would specify a code that your script can associate with a text file. As output parameters, you would specify the variable names associated with the contents of the NetIQ data.

2) Create Data Templates for your performance data. What the Data Template will do is contain mappings of the output values in the Data Input Method to RRDtool DS names to be stored in the RRD file.

3) Create Graph Templates associated with each graph you want to create from the performance data. Nothing more to say here.

4) Create a Host Template for each host type you will be polling.

5) Add the associated Graph Templates to each.

The format of the text file that your NetIQ application will output for Cacti to poll is as follows:

Variable1:Value1 Variable2:Value2 ... VariableN:ValueN

From that, the Data Input method will populate the variables for the Data Template to draw against. The poller will execute the script, which will open the files and extract the data, store it in the poller_output table and then the poller will utilize the Data Template definition to create/update your RRD files associated with the graphs from the contents of the poller_output table.

The creation of the host will drive the synchronization of all three elements. By "Creating Graphs" associated with the Data Input Method input parameters, you will be prompted to enter the code that will be used to uniquely identify the text files.

I currently use this exact method to collect active network ports at various sites within our organization. The query takes a long time to run. Therefore, I run it outside of Cacti and generate a file that has the following format:

TotalPorts:XXXX ActivePorts:XXXX DeviceErrors:XX

If do that once an hour, but Cacti checks the file every 5 minutes.

Good Luck.

TheWitness

[ddowdy]

Excellent, Let me see if I can make this work. Again thanks for the help.