Hello everybody !
I am using BO 4.1.4 bundle. Now that system is in production I would like to audit user activity:
1- User access to repository and report usage 2- which reports are being most accessed and refreshed?
Is there a easy way of doing this or will I fall in Rocket Science domains??
Thank You All
PRP
Listserv Archives (BOB member since 2002-06-25)
Rocket Science, I’m afraid.
You need to create some BO Scripts that log activity to, say, a central CSV file or, more ambitiously, to a central database.
I’m sure lots of people will jump to your assistance with examples. We opted for simple repository access (who logged on and when).
I have attached a copy of the script we use. This must be named BOSOBJ.SPT and must reside in the user’s script directory (mine is C:\Program Files\BusinessObjects\Scripts)
sub OnStart
dim var as BOVariable
set var = Application.Variables.Item(“BOUSER”)
open “Z:\SharedFolder\BOAudit.csv” For Append Access Write as #1
write #1, Var.value, Format(Now,“dd/mm/yyyy hh:m:ss”) close #1
end sub
Basically, open a file on the network, for append (Z:\SharedFolder\BOAudit.csv).
Write current BO user and current date.
Close the file.
Hope this helps,
Steve
Listserv Archives (BOB member since 2002-06-25)
Unfortunetely I don’t know of an easier way and logging activity and I haven’t found a way to log new report information. Basically the following script logs when the user logs in, report refresh information like time elapsed, SQL, user id, etc., and when they log out. I haven’t tested this thouroughly and I haven’t included all the data providers (only the 1st) but it should give you a start… Also this script should be stored in the BusObj script file.
Dim StartTimeSec
Dim EndTimeSec
Dim TotalTimeSec
Dim TotalHours As Integer
Dim TotalMinutes As Integer
Dim TotalSeconds As Integer
Dim BOUser
Dim BODp As BODataProvider
Sub OnStart
BOUser = Application.Variables.Item(“BOUSER”) Open “C:\Program Files\BusinessObjects\BOTime.txt” For Append As #1 Print #1, “”
Print #1, "User ID: " & BOUser & " Logged in on " & now() Close #1
End Sub
Sub OnQuit
BOUser = Application.Variables.Item(“BOUSER”) Open “C:\Program Files\BusinessObjects\BOTime.txt” For Append As #1 Print #1, "User ID: " & BOUser & " Logged off on " & now() Print #1,
“*************************************************************************** ****”
Close #1
End Sub
Sub OnBeforeRefreshDocument
StartTimeSec = (Hour(Time) * 3600) + (Minute(Time) * 60) + Second(Time) End Sub
sub OnAfterRefreshDocument
Set BODp = ActiveDocument.DataProviders.Item(1)
BOUser = Application.Variables.Item(“BOUSER”)
EndTimeSec = (Hour(Time) * 3600) + (Minute(Time) * 60) + Second(Time) TotalTimeSec = EndTimeSec - StartTimeSec
Open “C:\Program Files\BusinessObjects\BOTime.txt” For Append As #1
TotalHours = TotalTimeSec / 3600
TotalMinutes = (TotalTimeSec - (TotalHours * 3600)) / 60 TotalSeconds = TotalTimeSec - (TotalHours * 3600) - (TotalMinutes * 60)
Print #1, " User ID: " & BOUser
Print #1, " Refreshed Date/Time: " & Date & " " & Time Print #1, " Universe: " & BOdp.UniverseName Print #1, " Document Name: " & ActiveDocument.Name Print #1, " SQL: " & BODp.SQL
Print #1, " Elasped Time: Hours: " & TotalHours & " Minutes: " & TotalMinutes & " Seconds: " & TotalSeconds
Close #1
End Sub
Hope this helps…
Kent Johnson
Listserv Archives (BOB member since 2002-06-25)
Is there any way we can save all the queries run by users, let us say in the past six months. We need this for full client users (ver 5.1.1) , not webi users. We need to keep track of the sql generated by the query, how long it took to run, how many rows it retrieved, whether it ran successfully or failed etc. This information is required for any adhoc report run by users through BCA or directly.
Any input will be very much appreciated. Thanks
sudhindra
Listserv Archives (BOB member since 2002-06-25)
This can probably done by creating a VBA addinin BO. As a guide you might want to refer to the BO SDK Object Model and look at the Dataprovider Class. In the archives you will find some examples of similar scripts that you can modify to achieve this.
Hope this provides a good start
Robert Zidyana
Executive Computing Group
From: Sudhindra Rao [SMTP:sudhindra.rao@BMS.COM] Sent: 5 December 2000 8:43
Is there any way we can save all the queries run by users, let us say in the
past six months. We need this for full client users (ver 5.1.1) , not webi users. We need to keep track of the sql generated by the query, how long it
took to run, how many rows it retrieved, whether it ran successfully or failed etc. This information is required for any adhoc report run by users
through BCA or directly.
Any input will be very much appreciated. Thanks
sudhindra
Listserv Archives (BOB member since 2002-06-25)
Robert,
Would this addin reside at the client level or the server level?
Thanks,
Kathy
Listserv Archives (BOB member since 2002-06-25)
Have to be client level, since BO has no process that runs on the server. Most people find this out and then give up since now each client will be writing to a database.
Unless you are in a chargeback environment, this information isn’t very useful.
Another option: Get a good database monitoring tool. I belive some of these tools can do the sort of audit required at the backend. The only downside is that each user will need to have a database id.
Finally, I wonder if BO’s Watchdog tool (in Beta) might be of help.
Listserv Archives (BOB member since 2002-06-25)
Quite a demand, really. A scripted solution would have to be based on each client, and the addin would have to be enabled within each client’s BusinessObjects. Each client now writes to a database (or a log file) but that’s not much more complex than reading from a database which is what they’re intending to do anyway. But what about BCA? Where does that addin get enabled? What if it’s running on Solaris? The database would seem to be the better solution. However, now users are logging in using the universe connections or they are using passthrough. But how do you tell if the SQL being passed to the DB server is from BusinessObjects and not from some other SQL front end tool? Or perhaps the user_ids are the same for transaction entries? My two cents, without knowing more, is that you may need to look at both methods. The database could trace input from the BCA servers by IP address (if necessary) and the client machines can use addins. If you figure it all out, let us know!
Cheers,
Paul Thomas
Listserv Archives (BOB member since 2002-06-25)
for what its worth…
we created views against our database (BOV = business objects views) that only business objects uses but we cannot distinguish weather it is webi or full cleint queries but its what we have started with. 
Listserv Archives (BOB member since 2002-06-25)