NetSight – External MySQL Queries

22nd September 2016 by Martin Flammia

Filed under Extreme Networks Configuration, Uncategorised

Last modified 26th September 2016

Recently had a query for a set of port statistics that could not be produced currently, and directly from the OneVeiw interface. This was to produce a report on all the ports at once on minimum, maximum and average utilisation.

The information would be in the database, so it should be just a matter of formulating the query and exporting the data – which is where the use of something like MySQL Workbench would come in handy. Fortunately there was a post on the Extreme Networks Hub forum here, that showed how to do it, of which I have given the full detail below.

Create External DB Access

Log into Netsight and run the command below where XXXXX is the password shown in the database backup link. The database backup link is shown in the image below and can be seen by clicking the purple square in the icon bar (Server Information), clicking the ‘Database’ tab and ticking the ‘Show Password’ box.

netsight_database

Then go into the NetSight database by going to this directory:

And then typing this command:

Once in enter the commands below, where YYYYY is the password you wish to give to the database access account.

(This account is not an account created anywhere else, i.e. the operating system, it only exits in the database)

Each line will return the following message:

Connect MySQL Workbench

In the example below when connecting to MySQL workbench, make sure you change the default port number of 3306 to 4589. If you get an error make sure the IP address included in the granted privileges above is the one you are connecting from.

mysql_workbench

NetSight Query

Initially looking at the database’s could be a challenge to reverse engineer and formulate a query for the non MySQL proficient like myself. What is needed is access to queries already used by NetSight, like for example to see what’s run behind the seens for something like the ‘Top 100 Interfaces by Bandwidth Daily’ report.

Fortunately with the direction of Extreme Networks GTAC there is a location were you can view all the queries run, so for example you can run the report above and see what query was used. To view this information log into Onview:

  1. Go to Administration
  2. Diagnostics
  3. Server
  4. Server Performance
  5. Select ‘SQL Execute’ from the drop-down and hit submit

sql_execute

You will then get a list of all the queries run and the variables used, for example part of the query used for the ‘Top 100 Interfaces by Bandwidth Daily’ is:

A list of parameters like:

A query like:

This would then equate to:

The ‘netsightrpt’ database turns out to be very well formulated to easily get the information we need, where the raw data has been ready collated into data for the hour, day, week and month:

  • rpt_default_hour
  • rpt_default_day
  • rpt_default_week
  • rpt_default_month

This table gives information about the port:

  • rpt_target

And a table that can be used to formulate the data in the statistic you want, with examples of the statistic number and the statistic it produces:

  • rpt_statistic

Statistic 1

Statistic 2

Statistic 3

Statistic 4

Statistic 5

Therfor a query can be written that pulls all this information together. The query below inner joins the rpt_target and rpt_default_day tables together so that the information is collated identifying port detail and the statistics required.

The time in the database is provided in milliseconds so has to be converted to seconds. To calculate the time you require in milliseconds from epoch you can use something like this website.

Statistic 3 has been used to give minimum, maximum and average utilisation, on the first 100 records.

 

Leave a Comment