Failing HDD Report (with warranty and drive model)

[HammettMike]

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

[DaRiddler324]

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

[HammettMike]

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]

BUT I think \Device\Harddisk0 is not equal \\.\PHYSICALDRIVE0 in many cases.
https://support.microsoft.com/en-us/kb/159865

[Mister_Nobody]

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]

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.

[HammettMike]

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]

ATAPI is for Optical Drives, CDROM, DVDs, etc.
They are not on the tblFloppy table, but on tbl.CDROMDrive.

[HammettMike]

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]

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