by Brian Dunbar
I have an application that writes alerts to it's MSSQL Table. I'd love it if site24x7 could tell me when certain events show up in the SQL table.
Sample below: 'Message' would be something like 'Heartbeat is unhealthy' or 'Error in processing'
select * from dbo.applicationlog;
ApplicationLogId BatchNumber Source Message LogDate Type Sequence ErrorCount BF620864-9462-E611-827A-122E68B77D56 0 NightlyMaintenance Nightly Maintenance Finished 2016-08-14 23:00:25.593 INFO 30537402
Any ideas?
Like (3)
Reply
Replies (5)
by Gibu Mathew
One option is to write a simple Windows Plugin that can run this SQL Query and return a status either via the "hearbeat" attribute of the Plugin or as, another attribute within the plugin. You may need some development experience to write this script.
Look at this article on how to write a Windows Plugin:
Please note that Site24x7 Plugins support a special "heartbeat" attribute (and mechanism) to report up and down time. This would be helpful.
ps : I would also check with an internal developer for some sample scripts.
Like (0)
Reply
The MSSQLAPPEvent plugin will notify you if such event occurs within last 5 minutes (by checking corresponding message in message field)
For adding MSSQLAPPEvent plugin please follow the steps listed below
1. Download the attached zip file and unzip MSSQLAPPEvent.zip under the Plugins folder
Eg: C:\Program Files (x86)\Site24x7\WinAgent\monitoring\Plugins
2. Execute the following PowerShell command before adding the plugin (if you have not already done that before)
Set-ExecutionPolicy RemoteSigned
3. Give sysadmin role to NT AUTHORITY\SYSTEM user by executing the following query
"EXEC sp_addsrvrolemember 'NT AUTHORITY\SYSTEM' ,'sysadmin'" or refer to the attached screenshot
4. Edit the file MSSQLAPPEvent.ps1 using a text editor and do the following changes
a. Add ServerInstance name to the variable $serverInstance
Note: If you are using named instances, please specify ServerInstance as "Servername\Instancename" (Servername is the name of the server host and Instancename is name of the SQL server instance). Else, just assign a "."
b. Add a database name to the variable $database
c. Add a username and password to variable $userid and $password, if you are using SQL Server Authentication
5. The Site24x7 Windows agent will now automatically detect the plugin and mark it up for monitoring
6. To enable alerts, please set threshold value as 0 for the MSSQLAPPEvent plugin
Attachments
MSSQLAPPEvent.zip
Size: 1.34 KB
SQLSystemPermission.jpg
Size: 184.12 KB
Like (0)
Reply
by Brian Dunbar
Thanks for putting that together - I'll install it on the QA server and test.
Generally, how long after the plugin is in the plugin folder does it take for it to show up in site24x7?
> To enable alerts, please set threshold value as 0 for the MSSQLAPPEvent plugin
I don't understand this part - can you explain?
Like (0)
Reply
After adding plugin in plugin folder, it will take at the maximum of 10 minutes to display it in Site24x7.
Generally, we will capture the event based on the two status 0 (no events occurred) and 1 (event occurred).
If you set the threshold value as 0, we will trigger alert whenever the plugin returns the value as 1 while monitoring. It means that some events are occurred in MSSQL table.
Regards,
Rafee
Like (0)
Reply