All Apps and Add-ons

Splunk DB Connect: How to get count of db queries run in Splunk environment?

sravankaripe
Communicator

how to get count of db queries run in Splunk environment? please help me with splunk query .

0 Karma

niketn
Legend

Have you looked at DB2 Health Dashboard? There are several panels like

DB Connect Health - No of Transaction over time

| tstats count as count from datamodel=DB_Health where `health_filter(*, *, *)` groupby "All_Records.CONNECTION", _time prestats=false summariesonly=false | rename All_Records.* as * | timechart sum(count) as abc by CONNECTION

DB Connect Health - Status by Transaction Type

| tstats values(All_Records.CONNECTION) as CONNECTION values(All_Records.DB_USER) as DB_USER values(All_Records.FUNCTION) as FUNCTION values(All_Records.STATE) as STATE from datamodel=DB_Health groupby "All_Records.TRANS_OBJECT_ID" | rename All_Records.* as * | eval FUNCTION=mvfilter(match(FUNCTION, "py_*")) | `health_transaction_filter(*, *, *)` |  stats count(eval(mvfind(STATE,"error"))) as Failure count as Total by FUNCTION | eval Success=(Total-Failure) | stats sum(Failure) as Failure sum(Success) as Success by FUNCTION

DB Connect Health - Transaction

| tstats values(All_Records.STATE) as STATE values(All_Records.CONNECTION) as All_Records.CONNECTION values(All_Records.DB_USER) as All_Records.DB_USER values(All_Records.ERROR_CODE) as All_Records.ERROR_CODE values(All_Records.JDBC_USE_SSL) as All_Records.JDBC_USE_SSL values(All_Records.DURATION) as All_Records.DURATION values(All_Records.MEMORY_USED) as All_Records.MEMORY_USED count as count from datamodel=DB_Health  groupby "All_Records.TRANS_OBJECT_ID",_time,"All_Records.MESSAGE","All_Records.FUNCTION"
             | rename All_Records.* as *
             | replace database with "" in MESSAGE
             | stats list(STATE) as STATE list(MESSAGE) as MESSAGE list(FUNCTION) as FUNCTION first(CONNECTION) as CONNECTION first(DB_USER) as DB_USER values(ERROR_CODE) as ERROR_CODE values(DURATION) as DURATION values(JDBC_USE_SSL) as JDBC_USE_SSL latest(_time) as _time max(MEMORY_USED) as MEMORY_USED by TRANS_OBJECT_ID
             | `health_transaction_filter(*, *, *)`
             | mvexpand FUNCTION
             | search FUNCTION=py_*
             | replace null* with * in MESSAGE
             | eval ERROR = mvfilter(match(STATE, "error"))
             | eval COMPLETED = mvindex(mvfilter(match(STATE, "completed")), 1)
             | fillnull value=null COMPLETED ERROR |eval MEMORY_USED=MEMORY_USED/1000
             | stats first(COMPLETED) as COMPLETED first(ERROR) as ERROR first(MESSAGE) as MESSAGE first(FUNCTION) as "Transaction Type" first(CONNECTION) as Connection first(DB_USER) as Identity first(ERROR_CODE) as ERROR_CODE first(DURATION) as Duration values(JDBC_USE_SSL) as SSL first(MEMORY_USED) as "Connection Used Mem(MB)" by TRANS_OBJECT_ID _time
             | eval Status=case(ERROR_CODE==400, "Error", len(MESSAGE)>0, "Error", ERROR="error", "Error", COMPLETED!="completed", "In Progress", len(MESSAGE)==0, "Success")
             | rename TRANS_OBJECT_ID as Transaction
             | table _time Transaction Connection "Transaction Type" Identity Status SSL Duration "Connection Used Mem(MB)"
             | fillnull SSL value=False
             | fillnull value="In Progress" Status
             | eval Duration=round(Duration, 2)
             | sort -_time

You can tweak these and many more as per your need.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

jbarlow_splunk
Splunk Employee
Splunk Employee

the number of queries dbconnect issues ?
If so, at the most basic.. something like this maybe?

index=_internal source=*dbx_query_audit.log sourcetype=dbx_query_audit action=sql_audit|stats count by sql

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 ...