BusinessObjects Board

Is there a way to find 'Who is running this SQL'

other Auditor
#1

If I know the SQL thats being run, can I find out using Auditor who is running it? We are using SQL Server 2000 and we use SQL Server Authentication.

Thanks,
Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#2

Hi,

Haven’t you got any information under SQL server ? Under Oracle you can know this ! But i don’t think you can do any link with auditor data. May be except if for each report associated via a macro SQL generated ?

Regards

#3

Thanks for your reply. I am using SQL Server authentication and we created an account ‘bo’ for Business Objects usage. Wheneve someone runs anything, for the DBA it shows as ‘bo’ is tunning it but at times we need to find out who exactly is running this.

Hope this explains it better.

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#4

Do you have the info which report hosts that SQL? We can get the IP Address of the node using the system. Maybe that can provide us some information and ultimately find the user who runs it.

Sridharan :india: (BOB member since 2002-11-08)

#5

Thanks for your reply Sri. The problem is that it was not a canned report. It was a report that someone created on the fly using Webi that kept on running and blocked some resources.

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#6

One more way would be if that request is the longest running one then we can create a report which takes the longest duration. But I think the SQL might be captured by the Audit tables. I have seen some SQL statement kinda values in one of the Audit Tables sometime back while working on some Audit request. Maybe I can check upon that.

Sridharan :india: (BOB member since 2002-11-08)

#7

I went through all the audot tables and did not find any columns that looked like the captured sql :cry:
Thanks again for your help :!:

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#8

Hi,

SQL is not stored under audit tables. But you can have it using a simple macro :wink:

Regards

#9

obj_a_event_detl seems to have a column where there are some SQL like syntax stored. Not sure if they are actual captured SQL (need to confirm) but I do see SELECT statements, GROUP BY statements and other SQL keywords.

Sridharan :india: (BOB member since 2002-11-08)

#10

Goffin,

Do you happen to have this simple macro :wink: Can you please share it?

Sri,
I have got 4 columns in this table (a_evdet_n_id, a_evdet_n_objectid, a_evdet_c_objname, a_evdet_n_objtypid) and none of them have anything like the SQL statment. Could I be missing some columns in this table?

Thanks for your help.

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#11

Hi,

No SQL of the query stored under OBJ_A tables.

Have a look to dwayne’s macro. (Objects used in reports) then add this line

dpMyDataProvider.SQL

instead of

dpMyDataProvider.Columns.Item(k).Name

Hope this will help you

Regards

#12

Thaks for your reply Goiffon. Are you referring to this topic. It says that this macro is for documents within a folder and I am trying to capture the sql and who is running it trrough Webi.

Hope this explains it.

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#13

Hi,

Yes it’s the right topic. No what i told you is that with this macro you can know the SQL of each document. Make a link with the id of these doc and their SQL and with information stored under audit tables OBJ_A*

Regards

#14

Thats the column Kashif. I just checked the table on our site and do see some SQL query across multiple rows in A_EVDET_C_OBJNAME of table OBJ_A_EVENT_DETL. Again I’m not if they are report SQL as there are only fewer rows when I checked in that table and we have hundreds of reports that are being run by users across locations with audit on and so there should be lot of rows. Not sure how those SQL statements came in those.

Sridharan :india: (BOB member since 2002-11-08)