cancel
Showing results for 
Search instead for 
Did you mean: 

User activity Log

Former Member
0 Kudos

Dear all SAP expert,

I would like to know where i can get data about user activity log for auditing purpose. I would like to know, which module that user opened, what time etc.

Please help and Thanks a lot for your help

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hai!

1. Transaction related log's you can get it from AJDT.

2. Document related log's from ADOC.

3. Item Master log's from AITM.

4. BP Master log's from ACRD.

Regards,

Thanga Raj.K

Former Member
0 Kudos

Dear Raj,

Thanks for your reply, By the way, is there any query that can summarize all activity. For example, I want to see history activity of user Cashier. From that query, I can see her history from certain period..

Thanks

Former Member
0 Kudos

Hai!

Try this.

Log For BP Master.


Declare @FromDate as Datetime
Declare @ToDate as DateTime
Declare @User as numeric
Set @FromDate = (Select min(S1.UpdateDate) From ACRD S1 Where S1.UpdateDate >= '[%0]')
Set @ToDate = (Select max(S2.UpdateDate) From ACRD S2 Where S2.UpdateDate <= '[%1]')
Set @user = (select max(S3.USERID) From OUSR S3 Where S3.U_NAME='[%2]')
select * from ACRD C0
where
C0.loginstanc <> 1 and
C0.UserSign=@user and
C0.Updatedate >=@FromDate and
C0.UpdateDate <=@ToDate

Regards,

Thanga Raj.K

Edited by: Thanga Raj K on Jun 15, 2009 10:46 AM

Former Member
0 Kudos

hai!

Log for Documents


Declare @FromDate as Datetime
Declare @ToDate as DateTime
Declare @User as numeric
Set @FromDate = (Select min(S1.UpdateDate) From ACRD S1 
Where S1.UpdateDate >= '[%0]')
Set @ToDate = (Select max(S2.UpdateDate) From ACRD S2 
Where S2.UpdateDate <= '[%1]')
Set @user = (select max(S3.USERID) From OUSR S3 
Where S3.U_NAME='[%2]')

Select 
(CASE WHEN A0.ObjType = '-2' THEN 'Opening Balance'
WHEN A0.ObjType = '22' THEN 'Purchase Order'
WHEN A0.ObjType = '23' THEN 'Sales Quotations'
WHEN A0.ObjType = '17' THEN 'Sales Orders'
WHEN A0.ObjType = '13' THEN 'AR Invoice' 
WHEN A0.ObjType = '14' THEN 'AR Credit Memo'
WHEN A0.ObjType = '15' THEN 'Delivery' 
WHEN A0.ObjType = '16' THEN 'Return'
WHEN A0.ObjType = '18' THEN 'AP Invoice'
WHEN A0.ObjType = '19' THEN 'AP Credit Memo' 
WHEN A0.ObjType = '20' THEN 'Goods Receipt PO' 
WHEN A0.ObjType = '202' THEN 'Production Order' 
WHEN A0.ObjType = '21' THEN 'Goods Return' 
WHEN A0.ObjType = '24' THEN 'Incoming Payments' 
WHEN A0.ObjType = '30' THEN 'Journal Entry'
WHEN A0.ObjType = '46' THEN 'Outgoing Payments' 
WHEN A0.ObjType = '58' THEN 'Stock Posting' 
WHEN A0.ObjType = '59' THEN 'Goods Receipt/Receipt from production' 
WHEN A0.ObjType = '60' THEN 'Goods Issue/Issue from Production' 
WHEN A0.ObjType = '67' THEN 'InventoryTransfer'
WHEN A0.ObjType = '69' THEN 'Landed Costs' 
WHEN A0.ObjType = '162' THEN 'Inventory Revaluation' 
WHEN A0.ObjType = '140000009' THEN 'Outgoing Excise Invoice' 
WHEN A0.ObjType = '140000010' THEN 'Incoming Excise Invoice' ELSE 'NULLVALUE' END) AS 'Transaction Name',
* From ADOC A0,ADO1 A1
Where
A0.DocEntry=A1.DocEntry and
A0.loginstanc <> 1 and
A0.UserSign2=@User and
A0.UpdateDate >=@FromDate and
A0.UpdateDate <=@Todate

Regards,

Thanga Raj.K

Former Member
0 Kudos

Hai!

Log For Item Master


Declare @FromDate as datetime
Declare @ToDate as datetime
Declare @Uname as int
set @FromDate = (select min(S0. UpdateDate) from AITM S0 where S0. UpdateDate >= '[%0]')
set @ToDate = (select max(S1. UpdateDate) from AITM S1 where S1. UpdateDate <= '[%1]')
set @Uname = (select max(s2.userid) from OUSR S2 Where s2.u_name = '[%2]')

select 
* from AITM A1
where
A1. UpdateDate >=@FromDate and
A1. UpdateDate <= @Todate and
A1.loginstanc <> 1 and
A1.Usersign = @Uname

Regards,

Thanga Raj.K

Former Member
0 Kudos

Hai!

Log For transcation Journals

************************************************************************************************************************

Declare @FromDate as datetime

Declare @ToDate as datetime

Declare @Uname as int

set @FromDate = (select min(S0.RefDate) from AJDT S0 where S0.RefDate >= '[%0]')

set @ToDate = (select max(S1.RefDate) from AJDT S1 where S1.RefDate <= '[%1]')

set @Uname = (select max(s2.userid) from OUSR S2 Where s2.u_name = '[%2]')

select case A1.TransType

when 1 then 'oChartOfAccounts'

when 2 then 'oBusinessPartners'

when 3 then 'oBanks'

when 4 then 'oItems'

when 5 then 'oVatGroups'

when 6 then 'oPriceLists'

when 7 then 'oSpecialPrices'

when 8 then 'oItemProperties'

when 12 then 'oUsers'

when 13 then 'oInvoices'

when 14 then 'oCreditNotes'

when 15 then 'oDeliveryNotes'

when 16 then 'oReturns'

when 17 then 'oOrders'

when 18 then 'oPurchaseInvoices'

when 19 then 'oPurchaseCreditNotes'

when 20 then 'oPurchaseDeliveryNotes'

when 21 then 'oPurchaseReturns'

when 22 then 'oPurchaseOrders'

when 23 then 'oQuotations'

when 24 then 'oIncomingPayments'

when 28 then 'oJournalVouchers'

when 30 then 'oJournalEntries'

when 31 then 'oStockTakings'

when 33 then 'oContacts'

when 36 then 'oCreditCards'

when 37 then 'oCurrencyCodes'

when 40 then 'oPaymentTermsTypes'

when 42 then 'oBankPages'

when 43 then 'oManufacturers'

when 46 then 'oVendorPayments'

when 48 then 'oLandedCostsCodes'

when 49 then 'oShippingTypes'

when 50 then 'oLengthMeasures'

when 51 then 'oWeightMeasures'

when 52 then 'oItemGroups'

when 53 then 'oSalesPersons'

when 56 then 'oCustomsGroups'

when 57 then 'oChecksforPayment'

when 58 then 'Inventory OpeningBalance'

when 59 then 'oInventoryGenEntry'

when 60 then 'oInventoryGenExit'

when 64 then 'oWarehouses'

when 65 then 'oCommissionGroups'

when 66 then 'oProductTrees'

when 67 then 'oStockTransfer'

when 68 then 'oWorkOrders'

when 70 then 'oCreditPaymentMethods'

when 71 then 'oCreditCardPayments'

when 73 then 'oAlternateCatNum'

when 77 then 'oBudget'

when 78 then 'oBudgetDistribution'

when 81 then 'oMessages'

when 91 then 'oBudgetScenarios'

when 97 then 'oSalesOpportunities'

when 93 then 'oUserDefaultGroups'

when 101 then 'oSalesStages'

when 103 then 'oActivityTypes'

when 104 then 'oActivityLocations'

when 112 then 'oDrafts'

when 116 then 'oDeductionTaxHierarchies'

when 117 then 'oDeductionTaxGroups'

when 125 then 'oAdditionalExpenses'

when 126 then 'oSalesTaxAuthorities'

when 127 then 'oSalesTaxAuthoritiesTypes'

when 128 then 'oSalesTaxCodes'

when 134 then 'oQueryCategories'

when 138 then 'oFactoringIndicators'

when 140 then 'oPaymentsDrafts'

when 142 then 'oAccountSegmentations'

when 143 then 'oAccountSegmentationCategories'

when 144 then 'oWarehouseLocations'

when 145 then 'oForms1099'

when 146 then 'oInventoryCycles'

when 147 then 'oWizardPaymentMethods'

when 150 then 'oBPPriorities'

when 151 then 'oDunningLetters'

when 152 then 'oUserFields'

when 153 then 'oUserTables'

when 156 then 'oPickLists'

when 158 then 'oPaymentRunExport'

when 160 then 'oUserQueries'

when 162 then 'oMaterialRevaluation'

when 163 then 'oCorrectionPurchaseInvoice'

when 164 then 'oCorrectionPurchaseInvoiceReversal'

when 165 then 'oCorrectionInvoice'

when 166 then 'oCorrectionInvoiceReversal'

when 170 then 'oContractTemplates'

when 171 then 'oEmployeesInfo'

when 176 then 'oCustomerEquipmentCards'

when 178 then 'oWithholdingTaxCodes'

when 182 then 'oBillOfExchangeTransactions'

when 189 then 'oKnowledgeBaseSolutions'

when 190 then 'oServiceContracts'

when 191 then 'oServiceCalls'

when 193 then 'oUserKeys'

when 194 then 'oQueue'

when 198 then 'oSalesForecast'

when 200 then 'oTerritories'

when 201 then 'oIndustries'

when 202 then 'oProductionOrders'

when 205 then 'oPackagesTypes'

when 206 then 'oUserObjectsMD'

when 211 then 'oTeams'

when 212 then 'oRelationships'

when 214 then 'oUserPermissionTree'

when 217 then 'oActivityStatus'

when 218 then 'oChooseFromList'

when 219 then 'oFormattedSearches'

when 221 then 'oAttachments2'

when 223 then 'oUserLanguages'

when 224 then 'oMultiLanguageTranslations'

when 229 then 'oDynamicSystemStrings'

when 231 then 'oHouseBankAccounts'

when 247 then 'oBusinessPlaces'

when 250 then 'oLocalEra'

when 280 then 'oSalesTaxInvoice'

when 281 then 'oPurchaseTaxInvoice'

when 300 then 'BoRecordset'

when 305 then 'BoBridge'

when 260 then 'oNotaFiscalUsage'

when 258 then 'oNotaFiscalCFOP'

when 259 then 'oNotaFiscalCST'

when 261 then 'oClosingDateProcedure'

when 10 then 'oBusinessPartnerGroups'

when 278 then 'oBPFiscalRegistryID'

when '-2' then 'OB'

when 69 then 'Landed Cost'

when 140000009 then 'Outgoing Excise Invoice'

when 140000010 then 'Incoming Excise Invoice'

end 'ObjectName',

  • from AJDT A1

where

A1.Refdate >=@FromDate and

A1.Refdate <= @Todate and

C0.loginstanc <> 1 and

A1.Usersign2 = @Uname

Order By

A1.TransID,A1.TransType

************************************************************************************************************************

Regards,

Thanga Raj.K

Edited by: Thanga Raj K on Jun 15, 2009 10:44 AM

Edited by: Thanga Raj K on Jun 15, 2009 10:45 AM

Former Member
0 Kudos

Hai!

Run all above querys in SAP Query Manager. It will not work in SQL

*Close the thread if answered.

Regards,

Thanga Raj.K

Former Member
0 Kudos

Dear Thanga,

Query for item master and transaction journal is still error

Edited by: Ho We I on Jun 16, 2009 4:58 AM

Former Member
0 Kudos

Hai!

What error it throws?

Regards,

Thanga Raj.K

Former Member
0 Kudos

Dear Thanga,

The error produce when I executed the query is

1). [Microsoft][SQL Native Client][SQL Server]Must specify table to select from.

2). [Microsoft][SQL Native Client][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared.

Former Member
0 Kudos

Hai!

What is ur country verision and patch of SAP B1.

Can u find wheather AITM and AJDT tables are there in your DB.

Open SQL Window, select the Company DB name from the Drop Down on the Icon Bar.

Execute,

Select * from AITM

Select * from AJDT

Give Feedback

Regards,

Thanga Raj.K

Former Member
0 Kudos

Dear Thanga,

I'm using SAP 2007A PL 30

Tables AITM and AJDT is in my DB

Former Member
0 Kudos

Hai!

Run this code in SQL


Declare @FromDate as datetime
Declare @ToDate as datetime
Declare @Uname as int
set @FromDate = (select min(S0. UpdateDate) from AITM S0 where S0. UpdateDate >= '20080401')
set @ToDate = (select max(S1. UpdateDate) from AITM S1 where S1. UpdateDate <= '20090331')
set @Uname = (select max(s2.userid) from OUSR S2 Where s2.u_name = 'manager')
 
select 
* from AITM A1
where
A1. UpdateDate >=@FromDate and
A1. UpdateDate <= @Todate and
A1.loginstanc <> 1 and
A1.Usersign = @Uname

Regards,

Thanga Raj.K

Former Member
0 Kudos

Hi Thanga,

I hope you can help me on my error,

I created a new account for 2013, but i cant add a transaction on  Journal Entry,

This is my error :

[Microsoft][SQL Native Client][SQL Server] Cannot insert the value NULL into column ‘TransId’, table ‘TEST_MPC.dbo.AJDT’; column does not allow nulls. INSERT fails. 2) [Microsoft][SQL Native Client][SQL Server]The statement has been terminated. “(

Please help me on this...

Best Regards,

Jon

kedalenechong
Participant
0 Kudos

Hi Thanga Raj

Is this Query log for BP Master same as the details of Change log history for BP Customer?

I wasnt able to match the details such as Update Date?

Kedalene

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Since this thread already closed by thread owner.  Please post as new discussion to get quick response.

Thanks & Regards,

Nagarajan