Splunk Add-on for Microsoft SQL Server blocked SYSTEM account

[atyshke1]

Hi
After deployed TA-SQLServer I can see many errors in temp.db at all sql servers. Account SYSTEM which I use as per documentation splunk:
"Open your SQL Server Management Studio and log in as sysadmin (sa). Go to Security ->Logins -> NT AUTHORITY\SYSTEM (Properties) and grant the user sysadmin Server Role. Apply the change and restart your Splunk service.

Once you have all these steps done, then go into the app and run the Lookup Table Rebuilder (Searches & Reports->Lookup Table Rebuilder)"
The error is: How I understand when splunk try to check capacity of database, tranaction and etc. he is lock and use in monopoly mode because of this our application can't read or write a date from databases.
How can we fix monopoly mode for splunk scripts?

[atyshke1 · Accepted solution]

I found...
Need open a psm1 script: C:\Program Files\SplunkUniversalForwarder\etc\apps\TA-SQLServer\bin\Common.psm1
Find code:

function Get-SQLDatabases {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
        $Instance
    )

    PROCESS {
        foreach ($i in $Instance) {
            $s = New-Object('Microsoft.SqlServer.Management.Smo.Server') $i.ServerInstance

            $s.Databases | %{
                $DB = New-Object PSObject

                $DB | Add-Member -MemberType NoteProperty -Name Name -Value $_.Name
                $DB | Add-Member -MemberType NoteProperty -Name ID -Value $_.ID
                $DB | Add-Member -MemberType NoteProperty -Name DatabaseGuid -Value $_.DatabaseGuid
                $DB | Add-Member -MemberType NoteProperty -Name DatabaseOwnershipChaining -Value $_.DatabaseOwnershipChaining
                $DB | Add-Member -MemberType NoteProperty -Name Parent -Value $_.Parent
                $DB | Add-Member -MemberType NoteProperty -Name CompatibilityLevel -Value $_.CompatibilityLevel
                $DB | Add-Member -MemberType NoteProperty -Name DboLogin -Value $_.DboLogin
                $DB | Add-Member -MemberType NoteProperty -Name DefaultSchema -Value $_.DefaultSchema
                $DB | Add-Member -MemberType NoteProperty -Name EncryptionEnabled -Value $_.EncryptionEnabled
                $DB | Add-Member -MemberType NoteProperty -Name LastBackupDate -Value $_.LastBackupDate
                $DB | Add-Member -MemberType NoteProperty -Name LastDifferentialBackupDate -Value $_.LastDifferentialBackupDate
                $DB | Add-Member -MemberType NoteProperty -Name LastLogBackupDate -Value $_.LastLogBackupDate
                $DB | Add-Member -MemberType NoteProperty -Name Owner -Value $_.Owner
                $DB | Add-Member -MemberType NoteProperty -Name PrimaryFilePath -Value $_.PrimaryFilePath
                $DB | Add-Member -MemberType NoteProperty -Name ReadOnly -Value $_.ReadOnly
                $DB | Add-Member -MemberType NoteProperty -Name Version -Value $_.Version
                $DB | Add-Member -MemberType NoteProperty -Name Urn -Value $_.Urn

                            # Work out what mount point the PrimaryFilePath is on
                $LVM = [IO.Directory]::GetDirectoryRoot($_.PrimaryFilePath).TrimEnd("\")
                $DB | Add-Member -MemberType NoteProperty -Name LogicalDisk -Value $LVM

                # Add in relevant information from the instance
                $DB | Add-Member -MemberType NoteProperty -Name ServerInstance -Value $i.ServerInstance

                # Add in information about AutoShrink / AutoGrow
                if ($_.FileGroups -eq $Null -or $_.FileGroups[0] -eq $Null -or $_.FileGroups[0].Files -eq $Null) {
                    $DB | Add-Member -MemberType NoteProperty -Name AutoShrink -Value "!ERROR"
                    $DB | Add-Member -MemberType NoteProperty -Name AutoGrow -Value "!ERROR"
                    $DB | Add-Member -MemberType NoteProperty -Name AutoGrowSetting -Value "!ERROR"
                    $DB | Add-Member -MemberType NoteProperty -Name Warning -Value "SQL Authentication Issues"
                } else {
                    $DB | Add-Member -MemberType NoteProperty -Name AutoShrink -Value $_.DatabaseOptions.AutoShrink
                    $FileGroup = $_.FileGroups[0].Files[0]
                    if ($FileGroup.Growth -ne $Null -and $FileGroup.Growth -gt 0) {
                        $DB | Add-Member -MemberType NoteProperty -Name AutoGrow -Value $true
                        [string] $g = "{0} {1}" -f $FileGroup.Growth,$FileGroup.GrowthType
                        $DB | Add-Member -MemberType NoteProperty -Name AutoGrowSetting -Value $g
                    } else {
                        $DB | Add-Member -MemberType NoteProperty -Name AutoGrow -Value $false
                        $DB | Add-Member -MemberType NoteProperty -Name AutoGrowSetting -Value ""
                    }
                }

                # Calculate the checksum at this point
                [string]$Checksum = Get-Checksum -Object $DB

                # Add in the extra fields for non-checksum items 
                $DB | Add-Member -MemberType NoteProperty -Name Size -Value $_.Size
                $DB | Add-Member -MemberType NoteProperty -Name Status -Value $_.Status
                $DB | Add-Member -MemberType NoteProperty -Name State -Value $_.State
                $DB | Add-Member -MemberType NoteProperty -Name SpaceAvailable -Value $_.SpaceAvailable
                $DB | Add-Member -MemberType NoteProperty -Name ActiveConnections -Value $_.ActiveConnections
                #$DB | Add-Member -MemberType NoteProperty -Name DataSpaceUsage -Value $_.DataSpaceUsage
                #$DB | Add-Member -MemberType NoteProperty -Name IndexSpaceUsage -Value $_.IndexSpaceUsage

                # Add in the Checksum
                $DB | Add-Member -MemberType NoteProperty -Name Checksum -Value $Checksum

                Write-Output $DB
            }
           }
        }

}

and instead this code to my:

function Get-SQLDatabases {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
        $Instance
    )

    PROCESS {
        foreach ($i in $Instance) {
            $s = New-Object('Microsoft.SqlServer.Management.Smo.Server') $i.ServerInstance

            $s.Databases | %{
                $DB = New-Object PSObject

                $DB | Add-Member -MemberType NoteProperty -Name Name -Value $_.Name
                $DB | Add-Member -MemberType NoteProperty -Name ID -Value $_.ID
                $DB | Add-Member -MemberType NoteProperty -Name DatabaseGuid -Value $_.DatabaseGuid
                $DB | Add-Member -MemberType NoteProperty -Name DatabaseOwnershipChaining -Value $_.DatabaseOwnershipChaining
                $DB | Add-Member -MemberType NoteProperty -Name Parent -Value $_.Parent
                $DB | Add-Member -MemberType NoteProperty -Name CompatibilityLevel -Value $_.CompatibilityLevel
                $DB | Add-Member -MemberType NoteProperty -Name DboLogin -Value $_.DboLogin
                $DB | Add-Member -MemberType NoteProperty -Name DefaultSchema -Value $_.DefaultSchema
                $DB | Add-Member -MemberType NoteProperty -Name EncryptionEnabled -Value $_.EncryptionEnabled
                $DB | Add-Member -MemberType NoteProperty -Name LastBackupDate -Value $_.LastBackupDate
                $DB | Add-Member -MemberType NoteProperty -Name LastDifferentialBackupDate -Value $_.LastDifferentialBackupDate
                $DB | Add-Member -MemberType NoteProperty -Name LastLogBackupDate -Value $_.LastLogBackupDate
                $DB | Add-Member -MemberType NoteProperty -Name Owner -Value $_.Owner
                $DB | Add-Member -MemberType NoteProperty -Name PrimaryFilePath -Value $_.PrimaryFilePath
                $DB | Add-Member -MemberType NoteProperty -Name ReadOnly -Value $_.ReadOnly
                $DB | Add-Member -MemberType NoteProperty -Name Version -Value $_.Version
                $DB | Add-Member -MemberType NoteProperty -Name Urn -Value $_.Urn

                If ($DB.Name -ne "tempdb"){
                # Work out what mount point the PrimaryFilePath is on
                $LVM = [IO.Directory]::GetDirectoryRoot($_.PrimaryFilePath).TrimEnd("\")
                $DB | Add-Member -MemberType NoteProperty -Name LogicalDisk -Value $LVM

                # Add in relevant information from the instance
                $DB | Add-Member -MemberType NoteProperty -Name ServerInstance -Value $i.ServerInstance

                # Add in information about AutoShrink / AutoGrow
                if ($_.FileGroups -eq $Null -or $_.FileGroups[0] -eq $Null -or $_.FileGroups[0].Files -eq $Null) {
                    $DB | Add-Member -MemberType NoteProperty -Name AutoShrink -Value "!ERROR"
                    $DB | Add-Member -MemberType NoteProperty -Name AutoGrow -Value "!ERROR"
                    $DB | Add-Member -MemberType NoteProperty -Name AutoGrowSetting -Value "!ERROR"
                    $DB | Add-Member -MemberType NoteProperty -Name Warning -Value "SQL Authentication Issues"
                } else {
                    $DB | Add-Member -MemberType NoteProperty -Name AutoShrink -Value $_.DatabaseOptions.AutoShrink
                    $FileGroup = $_.FileGroups[0].Files[0]
                    if ($FileGroup.Growth -ne $Null -and $FileGroup.Growth -gt 0) {
                        $DB | Add-Member -MemberType NoteProperty -Name AutoGrow -Value $true
                        [string] $g = "{0} {1}" -f $FileGroup.Growth,$FileGroup.GrowthType
                        $DB | Add-Member -MemberType NoteProperty -Name AutoGrowSetting -Value $g
                    } else {
                        $DB | Add-Member -MemberType NoteProperty -Name AutoGrow -Value $false
                        $DB | Add-Member -MemberType NoteProperty -Name AutoGrowSetting -Value ""
                    }
                }

                # Calculate the checksum at this point
                [string]$Checksum = Get-Checksum -Object $DB

                # Add in the extra fields for non-checksum items 
                $DB | Add-Member -MemberType NoteProperty -Name Size -Value $_.Size
                $DB | Add-Member -MemberType NoteProperty -Name Status -Value $_.Status
                $DB | Add-Member -MemberType NoteProperty -Name State -Value $_.State
                $DB | Add-Member -MemberType NoteProperty -Name SpaceAvailable -Value $_.SpaceAvailable
                $DB | Add-Member -MemberType NoteProperty -Name ActiveConnections -Value $_.ActiveConnections
                #$DB | Add-Member -MemberType NoteProperty -Name DataSpaceUsage -Value $_.DataSpaceUsage
                #$DB | Add-Member -MemberType NoteProperty -Name IndexSpaceUsage -Value $_.IndexSpaceUsage

                # Add in the Checksum
                $DB | Add-Member -MemberType NoteProperty -Name Checksum -Value $Checksum

                Write-Output $DB
            }
           }
        }
    }
}

So we need add additional expression which help us exclude check tempdb.
I use this code 2 months and it's works fine for me.

[atyshke1]

I found a screpts which call blocks in SQL:
1. [powershell://Databases]
2. [powershell://DBInstances]

Could you help me to find, what in this powershell block acount System in SQL?