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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...