CJSmith dot me

I dump stuff I find useful here

Check the audit on Business Objects XIR3 SP7 for logons

Published / by Chris Smith / Leave a Comment

This is useful to see who is using their licenses which are very expensive. I tend to check every few months and look at 90 days.
Its a simple count excluding the System Account and look at a date range.
I have only run this against XIR3 SP7 where auditing has been enabled on an environment running Windows and Microsoft SQL Server (Obviously it can run on almost most OS and database configs).

USE <XIR3AuditDatabase>
SELECT MAX(AUDIT_EVENT.User_Name) as "User_Name", COUNT(*) as Logins
FROM
  AUDIT_EVENT
WHERE
   AUDIT_EVENT.User_Name  <>  'System Account'   AND
   AUDIT_EVENT.Event_Type_ID  IN  ( 65537, 65538  )   AND
   AUDIT_EVENT.Start_Timestamp  BETWEEN  GETDATE() -90  AND GETDATE()
  GROUP BY AUDIT_EVENT.User_Name
  order by AUDIT_EVENT.User_Name

I have no idea where Business Objects stores the user list, so unfortunatly I can’t easily use a join to show where users havent logged in.

Leave a Reply