scorestack · oneNutW0nder · Feb 21, 2021 · Feb 21, 2021 · Feb 21, 2021 · Feb 21, 2021
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -28,6 +28,7 @@ Each section organizes entries into the following subsections:
 #### Added
 
 - PostgreSQL check type (#294)
+- MSSQL check type (#295)
 
 #### Fixed
 

diff --git a/docs/reference/mssql.md b/docs/reference/mssql.md
@@ -0,0 +1,27 @@
+MSSQL
+=====
+
+| Name         | Type   | Required     | Description                                                          |
+| ------------ | ------ | ------------ | -------------------------------------------------------------------- |
+| Host         | String | Y            | IP or FQDN for the MSSQL server                                      |
+| Username     | String | Y            | Username for the database                                            |
+| Password     | String | Y            | Password for the user                                                |
+| Database     | String | Y            | Name of the database to access                                       |
+| Table        | String | Y            | Name of the table to access                                          |
+| Column       | String | Y            | Name of the column to access                                         |
+| MatchContent | String | N :: "false" | Whether to perform a regex content match on the results of the query |
+| ContentRegex | String | N :: "\.\*"  | Regex to match on                                                    |
+| Port         | String | N :: "1433"  | Port for the server                                                  |
+
+Example Check
+-------------
+
+The example check provided in the `examples/` folder uses the following schema:
+
+```sql
+CREATE DATABASE scorestack
+USE scorestack
+CREATE TABLE stacks (testVal NVARCHAR(50))
+INSERT INTO stacks ("hello from scorestack!")
+GO
+```
diff --git a/dynamicbeat/checks/checks.go b/dynamicbeat/checks/checks.go
@@ -21,6 +21,7 @@ import (
 	"github.com/scorestack/scorestack/dynamicbeat/checks/icmp"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/imap"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/ldap"
+	"github.com/scorestack/scorestack/dynamicbeat/checks/mssql"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/mysql"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/noop"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/postgresql"
@@ -164,6 +165,8 @@ func unpackDef(config schema.CheckConfig) (schema.Check, error) {
 		def = &smb.Definition{}
 	case "postgresql":
 		def = &postgresql.Definition{}
+	case "mssql":
+		def = &mssql.Definition{}
 	default:
 		logp.Warn("Invalid check type found. Offending check : %s:%s", config.Name, config.Type)
 		def = &noop.Definition{}

diff --git a/dynamicbeat/checks/mssql/mssql.go b/dynamicbeat/checks/mssql/mssql.go
@@ -0,0 +1,112 @@
+package mssql
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"regexp"
+	"strconv"
+
+	// MSSQL driver
+	_ "github.com/denisenkom/go-mssqldb"
+	"github.com/scorestack/scorestack/dynamicbeat/checks/schema"
+)
+
+type Definition struct {
+	Config       schema.CheckConfig // generic metadata about the check
+	Host         string             `optiontype:"required"`                      // IP or Hostname for the MSSQL server
+	Username     string             `optiontype:"required"`                      // Username for the database
+	Password     string             `optiontype:"required"`                      // Password for the user
+	Database     string             `optiontype:"required"`                      // Name of the database to access
+	Table        string             `optiontype:"required"`                      // Name of the table to access
+	Column       string             `optiontype:"required"`                      // Name of the column to access
+	MatchContent string             `optiontype:"optional"`                      // Whether to perform a regex content match on the results of the query
+	ContentRegex string             `optiontype:"optional" optiondefault:".*"`   // Regex to match on
+	Port         string             `optiontype:"optional" optiondefault:"1433"` // Port for the server
+}
+
+// Run a single instance of the check
+func (d *Definition) Run(ctx context.Context) schema.CheckResult {
+	// Initialize empty result
+	result := schema.CheckResult{}
+
+	// Create DB handle
+	db, err := sql.Open("mssql", fmt.Sprintf("sqlserver://%s:%s@%s:%s/instance?database=%s", d.Username, d.Password, d.Host, d.Port, d.Database))
+	if err != nil {
+		result.Message = fmt.Sprintf("Creating database handle failed : %s", err)
+		return result
+	}
+	defer db.Close()
+
+	// Set connection parameters
+	db.SetMaxIdleConns(-1)
+	db.SetMaxOpenConns(1)
+
+	// Check db connection
+	err = db.PingContext(ctx)
+	if err != nil {
+		result.Message = fmt.Sprintf("Failed to ping database : %s", err)
+		return result
+	}
+
+	// Query the Db
+	// TODO: This is SQL injectable. FIgure out paramerterized queries
+	rows, err := db.QueryContext(ctx, fmt.Sprintf("SELECT %s FROM %s;", d.Column, d.Table))
+	if err != nil {
+		result.Message = fmt.Sprintf("Could not query database : %s", err)
+		return result
+	}
+	defer rows.Close()
+
+	// Store the value from the column
+	var val string
+
+	// Perform regex matching if necessary
+	if matchContent, _ := strconv.ParseBool(d.MatchContent); matchContent {
+		// Compile the regex
+		regex, err := regexp.Compile(d.ContentRegex)
+		if err != nil {
+			result.Message = fmt.Sprintf("Error compiling regex string %s : %s", d.ContentRegex, err)
+			return result
+		}
+
+		// Check the rows
+		for rows.Next() {
+			// Grab a value
+			err := rows.Scan(&val)
+			if err != nil {
+				result.Message = fmt.Sprintf("Could not scan row values : %s", err)
+				return result
+			}
+			// Check value with regex
+			if regex.MatchString(val) {
+				// If we reach here the check passes
+				result.Passed = true
+				return result
+			}
+
+		}
+	}
+
+	// Check for error in the rows
+	if rows.Err() != nil {
+		result.Message = fmt.Sprintf("Something happened to the rows : %s", err)
+		return result
+	}
+
+	// Check passes if we reach here
+	result.Passed = true
+	return result
+
+}
+
+// GetConfig returns the current CheckConfig struct this check has been
+// configured with
+func (d *Definition) GetConfig() schema.CheckConfig {
+	return d.Config
+}
+
+// SetConfig reconfigures this check with a new CheckConfig struct
+func (d *Definition) SetConfig(config schema.CheckConfig) {
+	d.Config = config
+}
diff --git a/dynamicbeat/go.mod b/dynamicbeat/go.mod
@@ -21,6 +21,7 @@ replace (
 
 require (
 	github.com/akavel/rsrc v0.9.0 // indirect
+	github.com/denisenkom/go-mssqldb v0.0.0-20200206145737-bbfc9a55622e
 	github.com/dlclark/regexp2 v1.2.1 // indirect
 	github.com/dop251/goja v0.0.0-20200929101608-beb0a9a01fbc // indirect
 	github.com/dop251/goja_nodejs v0.0.0-20200811150831-9bc458b4bbeb // indirect

diff --git a/dynamicbeat/go.sum b/dynamicbeat/go.sum
@@ -178,6 +178,7 @@ github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSs
 github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
 github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 github.com/davecgh/go-xdr v0.0.0-20161123171359-e6a2ba005892/go.mod h1:CTDl0pzVzE5DEzZhPfvhY/9sPFMQIxaJ9VAMs9AagrE=
+github.com/denisenkom/go-mssqldb v0.0.0-20200206145737-bbfc9a55622e h1:LzwWXEScfcTu7vUZNlDDWDARoSGEtvlDKK2BYHowNeE=
 github.com/denisenkom/go-mssqldb v0.0.0-20200206145737-bbfc9a55622e/go.mod h1:xbL0rPBG9cCiLr28tMa8zpbdarY27NDyej4t/EjAShU=
 github.com/devigned/tab v0.1.1/go.mod h1:XG9mPq0dFghrYvoBF3xdRrJzSTX1b7IQrvaL9mzjeJY=
 github.com/devigned/tab v0.1.2-0.20190607222403-0c15cf42f9a2/go.mod h1:XG9mPq0dFghrYvoBF3xdRrJzSTX1b7IQrvaL9mzjeJY=
@@ -329,6 +330,7 @@ github.com/gogo/protobuf v1.1.1/go.mod h1:r8qH/GZQm5c6nD/R0oafs1akxWv10x8SbQlK7a
 github.com/gogo/protobuf v1.2.1/go.mod h1:hp+jE20tsWTFYpLwKvXlhS1hjn+gTNwPg2I6zVXpSg4=
 github.com/gogo/protobuf v1.3.1 h1:DqDEcV5aeaTmdFBePNpYsp3FlcVH/2ISVVM9Qf8PSls=
 github.com/gogo/protobuf v1.3.1/go.mod h1:SlYgWuQ5SjCEi6WLHjHCa1yvBfUnHcTbrrZtXPKa29o=
+github.com/golang-sql/civil v0.0.0-20190719163853-cb61b32ac6fe h1:lXe2qZdvpiX5WZkZR4hgp4KJVfY3nMkvmwbVkpv1rVY=
 github.com/golang-sql/civil v0.0.0-20190719163853-cb61b32ac6fe/go.mod h1:8vg3r2VgvsThLBIFL93Qb5yWzgyZWhEmBwUJWevAkK0=
 github.com/golang/glog v0.0.0-20160126235308-23def4e6c14b/go.mod h1:SBH7ygxi8pfUlaOkMMuAQtPIUF8ecWP5IEl/CR7VP2Q=
 github.com/golang/groupcache v0.0.0-20160516000752-02826c3e7903/go.mod h1:cIg4eruTrX1D+g88fzRXU5OdNfaM+9IcxsU14FzY7Hc=

diff --git a/examples/mssql/admin-attribs.json b/examples/mssql/admin-attribs.json
@@ -0,0 +1,8 @@
+{
+    "Host": "localhost",
+    "Username": "sa",
+    "Database": "scorestack",
+    "Table": "stacks",
+    "Column": "testVal",
+    "MatchContent": "true"
+}
diff --git a/examples/mssql/check.json b/examples/mssql/check.json
@@ -0,0 +1,17 @@
+{
+    "id": "mssql",
+    "name": "MSSQL",
+    "type": "mssql",
+    "group": "example",
+    "score_weight": 1,
+    "definition": {
+        "Host": "{{.Host}}",
+        "Username": "{{.Username}}",
+        "Password": "{{.Password}}",
+        "Database": "{{.Database}}",
+        "Table": "{{.Table}}",
+        "Column": "{{.Column}}",
+        "MatchContent": "{{.MatchContent}}",
+        "ContentRegex": "hello"
+    }
+}
diff --git a/examples/mssql/user-attribs.json b/examples/mssql/user-attribs.json
@@ -0,0 +1,3 @@
+{
+    "Password": "StongPassword!"
+}