4 Replies Latest reply on Mar 31, 2015 3:35 PM by mandevil

    finding SQL with a string

    nicolasdiogo

      hello

       

       

      sorry for the title of this question - please suggest an update.

       

      We have a situation that requires an audit, and since DPA has been collecting details on the SQL Scripts run on the server.

      I would like to find out if it would be possible to find a query that contains a given string? (eg: an object name {table, view, procedure, function})

       

      It would be great if DPA could assist us in this kind of scenario.

       

       

      Thanks a lot,

       

      Nicolas

        • Re: finding SQL with a string
          deedsd

          There are a couple of ways to do that.  Is your repository running Oracle or something else?  You can use the repo query tool (options .. support .. database query tool)  to determine which id you are interested based on the name of the database you want to search.  You then search const_xx on the st field for the string you want to find...

           

          select id,name from cond where name like '%MY_DB_NAME_HERE%';

           

          select st from const_ID_FROM_ABOVE_QUERY where upper(st) like '%STRING_YOU_WISH_TO_SEARCH%';

           

          If you have Oracle for the repository, you can do all of the regular expression matching as well.  Other engines may have that ability, but I don't know about it.

            • Re: finding SQL with a string
              nicolasdiogo

              Thanks very much for that.

               

              i have found the queries. and one that i was particularly interested on.

              what is the way to link the query with the username used for running this query?

               

               

              thanks,

                • Re: finding SQL with a string
                  deedsd

                  I don't know how to map it back to a username.  I don't have the DPA ERD .

                   

                  I usually hit conss_xx for the datestamps for the particular SQL hash you can pull from above, and then use the GUI to figure out which users were running the statement.  Solarwinds Support might be willing to help you get the data from the repo tables, I would suggest you open a case on it.

                    • Re: finding SQL with a string
                      mandevil

                      If you have the SQL hash value stored in DPA, you can get the user running it using the following:

                       

                      select st, h from const_ID_FROM_ABOVE_QUERY where upper(st) like '%STRING_YOU_WISH_TO_SEARCH%';

                      -- using the h for the hash from that query:

                      select name from conu_xx, consw_xx where consw_xx.izho = <hash value from above> and conu_xx.id = consw_xx.xcuw;

                       

                      Sorry for the cryptic naming...  I like deedsd answer also.

                       

                      CAVEAT:  DPA was not meant to be used as an auditing tool.  We poll once every second and can miss executions of SQL statements.  We are focused on performance/tuning.