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

[sravankaripe]

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

[niketn]

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.

[jbarlow_splunk]

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