OpenEye - Lighting fast data collection process and real-time dashboard for on-premises SQL Server

CLI of OpenEye has now several command line parameters and options. These parameters can be used using “/” (slash) or “-“ (dash) followed by a switch name and value.

Older post for more information:
OpenEye - Seeing what others cannot see
https://sqltouch.blogspot.com/2021/04/openeye-seeing-what-others-cannot-see.html
OpenEye - effcient way monitoring Windows and SQL Server performance
https://sqltouch.blogspot.com/2023/10/openeye-effcient-way-monitoring-windows.html

Download Link: https://bit.ly/3t5kj1l
Update: 2024.01.10 (January 10, 2024) 

Step-by-step OpenEye Deployment:

Step # 1: Install and configure required components.

• Download the zipped file from the link above and extract in a folder
• Download and install latest TimescalDB. Alternatively, use docker container for TimescalDB
• Create a database named openeyedb.
• Create a user who will have read and write access to the openeyedb
• Download and install latest Grafana.
• Import dashboard provided in the zipped file.
• Create a data source as openeyedb.
  

Step#2: Using OpenEye as data collector process:

• Open the command prompt as administrator
• Go to the download folder and simply run:

          C:\download>openeye.exe

• A openeye.ini is created. Change the PostgresSQL server, username and password.
• We need to create tables in openeyedb. Execute the following command:
  

          C:\download>openeye /create

• To convert PostgreSQL table as timescaledb hypertable, execute the following command: 
  

          C:\download>openeye /hypertable

• To create data retention period for all hypertables in openeyedb, use the following command:

           C:\download>openeye /retention

• To truncate all data, use the command: C:\download>openeye /truncate

• To test OpenEye configuration and validity of data collection process for a remote SQL server for the first time, say SRVDEP312, execute the following command:

          C:\download>openeye -s=SRVDEP312 -d=5000 -g=test

• To collect data permanently, use the following syntax. Remove “-g=test” or “/get=test”

          C:\download>openeye -s=SRVDEP312 -d=5000

• Create a Windows Schedular task OpenEye CLI instead running on the command prompt.

Get help from OpenEye command line:
On the command prompt, type:
C:\>openeye /help

This will provide all available switches.

Show example of OpenEye CLI:
To see various examples, type:
C:\> openeye /example

All available OpenEye command line switches:
To get all available command line switches, on the command prompt, type:

C:\>openeye /help or
C:\>openeye /?

Output will be similar to the following:

How to collect Windows and SQL Server performance data?
OpenEye is designed to collect performance data remotely without deploying any other components. To collect data from a remote server, say WinSrv001 use the following syntax:

To collect Windows performance data:
C:\>openeye /server=WinSrv001 /target=win /duration=5000

To collect SQL Server Performance data without SQL Agent:
C:\>openeye /server=WinSrv001 /target=SQL /duration=5000, or
C:\>openeye -s=WinSrv001 -t=SQL -d=5000 

To collect SQL Server Performance data with SQL Agent:
C:\>openeye /server=WinSrv001 /target=SQL\AGENT /duration=5000, or
C:\>openeye -s=WinSrv001 -t=SQL\AGENT -d=5000

Permission requirements:
OpenEye uses Windows Authentication only, the process or person who executes the OpenEye must have WMI read permission on the local and on the remote server.

On the SQL Server required permission are VIEW SERVER STATE and SELECT permission on master and msdb database on the target (local or remote).

Data collection from a remove server and non-default SQL port:
To collect data from a remote SQL Server, say SrvSQL001, execute the following:

C:\> openeye /server= SrvSQL001 /target=sql\agent /port=3451, or
C:\> openeye -s= SrvSQL001 -t=sql\agent -p=3451

Data collection from a named instance of SQL Server:
To collect data from a named instance of a SQL Server on remote (or local) server, execute following:

C:\> openeye /server=WinSrv012\FinanceSql /target=sql\agent /port=4433, or
C:\> openeye -s=WinSrv012\FinanceSql -t=sql\agent -p=4433

Data collection every 15 seconds (15000 milliseconds):
To collect data every 15 seconds, use the following command. Default collection interval is 10 seconds (1000 milliseconds):

C:\> openeye /duration=15000 /server= SrvSQL001 /target=sql\agent, or
C:\> openeye -d=15000 -s= SrvSQL001 -t=sql\agent

Continuous data collection process:
Create a Windows Scheduler Task to collect data with any CLI option from the example. Use a user name and password who has WMI and SQL Server access.