'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 FQDN
' 3 SQL Instance that this rule is being run for
' 4 The Data Source property in OLEDB connection string. (The connection string from DBEngine instance)
Option Explicit
SetLocale("en-us")
Dim scriptStartTime
scriptStartTime = GetUTCNow()
Dim oArgs
Set oArgs = WScript.Arguments
If oArgs.Count <> 5 Then
Wscript.Quit -1
End If
Dim oAPI, oSQLDiscoveryData, connString
Set oAPI = CreateObject("MOM.ScriptAPI")
Set oSQLDiscoveryData = oAPI.CreateDiscoveryData(0, SourceId, ManagedEntityId)
ConnectionString= "Provider=SQLOLEDB;Data Source=" + DataSource+ "; Initial Catalog=master;Integrated Security=SSPI"
' Calling these functions in this order is very important
' to capture the right time stamps for discovery
DoSysDatabaseDiscovery
DoDatabaseDiscovery
Call oAPI.Return(oSQLDiscoveryData)
WScript.Quit()
'---------------------------------------------------------------------------
'Add a property to the discovery data if not null
'---------------------------------------------------------------------------
Sub AddPropertyIfNotNull(ByRef discData, prop, value)
if NOT isNull(value) Then
Call discData.AddProperty(prop, value)
End if
End Sub
'---------------------------------------------------------------------------
'Perform discovery on sys.Databases table
'---------------------------------------------------------------------------
Sub DoSysDatabaseDiscovery
Dim cnADOConnection
Set cnADOConnection = CreateObject("ADODB.Connection")
cnADOConnection.Open ConnectionString
Dim oResults, query, oInst
dim EngineModelCollation
' Get the Engine Model Collation first
query = "SELECT collation_name FROM sys.databases WHERE [name] = N'model'"
Set oResults = cnADOConnection.Execute(query)
EngineModelCollation = oResults(0)
query = "SELECT " & _
"name, " & _
"database_id as [ID], " & _
"is_honor_broker_priority_on, " & _
"DateAdd(MINUTE, DateDiff(MINUTE, GETDATE(), GETUTCDATE()),create_date), " & _
"compatibility_level, " & _
"is_read_only, " & _
"is_auto_close_on, " & _
"is_auto_shrink_on, " & _
"state_desc as [Status], " & _
"is_in_standby, " & _
"is_cleanly_shutdown, " & _
"is_supplemental_logging_enabled, " & _
"snapshot_isolation_state_desc, " & _
"is_read_committed_snapshot_on, " & _
"page_verify_option_desc as [PageVerify], " & _
"is_auto_create_stats_on, " & _
"is_auto_update_stats_on, " & _
"is_auto_update_stats_async_on, " & _
"is_ansi_null_default_on, " & _
"is_ansi_nulls_on, " & _
"is_ansi_padding_on, " & _
"is_ansi_warnings_on, " & _
"is_arithabort_on, " & _
"is_concat_null_yields_null_on, " & _
"is_numeric_roundabort_on, " & _
"is_quoted_identifier_on, " & _
"is_recursive_triggers_on, " & _
"is_cursor_close_on_commit_on, " & _
"is_local_cursor_default, " & _
"is_fulltext_enabled, " & _
"is_trustworthy_on, " & _
"is_db_chaining_on, " & _
"is_parameterization_forced, " & _
"is_master_key_encrypted_by_server, " & _
"is_published, " & _
"is_subscribed, " & _
"is_merge_published, " & _
"is_distributor, " & _
"is_sync_with_backup, " & _
"is_broker_enabled, " & _
"log_reuse_wait_desc, " & _
"is_date_correlation_on, " & _
"is_cdc_enabled, " & _
"is_encrypted " & _
"FROM sys.databases where source_database_id IS NULL"
Set oResults = cnADOConnection.Execute(query)
Do While Not oResults.EOF
dim sStatus
set sStatus = oResults(8)
Set oInst = oSQLDiscoveryData.CreateClassInstance("$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']$")
if oResults(0) = "model" or oResults(0) = "master" or oResults(0) = "tempdb" or oResults(0) = "msdb" then
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/IsSharepointSSRSSystemDatabase$", true)
else
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/IsSharepointSSRSSystemDatabase$", false)
end if
'---------------------------------------------------------------------------
'perform discovery for mirroring and Updatability
'---------------------------------------------------------------------------
Sub DoDatabaseDiscovery
Dim cnADOConnection
Set cnADOConnection = CreateObject("ADODB.Connection")
cnADOConnection.Open ConnectionString
Dim oResults, query, oInst
query = "SELECT a.name, " & _
"CAST(case when b.mirroring_partner_name is null then 0 else 1 end AS bit) AS [IsMirroringEnabled], b.mirroring_role_desc " & _
"FROM sys.databases a, sys.database_mirroring b " & _
"WHERE a.database_id = b.database_id and a.source_database_id IS NULL"
Set oResults = cnADOConnection.Execute(query)
Dim scriptEndTime
scriptEndTime = GetUTCNow()
Do While Not oResults.EOF
Set oInst = oSQLDiscoveryData.CreateClassInstance("$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']$")
' The following code was added by ericzha for new database config points SystemCatalogUpdated,TargetRecoveryTimeInSeconds, Containment, FiletableNonTransactedAccess
' Check if this is a SQL 2012 database
Dim version
query = "select SERVERPROPERTY('ProductVersion')"
Set oResults = cnADOConnection.Execute(query)
version = CStr(oResults(0))
version = CInt(Mid(version,1, InStr(1,version,".",1)-1))
Set oResults = Nothing
' Config points for all SQL versions
' #1 SystemCatalogUpdated
query = "exec sp_executesql N'SET NOCOUNT ON; DECLARE @dbinfo TABLE(ParentObject nvarchar(255),Object nvarchar(255),Field nvarchar(255),Value nvarchar(255)); INSERT INTO @dbinfo EXEC (''DBCC DBINFO ([" + EscapeConnectionString(EscapeConnectionString(dbName)) + "]) WITH TABLERESULTS ''); SELECT CASE [Value] WHEN N''1900-01-01 00:00:00.000'' THEN 0 ELSE 1 END AS SystemCatalog_UpdatedManually_DB_Unsupported FROM @dbinfo WHERE ParentObject = N''DBINFO STRUCTURE:'' AND Field = N''dbi_updSysCatalog''; SET NOCOUNT OFF;'"
Set oResults = cnADOConnection.Execute(query)
bValue = false
bValue = CBool(oResults(0))
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/SystemCatalogUpdated$", bValue)
Set oResults = Nothing
' Config points that are introduced since SQL 2012
If version >= 11 Then
' #2 TargetRecoveryTimeInSeconds; #3 Containment
intValue=0
query = "select target_recovery_time_in_seconds, containment from sys.databases where name = '" & EscapeConnectionString(dbName) & "'"
Set oResults = cnADOConnection.Execute(query)
intValue = CInt(oResults(0))
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/TargetRecoveryTimeInSeconds$", intValue)
intValue = CInt(oResults(1))
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/Containment$", intValue)
Set oResults = Nothing
' #4 FiletableNonTransactedAccess
intValue=0
query = "select non_transacted_access from sys.database_filestream_options where database_id = db_id('" & EscapeConnectionString(dbName) & "')"
Set oResults = cnADOConnection.Execute(query)
intValue = CInt(oResults(0))
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/FiletableNonTransactedAccess$", intValue)
Set oResults = Nothing
End If
' End of modification by ericzha
' Begin: Edited by v-jobzha
' #5 DataBaseGuid
Dim strDataBaseGuid
query = "select database_guid from sys.database_recovery_status where database_id = db_id('" & EscapeConnectionString(dbName) & "')"
Set oResults = cnADOConnection.Execute(query)
strDataBaseGuid = oResults(0)
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/DataBaseGuid$", strDataBaseGuid)
Set oResults = Nothing
' #6 FamilyGuid
Dim strFamilyGuid
query = "select family_guid from sys.database_recovery_status where database_id = db_id('" & EscapeConnectionString(dbName) & "')"
Set oResults = cnADOConnection.Execute(query)
strFamilyGuid = oResults(0)
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/FamilyGuid$", strFamilyGuid)
Set oResults = Nothing
' End: Edited by v-jobzha
' Check if this is a Sharepoint database
query = "select case SUM(query1.IndividualCounts) " & _
"when 14 then 1 else 0 end as [SharePointDatabase] from " & _
"(select COUNT(*) as 'IndividualCounts' from sys.objects where " & _
"([name] = N'Versions' and [type] = 'U') or ([name] = N'Versions_PK' and [type] = 'PK') " & _
"union all select COUNT(*) " & _
"from sys.columns where [object_id] = OBJECT_ID(N'Versions',N'U') " & _
"union all select COUNT(*) from sys.index_columns " & _
"where [object_id] = OBJECT_ID(N'Versions',N'U') and [index_id] = 1) AS query1"
Set oResults = cnADOConnection.Execute(query)
bValue = CBool(oResults(0))
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/IsSharepointDatabase$", bValue)
Set oResults = Nothing
' Check if this is a SSRS database
query = "select case SUM(query1.IndividualCounts) " & _
"when 5 then 1 else 0 end as [SharePointDatabase] from " & _
"(select COUNT(*) as 'IndividualCounts' from sys.objects where " & _
"([name] = N'Segment' and [type] = 'U') or ([name] = N'PK_Segment' and [type] = 'PK') " & _
"union all select COUNT(*) " & _
"from sys.columns where [object_id] = OBJECT_ID(N'Segment',N'U') " & _
"union all select COUNT(*) from sys.index_columns " & _
"where [object_id] = OBJECT_ID(N'Segment',N'U') and [index_id] = 1) AS query1"
bValue = false
Set oResults = cnADOConnection.Execute(query)
bValue = CBool(oResults(0))
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/IsSSRSDatabase$", bValue)
Set oResults = Nothing
cnADOConnection.Close
' On Error Goto 0
Else
' Set the properties to false.
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/IsSharepointDatabase$", false)
Call AddPropertyIfNotNull(oInst, "$MPElement[Name='KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database']/IsSSRSDatabase$", false)
End If
End Sub
'---------------------------------------------------------------------------
'This function returns the UTC time by looking at the time skew in registry
'and correcting for it.
'---------------------------------------------------------------------------
Function GetUTCNow()
Dim localTime, wScriptShell, registryKey, offsetMinutes, utcTime
localTime = now
set wScriptShell = CreateObject("WScript.Shell")
registryKey = "HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
offsetMinutes = wScriptShell.RegRead(registryKey)
utcTime = dateadd("n", offsetMinutes, localTime)
GetUTCNow = utcTime
End Function </Script></ScriptBody>
<TimeoutSeconds>300</TimeoutSeconds>
<IntervalInSeconds>15399</IntervalInSeconds>
</DataSource>
</Discovery>
Home