Query to extract all SQL statements and timestamp from DPA for a particular registered instance?

vogelm

Hello!  I've been working on an *interesting* request from our audit team regarding some data they want to pull out of the DPA repository.  None of the canned reports are giving me exactly what I'm looking for, so I'm hoping the community can help?

I actually think I'll have two queries - one for detailed data and one for summary data.

I've identified the correct ID for the instance from the COND table.

For the detailed data, I'm looking at CONSW_XXX, CONST_XXX, and CONSS_XXX.  Where I'm struggling is with how to join between the tables correctly.  The Hash value lines up but the Date columns do not.  Does anyone have any experience with these tables and how to accurately join them?

I have the following query, which bypasses the CONSW table, but does not give me the detailed date data, only to the nearest minute.  I'd like to include the "D" column from CONSW_126, but I'm struggling with how to complete the join without getting extra rows back for each hash value.  Any assistance is appreciated!

select ss.H, ss.D, ss.EXECS, st.ST
from CONSS_126 ss
join CONST_126 st on ss.H = st.H

The two fields that my audit team would like are 1) the SQL statement executed and 2) the Date.

jaminsql

One issue here is that you are looking to join CONSW_ and expecting to not see many rows. This table is the detail table for the DPA quick poll so it has a row for every second of wait for a hash. It joins to CONSS_ on the CONSW_ column IZHO but, you would want to do some sum of the QP column / 100 and where some start and stop date in the D column really to limit what you see there. 

CONST_ also has a P column this is part as the SQL text is broken up into parts in that table so you can have for instance a long SQL text that has P 0 - 5 with each part being 4000 characters of the total text. 

This might help some 

CONSW_X
----------
- XCUW - DB User - joins to CONU_X
- IXOY - O/S User - joins to CONO_X table (Oracle)
- PWMY - Machine - joins to CONM_X table
- IZHO - SQL Hash Value - joins to CONSS_X, CONST_X, CON_SQL_NAME
- KXPI - P1 value from wait interface (Oracle)
- IDSQ - P2 value from wait interface (Oracle)
- KQTZ - P3 value from wait interface (Oracle)
- KEEQ - wait event/wait type - joins to CONEV_X
- UDPW - Program - joins to CONPR_X
- VDSI - SID || '.' || Serial# (Oracle)
- MDCP - unused
- DBML - Module - joins to CONMOD_X (Oracle)
- RMAL - Action - joins to CONACT_X (Oracle)
- BLEE - blockee ID
- BLER - blocker ID
- ORPH - plan hash value - joins to CONPT_X
- HGOB - object id - joins to CONOBJ_X (Oracle)
- CTFL - file id - joins to CONF_X (Oracle)
- QP - frequency of polling in centiseconds - divide by 100 to get seconds
- D - timestamp of the activity

see also some example alerts that use the tables CONSW like this one https://thwack.solarwinds.com/t5/DPA-Documents/Custom-Alert-Average-Blocking-Time-per-Session/ta-p/535898