→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
asifmcp
Engaged Sweeper II

Hi,

I want to see bad block for group of computers as below scenario:

I have host name that start with different strings i.e., KFMIT001 - KFMIT100, KFMIJR001 - KFMIJR100

I want to see bad block of computers group that contains KFMITxxx as mentioned above.

Is there any way, please guide.

1 ACCEPTED SOLUTION
jduke_halls
Engaged Sweeper III

So in that case:

  • you can either use the default report and use the filters at the top to put in what you want to see and export (as seen below)

jduke_halls_1-1701189111411.png

  • or you can create multiple reports from the SQL above and change the 'KFMIT%' on line 33 to whichever prefix you prefer for each report, making sure to have the trailing %, as this is the wildcard character in MSSQL
  • or do as @Mister_Nobody said and add all the prefixes to one report. Example from line 33 above
Where (tblAssets.AssetName Like 'KFMIT%' OR tblAssets.AssetName Like 'KFMIJR%' OR tblAssets.AssetName Like 'KFMTEA%') And tblNtlogMessage.Message Like
  '%bad block%' And tblNtlogSource.Sourcename = 'disk'
Order By tblNtlog.TimeGenerated Desc

 

 

 

View solution in original post

5 REPLIES 5
asifmcp
Engaged Sweeper II

All of you, thanks for your support.

jduke_halls
Engaged Sweeper III

I'm guessing you're looking to modify the "Assets: Windows PCs with Bad Block" report?  If so, create a new report with the query below, which would give you all the computers that start with KFMIT with Bad Blocks:

 

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  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 tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetName Like 'KFMIT%' And tblNtlogMessage.Message Like
  '%bad block%' And tblNtlogSource.Sourcename = 'disk'
Order By tblNtlog.TimeGenerated Desc

 

asifmcp
Engaged Sweeper II

Yes, you are correct but I want variable, not fixed value every time i.e., sometime KFMIT, sometime KFMIJR, sometime KFMTEA etc.

jduke_halls
Engaged Sweeper III

So in that case:

  • you can either use the default report and use the filters at the top to put in what you want to see and export (as seen below)

jduke_halls_1-1701189111411.png

  • or you can create multiple reports from the SQL above and change the 'KFMIT%' on line 33 to whichever prefix you prefer for each report, making sure to have the trailing %, as this is the wildcard character in MSSQL
  • or do as @Mister_Nobody said and add all the prefixes to one report. Example from line 33 above
Where (tblAssets.AssetName Like 'KFMIT%' OR tblAssets.AssetName Like 'KFMIJR%' OR tblAssets.AssetName Like 'KFMTEA%') And tblNtlogMessage.Message Like
  '%bad block%' And tblNtlogSource.Sourcename = 'disk'
Order By tblNtlog.TimeGenerated Desc

 

 

 

You can edit report yourself and add all your prefixes