Audit and Event Log Reports

[BenTre]

I'm looking to create a report that runs daily for all the event logs for the workstations and servers in the domain. SQL is not something that I work with to often so I'm having some difficulty knowing exactly what it is I'm doing. However I was able to find the following from a forum post on here but when I add my domain to the report it doesn't seem to except it providing the following error: The multi-part identifier "my domain" could not be bound. Not sure what I'm missing.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.SP,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
When 3 Then 'Information' When 4 Then 'Success Audit'
When 5 Then 'Failure Audit' End As Eventtype,
tblNtlogFile.Logfile,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser,
tblNtlog.TimeGenerated
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.Domain = 'YourDomain' And Case tblNtlog.Eventtype
When 1 Then 'Error' When 2 Then 'Warning' When 3 Then 'Information'
When 4 Then 'Success Audit' When 5 Then 'Failure Audit'
End = 'error' And tblNtlog.TimeGenerated > GetDate() - 1 And
tblComputersystem.Domainrole < 2
Order By tblNtlog.TimeGenerated Desc

[AZHockeyNut]

do you mean this? When you comment out your case section in the join clause

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.SP,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
When 3 Then 'Information' When 4 Then 'Success Audit'
When 5 Then 'Failure Audit' End As Eventtype,
tblNtlogFile.Logfile,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser,
tblNtlog.TimeGenerated
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.Domain like '%justicetrax%' /*And Case tblNtlog.Eventtype
When 1 Then 'Error' When 2 Then 'Warning' When 3 Then 'Information'
When 4 Then 'Success Audit' When 5 Then 'Failure Audit'
End = 'error' */
And tblNtlog.TimeGenerated > GetDate() - 1 And
tblComputersystem.Domainrole < 2
Order By tblNtlog.TimeGenerated Desc

It cleans up to :

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.SP,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
When 3 Then 'Information' When 4 Then 'Success Audit'
When 5 Then 'Failure Audit' End As Eventtype,
tblNtlogFile.Logfile,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser,
tblNtlog.TimeGenerated
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.Domain like '%justicetrax%'
And tblNtlog.TimeGenerated > GetDate() - 1 And
tblComputersystem.Domainrole < 2
Order By tblNtlog.TimeGenerated Desc

[BenTre]

This worked thank you. I would also like to include audit logs in this report as well. Currently it is only reporting error logs how would I include this?

[AZHockeyNut]

change this line

Where tblAssets.Domain = 'YourDomain' And Case tblNtlog.Eventtype

to something like this

Where tblAssets.Domain like '%yourdomainname%' And Case tblNtlog.Eventtype

change the yourdomainname to be your domain name, you should only need the first few letters. if your domain is contoso.local then you could put '%conto%' after the work like and before the and above you will then get results..


the % is a wildcard, and the like forces a similar match versus the = with is an exact match