This module type is used to discover instances of Microsoft SQL Server 2008 Databases. The module connects to SQL and discovers all databases together with properties.
'Copyright (c) Microsoft Corporation. All rights reserved.
' Parameters that should be passed to this script
' Parameters that should be passed to this script
' 0 MPElement ID ($MPElement$)
' 1 Target Id for ME this rule is running against ($Target/Id$)
' 2 Computer ID
' 3 Computer FQDN
' 4 SQL Connection String for the instance that the DBs are being discovered on
' 5 SQL Instance that this rule is being run for
' 6 Exlcuded database list (prefixed with Exclude:)
set oSQLDiscoveryData = oAPI.CreateDiscoveryData(0, SourceId, ManagedEntityId)
If DoDatabaseDiscovery(InstanceName, ConnectionString, oSQLDiscoveryData) >= 0 Then Call oAPI.Return(oSQLDiscoveryData)
WScript.Quit()
Function DoDatabaseDiscovery(ByVal sSqlInstance, ByVal sSQLConnectionString, ByVal oDisc)
Dim e
Set e = New Error
Dim cnADOConnection
Set cnADOConnection = MomCreateObject("ADODB.Connection")
cnADOConnection.Provider = "sqloledb"
cnADOConnection.ConnectionTimeout = 15
Dim strProv
strProv = "Server=" & sSQLConnectionString & ";Database=master;Trusted_Connection=yes"
e.Clear
On Error Resume Next
cnADOConnection.Open strProv
e.Save
On Error Goto 0
if 0 <> Err.number then
'Error event in here
'g_oSQL.CreateConnectionFailureAlert sInstance, Err.number, Err.Description
DoDatabaseDiscovery = SQL_DISCOVERY_CONNECT_FAILURE
Exit Function
end if
Dim oResults
e.Clear
On Error Resume Next
' query for the list of databases which are not database snapshots
Set oResults = cnADOConnection.Execute("SELECT name, state_desc FROM sys.databases WHERE source_database_id IS NULL")
e.Save
On Error Goto 0
If e.Number <> 0 Then
' Will eventually drop some events here once script API supports
' g_oSQL.CreateAlert ALERT_WARNING, _
' SCRIPT_NAME & " (" & sSQLServerInstance & ")", _
' "Could not execute sp_databases on SQL Server Instance: " & sSQLServerInstance & ". Error number: " & e.Number & ", Error Information: " & e.Description, _
' "", _
' ""
DoDatabaseDiscovery = SQL_DISCOVERY_QUERY_FAILURE
Exit Function
End If
Dim iDBRow, iRow, sFileType
Dim iPos, iPos2, iTemp, bDBAuto, bLogAuto
Dim oDBResults, oDBInstance, sDBSize, sDBStatus
Dim sDBName, sDBState
Dim dDBSize, dLogSize
Dim sDBOwner
Do While Not oResults.EOF
sDBName = oResults(0)
sDBState = oResults(1)
If Not(IsExcluded(SDBName)) Then
Set oDBInstance = oSQLDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2008.Database']$")
' Set basic DB properties
With oDBInstance
.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", sDBName
End With
' Only do further discovery for DB that is online - sp_helpdb wont return state for offline databases
' To do - may want to add owner, created, dbid and compat level to discovery, collation etc.
If sDBState = "ONLINE" Then
On Error Resume Next
Set oDBResults = cnADOConnection.Execute("sp_helpdb [" & sDBName & "]")
e.Save
On Error Goto 0
Select Case e.Number
Case 0
On Error Resume Next
sDBSize = Trim(oDBResults(1))
e.Save
On Error Goto 0
If e.Number = 0 Then
sDBSize = Trim(oDBResults(1))
sDBStatus = Split(oDBResults(5),",")
sDBOwner = oDBResults(2)
Set oDBResults = oDBResults.NextRecordset
bDBAuto = false
bLogAuto = false
dDBSize = 0
dLogSize = 0
Do While Not oDBResults.EOF
sFileType = oDBResults(7)
If LCase(sFileType) = "data only" Then
dDBSize = dDBSize + ConvertSizeStringToNumber(oDBResults(4))
If (Trim(oDBResults(6)) <> "0%") And (Trim(oDBResults(6)) <> "0 KB") Then
bDBAuto = true
End If
End If
If LCase(sFileType) = "log only" Then
dLogSize = dLogSize + ConvertSizeStringToNumber(oDBResults(4))
If (Trim(oDBResults(6)) <> "0%") And (Trim(oDBResults(6)) <> "0 KB") Then
bLogAuto = true
End If
End If
oDBResults.MoveNext
Loop
Dim iDBSize, iLogSize
iDBSize = Fix(dDBSize / 1024)
If(iDBSize > MAX_INT_VALUE) Then
iDBSize = MAX_INT_VALUE
End If
iLogSize = Fix(dLogSize / 1024)
If(iLogSize > MAX_INT_VALUE) Then
iLogSize = MAX_INT_VALUE
End If
With oDBInstance
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseSize$", dDBSize / 1024
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseSizeNumeric$", iDBSize
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/LogSize$", dLogSize / 1024
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/LogSizeNumeric$", iLogSize
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/RecoveryModel$", Split(sDBStatus(3),"=")(1)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/Updateability$", Split(sDBStatus(1),"=")(1)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/UserAccess$", Split(sDBStatus(2),"=")(1)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/Collation$", Split(sDBStatus(5),"=")(1)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseAutogrow$", CStr(bDBAuto)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/LogAutogrow$", CStr(bLogAuto)
If Not(IsNull(sDBOwner)) Then
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/Owner$", sDBOwner
End If
End With
End If
Case ERR_CANNOT_INSERT_NULL
' Throw error to event log in here
' Error text will be "The system stored procedure sp_helpdb, which is used to gather information about the databases, has returned an error that may indicate that it cannot determine the db owner for the database [" & strDBName & ']. Here are the details: sp_helpdb @dbName='" & strDBName & "' on SQL Server Instance: " & strSQLServerInstanceName & ". Error number: " & e.Number & ", Error Information: " & e.Description, _
Case Else
' Throw error to event log in here
' Error text will be "Could not execute sp_helpdb @dbname=N'" & Replace(strDBName, "'", "''") & "' on SQL Server Instance: " & strSQLServerInstanceName & ". Error number: " & e.Number & ", Error Information: " & e.Description, _
End Select
Set oDBResults = nothing
End If
' Add DB to the Instance List and move to next DB
call oSQLDiscoveryData.AddInstance(oDBInstance)
End If
oResults.MoveNext
Loop
Set oResults = nothing
cnADOConnection.Close
DoDatabaseDiscovery = SQL_DISCOVERY_SUCCESS
End Function
Class Error
Private m_lNumber
Private m_sSource
Private m_sDescription
Private m_sHelpContext
Private m_sHelpFile
Public Sub Save()
m_lNumber = Err.number
m_sSource = Err.Source
m_sDescription = Err.Description
m_sHelpContext = Err.HelpContext
m_sHelpFile = Err.helpfile
End Sub
Public Sub Raise()
Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
End Sub
Public Sub Clear()
m_lNumber = 0
m_sSource = ""
m_sDescription = ""
m_sHelpContext = ""
m_sHelpFile = ""
End Sub
Public Default Property Get Number()
Number = m_lNumber
End Property
Public Property Get Source()
Source = m_sSource
End Property
Public Property Get Description()
Description = m_sDescription
End Property
Public Property Get HelpContext()
HelpContext = m_sHelpContext
End Property
Public Property Get HelpFile()
HelpFile = m_sHelpFile
End Property
End Class
Function MomCreateObject(ByVal sProgramId)
Dim oError
Set oError = New Error
On Error Resume Next
Set MomCreateObject = CreateObject(sProgramId)
oError.Save
On Error Goto 0
If oError.Number <> 0 Then ThrowScriptError "Unable to create automation object '" & sProgramId & "'", oError
End Function
'******************************************************************************
Function ThrowScriptErrorNoAbort(ByVal sMessage, ByVal oErr)
On Error Resume Next
Dim oAPITemp
Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")
oAPITemp.LogScriptEvent "DiscoverSQL2008DB.vbs", 4001, 1, sMessage & ". " & oErr.m_sDescription
End Function
'******************************************************************************
Function ThrowScriptError(Byval sMessage, ByVal oErr)
On Error Resume Next
ThrowScriptErrorNoAbort sMessage, oErr
Quit()
End Function
'******************************************************************************
Function ConvertSizeStringToNumber(sSizeString)
' Remove the KB and return a valid double
sSizeString = Replace(sSizeString, " KB", "")
ConvertSizeStringToNumber = CDbl(sSizeString)
End Function
Function IsExcluded (sDatabase)
Dim aExcludes
Dim match
Dim i
match = false
If Trim(ExcludeList) = "*" Then
match = true
Else
aExcludes = Split(ExcludeList, ",")
For i = 0 To UBound(aExcludes)
If LCase(sDatabase) = LCase(Trim(aExcludes(i))) Then
match = true
End If
Next
End If
IsExcluded = match
Home
Show References: