This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Machine, OS User, DB User - Custom Report

thomasoates over 7 years ago

I'm working with our DBAs in DPA asking for a report of Machines, OS Users, DB Users in a three column report showing all the unique combinations of the three columns who have connected and run SQL in 2017 in selected DBs. Including a count of the number of SQL submisions for each group would be a bonus.

Is that possible? How do we do that?

I'm a limited access user so the DBAs would need to create the custom report.

Appreciate any assistance.

Top Replies

jaminsql over 7 years ago in reply to houghjohnny +1

I'm a bit confused as above you said you had a Oracle repository. The tables are the same but, is the database that DPA is storing the data on MySQL or Oracle?

0 jaminsql over 7 years ago

Very possible. Given the structure of DPA's repository it some times needs to be either T SQL or PL/SQL to replace some table names dynamically. Is your DPA repository Oracle, SQL server, MySQL?
Cancel
Vote Up 0 Vote Down

Cancel
0 thomasoates over 7 years ago in reply to jaminsql

Oracle
Cancel
Vote Up 0 Vote Down

Cancel
0 jaminsql over 7 years ago in reply to thomasoates

So there are some reports that are here on THWACK that have some of this now.
Custom Report - Top SQL by Database User
Custom Report - Top SQL for a Machine
Custom Report - Top SQL by O/S User
In General as a starter for you DPA has sort of two star schema on for detail data and one for Sum data. The detail data is where these are polling from with the CONSW_DBID table in the center. Where DBID is a number in the table COND;
CONO_DBID has the O/S User for Oracle servers and the Database name for SQL server instances. CONM_DBID has the Machine and CONU_DBID the database user.
you may need to do some joins to get them all in one report but, these should show your DBA's the structure.
Cancel
Vote Up 0 Vote Down

Cancel
0 houghjohnny over 7 years ago in reply to jaminsql

I need a query for a MySQL repository database that combines all three reports data. I need to know what machines login with each user and the queries they run. I have the canned reports but don't know how to combine the data or get the underlying ddl for the reports please help.
Cancel
Vote Up 0 Vote Down

Cancel
0 jaminsql over 7 years ago in reply to houghjohnny

I'm a bit confused as above you said you had a Oracle repository. The tables are the same but, is the database that DPA is storing the data on MySQL or Oracle?
Cancel
Vote Up +1 Vote Down

Cancel
0 houghjohnny over 7 years ago in reply to jaminsql

My apologies for the confusion the original question posed by another user was for a Oracle database.
My separate question has to do with a MySQL database.
Cancel
Vote Up 0 Vote Down

Cancel