Troubleshoot the Splunk Add-on for Microsoft SQL Server

General troubleshooting

For helpful troubleshooting tips that you can apply to all add-ons, see Troubleshoot add-ons in Splunk Add-ons. For additional resources, see Support and resource links for add-ons in Splunk Add-ons.

Verify data collection

To check that the Splunk platform is collecting the data that you expect, use this search command to list the indexed source types:

| metadata index=main type=sourcetypes | fields sourcetype 

You can find all possible source types contributed by this add-on listed on the source types page. If your search results are missing a source type that you intended to collect, verify that you have enabled that source type in %SPLUNK_HOME%\etc\apps\Splunk_TA_microsoft-sqlserver\local\inputs.conf, %SPLUNK_HOME%\etc\apps\splunk_app_db_connect\local\inputs.conf.

Determine event sources

The add-on adds two fields at search time for each event that can be useful in determining the event source.

Field	How to determine the event source
sqlserver_instance_name	For events collected through file or performance monitoring, use the SQL Server instance name. For example: default_instance For events collected through Splunk DB Connect, use "DBConn:"+ connection_name. For example: DBConn:sqlserver_default_connection
sqlserver_full_instance_name	For events collected through file or performance monitoring, use host_name + "/"+sqlserver_instance_name. For example: DBConn:sqlserver_default_connection For events collected through Splunk DB Connect, usehost_name +"/"+"DBConn:"+ connection_name. For example: WIN-J0NE2C7KVR9/DBconn:sqlserver_default_connection

Eliminate white spaces in events

Some events from Microsoft SQL Server data contain extra white spaces caused by fixed sizes in SQL Server and the handling logic in Splunk DB Connect. Trim the white spaces using the search below:

sourcetype = [insert source type] | rex mode=sed "s/\s{2,}//g"

Line breaking and missing field issues for mssql:audit and mssql:trclog data collected with DB Connect v1

Due to limitations in Splunk DB Connect v1, you might experience line breaking issues or missing fields in your audit and trace logs. You can work around the issue by adding SHOULD_LINEMERGE = true in your mssql:audit and mssql:trclog stanzas in %SPLUNK_HOME%\etc\apps\dbx\local\props.conf.

Examine the buffer pool

If you want to see what objects and indexes are in the buffer pool, run the following SQL statements in DB query in Splunk DB Connect.

Note: Query statements in the DB connect conf files are limited to 128 characters, so this query must be run in Splunk Web.

select

       count(*)as cached_pages_count,

       obj.name as objectname,

       ind.name as indexname,

       obj.index_id as indexid

from sys.dm_os_buffer_descriptors as bd

    inner join

    (

        select       object_id as objectid,

                           object_name(object_id) as name,

                           index_id,allocation_unit_id

        from sys.allocation_units as au

            inner join sys.partitions as p

                on au.container_id = p.hobt_id

                    and (au.type = 1 or au.type = 3)

        union all

        select       object_id as objectid,

                           object_name(object_id) as name,

                           index_id,allocation_unit_id

        from sys.allocation_units as au

            inner join sys.partitions as p

                on au.container_id = p.partition_id

                    and au.type = 2

    ) as obj

        on bd.allocation_unit_id = obj.allocation_unit_id

left outer join sys.indexes ind 

  on  obj.objectid = ind.object_id

 and  obj.index_id = ind.index_id

where bd.database_id = db_id()

  and bd.page_type in ('data_page', 'index_page')

group by obj.name, ind.name, obj.index_id

order by cached_pages_count desc

Understand the missing records_affected field

If you are using Microsoft SQL Server 2008 R2, the add-on does not provide the records_affected field for events in the source type mssql:execution:dm_exec_query_stats. The add-on cannot supply this field because the add-on derives this field from the last_rows column in the table sys.dm_exec_query_stats, but Microsoft SQL Server 2008 R2 does not include this column. The absence of this field does not affect the mapping of any other fields.