→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
HammettMike
Engaged Sweeper III
I have a report that gives me all occurrences of event logs with disk related errors and which machine that happened on, warranty end date, etc. However, I'm trying to add a couple columns that convert the format the identifier is in the event log to the format LANSweeper keeps it in tblFloppy.Name and then returning the InterfaceType and Model.

I'm trying some cases, but it's returning all models and interfacetypes as "other". I'm not really a SQL guy, just a Googler.

Select Top 1000000 tblAssets.AssetName,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlog.TimeGenerated,
tblNtlogMessage.Message,
tblAssets.AssetID,
tblAssetCustom.Warrantydate,
Case
When tblNtlogMessage.Message Like '\Device\Harddisk0' Then (Select
tblFloppy.Model Where tblFloppy.Name = '\\.\PHYSICALDRIVE0')
When tblNtlogMessage.Message Like '\Device\Harddisk1' Then (Select
tblFloppy.Model Where tblFloppy.Name = '\\.\PHYSICALDRIVE1') Else 'Other'
End As DriveModel,
Case
When tblNtlogMessage.Message Like '\Device\Harddisk0' Then (Select
tblFloppy.InterfaceType Where tblFloppy.Name = '\\.\PHYSICALDRIVE0')
When tblNtlogMessage.Message Like '\Device\Harddisk1' Then (Select
tblFloppy.InterfaceType Where tblFloppy.Name = '\\.\PHYSICALDRIVE1')
Else 'Other' End As DriveInterface
From tblAssets
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 tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblNtlogSource.Sourcename = 'Disk' And tblNtlog.Eventcode = '7') Or
(tblNtlogSource.Sourcename = 'Disk' And tblNtlog.Eventcode = '55') Or
(tblNtlogSource.Sourcename = 'Disk' And tblNtlog.Eventcode = '11')
Order By tblNtlog.TimeGenerated Desc
9 REPLIES 9
DaRiddler324
Engaged Sweeper II
Is there a way to remove all of the duplicates under Asset Name and just go with the Last TimeGenerated? I'm not smart enough to figure it out
DaRiddler324 wrote:
Is there a way to remove all of the duplicates under Asset Name and just go with the Last TimeGenerated? I'm not smart enough to figure it out


There is, but it doesn't mean enough to me to look it up. 🙂

I leave them in there to gauge the severity of the issue.
Mister_Nobody
Honored Sweeper II
BUT I think \Device\Harddisk0 is not equal \\.\PHYSICALDRIVE0 in many cases.
https://support.microsoft.com/en-us/kb/159865
Mister_Nobody
Honored Sweeper II
My report without Case (support HD from 0 up to 9):
Select Top 1000000 t.AssetName,
t.Sourcename,
t.Eventcode,
Max(t.TimeGenerated) lasttimegenerated,
t.Message,
t.AssetID,
t.DriveInterface,
t.DriveModel
From (Select tblAssets.AssetName,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlog.TimeGenerated,
tblNtlogMessage.Message,
tblAssets.AssetID,
IsNull((Select tblFloppy.InterfaceType From tblFloppy
Where tblAssets.AssetID = tblFloppy.AssetID And
tblFloppy.Name Like '%PHYSICALDRIVE' + SubString(tblNtlogMessage.Message,
CharIndex('Harddisk', tblNtlogMessage.Message) + 8, 1) + '%'),
'Other') DriveInterface,
IsNull((Select tblFloppy.Model From tblFloppy
Where tblAssets.AssetID = tblFloppy.AssetID And
tblFloppy.Name Like '%PHYSICALDRIVE' + SubString(tblNtlogMessage.Message,
CharIndex('Harddisk', tblNtlogMessage.Message) + 8, 1) + '%'),
'Other') DriveModel
From tblAssets
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Where tblNtlogSource.Sourcename = 'Disk' And (tblNtlog.Eventcode = '7' Or
tblNtlog.Eventcode = '55' Or tblNtlog.Eventcode = '11')) t
Group By t.AssetName,
t.Sourcename,
t.Eventcode,
t.Message,
t.AssetID,
t.DriveInterface,
t.DriveModel
Order By lasttimegenerated Desc
Mister_Nobody
Honored Sweeper II
I use this simple report:

Select Distinct Top 10000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlogMessage.Message,
tsysIPLocations.IPLocation,
Max(tblNtlog.TimeGenerated) lastTimeGenerated
From tblNtlog
Inner Join tblAssets On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Where tblNtlogSource.Sourcename = 'disk' And tblNtlog.Eventcode = '7'
Group By tblAssets.AssetID,
tblAssets.AssetUnique,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlogMessage.Message,
tsysIPLocations.IPLocation
Order By lastTimeGenerated Desc


In your report there is problem not all disks is enumerated. For example, we have disk Harddisk7.
Mister Nobody wrote:
I use this simple report:

Select Distinct Top 10000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlogMessage.Message,
tsysIPLocations.IPLocation,
Max(tblNtlog.TimeGenerated) lastTimeGenerated
From tblNtlog
Inner Join tblAssets On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Where tblNtlogSource.Sourcename = 'disk' And tblNtlog.Eventcode = '7'
Group By tblAssets.AssetID,
tblAssets.AssetUnique,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlogMessage.Message,
tsysIPLocations.IPLocation
Order By lastTimeGenerated Desc


In your report there is problem not all disks is enumerated. For example, we have disk Harddisk7.


Your report doesn't tell me what type of disk it is, which is where I was going with this. That helps determine if it's the factory disk, some other disk, a USB stick, etc. I also added a few more cases (up to nine).
fjca
Champion Sweeper II
ATAPI is for Optical Drives, CDROM, DVDs, etc.
They are not on the tblFloppy table, but on tbl.CDROMDrive.

HammettMike
Engaged Sweeper III
fjca wrote:
ATAPI is for Optical Drives, CDROM, DVDs, etc.
They are not on the tblFloppy table, but on tbl.CDROMDrive.



This looks like drive C: is registered as ATAPI on this Lenovo M83. Either that or it's a coincidence that there are ATAPI issues when there's no disk in the drive and that's shortly followed by an error on drive C:.
HammettMike
Engaged Sweeper III
Okay, I got that fixed up. I wasn't using % in my like statements. However, I noticed that some machines present the errors as ATAPI errors. I'm now having a hard time correlating the event log message with a piece of hardware.

Select Top 1000000 tblAssets.AssetName,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlog.TimeGenerated,
tblNtlogMessage.Message,
tblAssets.AssetID,
tblAssetCustom.Warrantydate,
Case
When tblNtlogMessage.Message Like '%Harddisk0%' Then (Select
tblFloppy.InterfaceType Where tblFloppy.Name Like '%PHYSICALDRIVE0%')
When tblNtlogMessage.Message Like '%Harddisk1%' Then (Select
tblFloppy.InterfaceType Where tblFloppy.Name Like '%PHYSICALDRIVE1%')
Else 'Other' End As DriveInterface,
Case
When tblNtlogMessage.Message Like '%\Harddisk0%' Then (Select
tblFloppy.Model Where tblFloppy.Name Like '%PHYSICALDRIVE0%')
When tblNtlogMessage.Message Like '%\Harddisk1%' Then (Select
tblFloppy.Model Where tblFloppy.Name Like '%PHYSICALDRIVE1%') Else 'Other'
End As DriveModel
From tblAssets
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 tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblNtlogSource.Sourcename = 'Disk' And tblNtlog.Eventcode = '7') Or
(tblNtlogSource.Sourcename = 'Disk' And tblNtlog.Eventcode = '55') Or
(tblNtlogSource.Sourcename = 'Disk' And tblNtlog.Eventcode = '11') Or
(tblNtlogSource.Sourcename = 'atapi' And tblNtlog.Eventcode = '7') Or
(tblNtlogSource.Sourcename = 'atapi' And tblNtlog.Eventcode = '55') Or
(tblNtlogSource.Sourcename = 'atapi' And tblNtlog.Eventcode = '11')
Order By tblNtlog.TimeGenerated Desc