locked
Analyse C2 Auditing trace files data in SQL Server RRS feed

  • Question

  • Hi,

    I enabled c2 auditing on a sql server and loaded those files in to a table for analysing last one month database activity. 

    Requirement: I want to list out all the users who accessed any database in that server.

    Can you please let me know what data should i analyse and also can you provide me the query for analysing data.

     

    SELECT TOP 1000 [TextData]
          ,[BinaryData]
          ,[DatabaseID]
          ,[TransactionID]
          ,[LineNumber]
          ,[NTUserName]
          ,[NTDomainName]
          ,[HostName]
          ,[ClientProcessID]
          ,[ApplicationName]
          ,[LoginName]
          ,[SPID]
          ,[Duration]
          ,[StartTime]
          ,[EndTime]
          ,[Reads]
          ,[Writes]
          ,[CPU]
          ,[Permissions]
          ,[Severity]
          ,[EventSubClass]
          ,[ObjectID]
          ,[Success]
          ,[IndexID]
          ,[IntegerData]
          ,[ServerName]
          ,[EventClass]
          ,[ObjectType]
          ,[NestLevel]
          ,[State]
          ,[Error]
          ,[Mode]
          ,[Handle]
          ,[ObjectName]
          ,[DatabaseName]
          ,[FileName]
          ,[OwnerName]
          ,[RoleName]
          ,[TargetUserName]
          ,[DBUserName]
          ,[LoginSid]
          ,[TargetLoginName]
          ,[TargetLoginSid]
          ,[ColumnPermissions]
          ,[LinkedServerName]
          ,[ProviderName]
          ,[MethodName]
          ,[RowCounts]
          ,[RequestID]
          ,[XactSequence]
          ,[EventSequence]
          ,[BigintData1]
          ,[BigintData2]
          ,[GUID]
          ,[IntegerData2]
          ,[ObjectID2]
          ,[Type]
          ,[OwnerID]
          ,[ParentName]
          ,[IsSystem]
          ,[Offset]
          ,[SourceDatabaseID]
          ,[SqlHandle]
          ,[SessionLoginName]
          ,[PlanHandle]
          ,[GroupID]
      FROM [audit].[dbo].[audit]

    Tuesday, April 2, 2013 11:39 PM

Answers

  • Here would be a query to get you started:

    This will list out each UserName and Domain and databases they accessed along with a count of how many times accessed.

    SELECT

    [NTUserName]
          ,[NTDomainName]
         ,[DatabaseName]
          ,[DBUserName]

    ,count(*) AccessCount


      FROM [audit].[dbo].[audit]
    where [StartTime] >  dateadd(day, -30, getdate() ) -- or set a specific date to get the last month

    group by 

    [NTUserName]
          ,[NTDomainName]
         ,[DatabaseName]
          ,[DBUserName]



    • Proposed as answer by NateInAZ Wednesday, April 3, 2013 12:07 AM
    • Marked as answer by SQListic Wednesday, April 3, 2013 12:28 AM
    • Edited by NateInAZ Wednesday, April 3, 2013 1:20 AM
    Wednesday, April 3, 2013 12:03 AM

All replies

  • Here would be a query to get you started:

    This will list out each UserName and Domain and databases they accessed along with a count of how many times accessed.

    SELECT

    [NTUserName]
          ,[NTDomainName]
         ,[DatabaseName]
          ,[DBUserName]

    ,count(*) AccessCount


      FROM [audit].[dbo].[audit]
    where [StartTime] >  dateadd(day, -30, getdate() ) -- or set a specific date to get the last month

    group by 

    [NTUserName]
          ,[NTDomainName]
         ,[DatabaseName]
          ,[DBUserName]



    • Proposed as answer by NateInAZ Wednesday, April 3, 2013 12:07 AM
    • Marked as answer by SQListic Wednesday, April 3, 2013 12:28 AM
    • Edited by NateInAZ Wednesday, April 3, 2013 1:20 AM
    Wednesday, April 3, 2013 12:03 AM
  • Error

    Msg 155, Level 15, State 1, Line 13

    'days' is not a recognized dateadd option.(can you please fix this)

    I made some modifications and got what i want..

    AWESOME....

    Thank you so much

    Wednesday, April 3, 2013 12:31 AM