All Apps and Add-ons

MSSQL DB KPIs are not getting populated in ITSI

satishsdange
Builder

I am unable to see MSSQL KPIs in ITSI. Below is what I have done so far -
- Installed SQL TA, DBX2 & Windows TA
- configured lookup in SQL TA for host & port
- I am able to see data into Splunk

But I don't following fields that populates DB KPIs -deadlock_rate, server, read_iops, server_write_iops, response_time, connections, connection_pool_used_percent, transaction_rate.

Search for datamodel has produced following results -
alt text

alt text

I am suspecting problem with tag=server field which does not produce any results.
index=* tag=database tag=performance tag=server

Could someone please advise me how to fix this problem.

Thanks in advance.

Tags (5)
0 Karma
1 Solution

mchang_splunk
Splunk Employee
Splunk Employee

deadlock_rate, server, read_iops, server_write_iops, response_time, connections, connection_pool_used_percent, transaction_rate are search time extracted fields defined in sourcetype=mssql:os:dm_os_performance_counters.

in $SPLUNK_HOME/etc/apps/Splunk_TA_microsoft-sqlserver/default/props.conf, you should be able to find the definition as following:

[mssql:os:dm_os_performance_counters]
EVAL-transaction_rate = case(counter_name="Transactions/sec" AND instance_name="_Total", cntr_value)
EVAL-current_size = case(counter_name="Data File(s) Size (KB)" AND instance_name="_Total", cntr_value/1024)
EVAL-connection_pool_used_percent = case(counter_name="User Connections", cntr_value*100/max_connection)
EVAL-connections = case(counter_name="User Connections", cntr_value)
EVAL-deadlock_rate = case(counter_name="Number of Deadlocks/sec" AND instance_name="_Total", cntr_value)
EVAL-server_read_iops = case(counter_name="Page reads/sec", cntr_value)
EVAL-server_write_iops = case(counter_name="Page writes/sec", cntr_value)
EVAL-error_rate = case(counter_name="Errors/sec" AND instance_name="_Total", cntr_value)

View solution in original post

0 Karma

satishsdange
Builder

SQL TA upgrade to 1.2 solved my problem

0 Karma

nravichandran
Communicator

How did you upgrade? can the upgrade be done like an update?

0 Karma

mchang_splunk
Splunk Employee
Splunk Employee

deadlock_rate, server, read_iops, server_write_iops, response_time, connections, connection_pool_used_percent, transaction_rate are search time extracted fields defined in sourcetype=mssql:os:dm_os_performance_counters.

in $SPLUNK_HOME/etc/apps/Splunk_TA_microsoft-sqlserver/default/props.conf, you should be able to find the definition as following:

[mssql:os:dm_os_performance_counters]
EVAL-transaction_rate = case(counter_name="Transactions/sec" AND instance_name="_Total", cntr_value)
EVAL-current_size = case(counter_name="Data File(s) Size (KB)" AND instance_name="_Total", cntr_value/1024)
EVAL-connection_pool_used_percent = case(counter_name="User Connections", cntr_value*100/max_connection)
EVAL-connections = case(counter_name="User Connections", cntr_value)
EVAL-deadlock_rate = case(counter_name="Number of Deadlocks/sec" AND instance_name="_Total", cntr_value)
EVAL-server_read_iops = case(counter_name="Page reads/sec", cntr_value)
EVAL-server_write_iops = case(counter_name="Page writes/sec", cntr_value)
EVAL-error_rate = case(counter_name="Errors/sec" AND instance_name="_Total", cntr_value)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...