→ 🚀What's New? Explore Lansweeper's Fall 2024 Updates! Fall Launch Blog !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Powen
Engaged Sweeper II
Hi everyone,

I want to write a report, is to find out if each computer event log has

「Device \Device\Harddisk0\DR0 has bad blocks」



This record, find out the damaged block of the hard disk in advance and replace it, thank you
4 REPLIES 4
rom
Champion Sweeper III
Great!

you can change this line:

And tblNtlog.TimeGenerated > GetDate() - 7



to

And tblNtlog.TimeGenerated > GetDate() - 30
Powen
Engaged Sweeper II
Rom wrote:
Great!

you can change this line:

And tblNtlog.TimeGenerated > GetDate() - 7



to

And tblNtlog.TimeGenerated > GetDate() - 30


Hi ROM,
Got it, thank you very much for your help.
rom
Champion Sweeper III
Powen.cheng wrote:
Hi everyone,

I want to write a report, is to find out if each computer event log has

「Device \Device\Harddisk0\DR0 has bad blocks」



This record, find out the damaged block of the hard disk in advance and replace it, thank you


What I do is take the event logs generated in X days report, and modify it (save as, dont overwrite the report)


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype
When 1 Then 'Error'
When 2 Then 'Warning'
When 3 Then 'Information'
When 4 Then 'Security Audit Success'
When 5 Then 'Security Audit Failure'
End As EventType,
tblNtlog.TimeGenerated,
tblNtlogSource.Sourcename,
tblNtlogFile.Logfile,
tblNtlogUser.Loguser,
tblNtlogMessage.Message
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblNtlog On tblNtlog.AssetID = tblAssets.AssetID
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 tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where Case tblNtlog.Eventtype
When 1 Then 'Error'
When 2 Then 'Warning'
When 3 Then 'Information'
When 4 Then 'Security Audit Success'
When 5 Then 'Security Audit Failure'
End = 'Error' And tblNtlog.TimeGenerated > GetDate() - 7 And
tblState.Statename = 'Active'
and tblNtlogSource.Sourcename = 'Disk' and tblNtlog.Eventcode=7
Order By tblNtlog.TimeGenerated Desc,
tblAssets.Domain,
tblAssets.AssetName
Powen
Engaged Sweeper II
Rom wrote:
Powen.cheng wrote:
Hi everyone,

I want to write a report, is to find out if each computer event log has

「Device \Device\Harddisk0\DR0 has bad blocks」



This record, find out the damaged block of the hard disk in advance and replace it, thank you


What I do is take the event logs generated in X days report, and modify it (save as, dont overwrite the report)


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype
When 1 Then 'Error'
When 2 Then 'Warning'
When 3 Then 'Information'
When 4 Then 'Security Audit Success'
When 5 Then 'Security Audit Failure'
End As EventType,
tblNtlog.TimeGenerated,
tblNtlogSource.Sourcename,
tblNtlogFile.Logfile,
tblNtlogUser.Loguser,
tblNtlogMessage.Message
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblNtlog On tblNtlog.AssetID = tblAssets.AssetID
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 tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where Case tblNtlog.Eventtype
When 1 Then 'Error'
When 2 Then 'Warning'
When 3 Then 'Information'
When 4 Then 'Security Audit Success'
When 5 Then 'Security Audit Failure'
End = 'Error' And tblNtlog.TimeGenerated > GetDate() - 7 And
tblState.Statename = 'Active'
and tblNtlogSource.Sourcename = 'Disk' and tblNtlog.Eventcode=7
Order By tblNtlog.TimeGenerated Desc,
tblAssets.Domain,
tblAssets.AssetName


Hi ROM,
Thank you very much for your report. It is effective after testing, but I would like to ask you about modifying the x day. I want to get the Event log of a month or a week before querying the report. Where should I modify it? Thanks!