File and Folder Access Audit Report

[JRall]

Hello all,

I am using a report I found in the forums for folder/file access auditing using event log events. It works, but I end up with a lot more info than needed and a lot of events for the same file/folder access event.

Has anyone used a Lansweeper report for this? I am looking for a way to combine these events into simple single lines events with what folder or file was accessed and if it was successful or denied access.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblNtlog.Eventcode,
  tblNtlog.TimeGenerated,
  tblNtlogFile.Logfile,
  tblNtlogSource.Sourcename,
  tblNtlogMessage.Message
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
      tblNtlog.SourcenameID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Where tblNtlog.Eventcode In (4656, 4658, 4660, 4663, 4685, 4985) And
  tblAssetCustom.State = 1
Order By tblNtlog.TimeGenerated Desc

I am kind of lost on how to accomplish this or if it is even possible.

Thanks!

[Mister_Nobody]

I think LS administrators have to set information events log collecting.

[JRall]

Yes, that is correct. You would have to be scanning for these log entries in Lansweeper as well.

[JRall]

I wanted to post an updated script. Again, it still may not be the cleanest code, but it appears to be working as expected. The changes made clean it up a bit and add a column for Denied, Granted, WriteDenied as well as color coding of the rows making it easy to scroll through and notice anything that stands out.

I did generalize a few criteria filters that you will want to update for you environment and this does require folder auditing to be enabled.

Select Distinct Top 1000000 tblAssets.AssetID,
  tsysOS.Image As icon,
  tblNtlog.TimeGenerated As Time,
  SubString(tblNtlogMessage.Message, CharIndex('Account Name:',
  tblNtlogMessage.Message) + Len('Account Name:'), CharIndex('Account Domain:',
  tblNtlogMessage.Message) - (CharIndex('Account Name:',
  tblNtlogMessage.Message) + Len('Account Name:'))) As Account,
  SubString(tblNtlogMessage.Message, CharIndex('Object Name:',
  tblNtlogMessage.Message) + Len('Object Name:'), CharIndex('Handle ID:',
  tblNtlogMessage.Message) - (CharIndex('Object Name:', tblNtlogMessage.Message)
  + Len('Object Name:'))) As Folder,
  Case
    When tblNtlogMessage.Message Like '%ReadAttributes:%Granted%' And
      tblNtlogMessage.Message Like '%WriteAttributes:%Not granted%' Then
      'WriteDenied'
    When tblNtlog.Eventtype = '5' Then 'Denied'
    When tblNtlog.Eventtype = '4' Then 'Granted'
    Else 'Unknown'
  End As AccessStatus,
  Case
    When tblNtlogMessage.Message Like '%ReadAttributes:%Granted%' And
      tblNtlogMessage.Message Like '%WriteAttributes:%Not granted%' Then
      '#FCFFAD'
    When tblNtlog.Eventtype = '5' Then '#FFC0C0'
    When tblNtlog.Eventtype = '4' Then '#BCFFB6'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
      tblNtlog.SourcenameID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Where tblNtlog.Eventcode In (4656) And tblNtlogMessage.Message Like
  '%X:\FOLDER_LOCATION%' And tblNtlogMessage.Message Not Like '%FILE_SERVER%' And
  tblNtlogMessage.Message Not Like '%ADMIN_ACCOUNT%' And
  tblNtlogMessage.Message Not Like '%$%' And tblNtlogMessage.Message Not Like
  '%.tmp%' And tblAssetCustom.State = 1
Order By Time Desc

[JRall]

So, I actually decided to try using AI to help me out a bit and was able to get what I needed in a short period of time working with it.

I am sure this could be even better or more useful, but it works for what I needed for now. I am open to ideas or anything that may make it more useful or cleaner.

Select Distinct Top 1000000 tblAssets.AssetID,
  tsysOS.Image As icon,
  tblNtlog.TimeGenerated As Time,
  SubString(tblNtlogMessage.Message, CharIndex('Account Name:',
  tblNtlogMessage.Message) + Len('Account Name:'), CharIndex('Account Domain:',
  tblNtlogMessage.Message) - (CharIndex('Account Name:',
  tblNtlogMessage.Message) + Len('Account Name:'))) As Account,
  SubString(tblNtlogMessage.Message, CharIndex('Object Name:',
  tblNtlogMessage.Message) + Len('Object Name:'), CharIndex('Handle ID:',
  tblNtlogMessage.Message) - (CharIndex('Object Name:', tblNtlogMessage.Message)
  + Len('Object Name:'))) As Folder
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
      tblNtlog.SourcenameID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Where tblNtlog.Eventcode In (4656) And tblNtlogMessage.Message Like
  '%FOLDER LOCATION%' And tblNtlogMessage.Message Not Like '%SERVER NAME%' And
  tblAssetCustom.State = 1
Order By Time Desc

Note - I did generalize the shared location and server I had set in the filter criteria that will need changed or removed before using.

[Mercedes_O]

Thank you for sharing this report, I will pin this to the top of the forum too

[JRall]

Not a problem, sounds good to me. The only thing I would like to add to it is a column and/or color code the rows based on if access was denied or granted. I haven't been able to find a reliable way of doing this since some events will have both "Granted" and "Not granted" in the same event message.

[JRall]

Thought I would bump this and see if anyone might provide some help. I have even tried trimming the log message to just the folder and the account that accessed it, but I didn't have much luck figuring out how to do that using SubString and CharIndex.

[JRall]

I should clarify, what would be best is to have a report with mainly 3 columns (Folder/File, User Account that was used, Access was Granted/Denied).