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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...