What reports do you use daily? Share here

[Mercedes_O]

Hello All!

I saw some great responses here https://community.lansweeper.com/t5/forum/what-reports-do-you-use-on-a-daily-basis/m-p/24780/thread-... so I have started a new discussion thread to see if we can get more examples: Reply or comment to share reports you regularly use.

Mercedes

[Mister_Nobody]

This report shows problems with time on computers (battery, bios time reset).
But to resolve you must clean logs on such computers and remove+rescan assets (or run SQL query: delete from tblntlog where tblNtlog.TimeGenerated > Current_TimeStamp
delete from tblntlog where assetid = 1)

Application errors from future

Select Top 10000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlog.TimeGenerated,
tblNtlogMessage.Message
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Where tblNtlog.TimeGenerated > Current_TimeStamp And tblAssetCustom.State = 1
Order By tblNtlog.TimeGenerated Desc

[Tim_N]

Oh! That's a good one. I hadn't thought about this type of report before. This is super relevant. Honestly, I don't see many CMOS batteries dying anymore. Maybe we recycle computers too quickly 🙂 

Thanks for bringing this one to our attention!

[Tim_N]

@Mister_Nobody these are great! I really enjoy seeing what reports others use so I can recommend to others! 

Thanks for putting the time in to put this stuff out. 

What about other sweepers? What do you use? 

[Mister_Nobody]

Asset: OS is not scanned

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Mac,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lasttried,
  tblAssets1.AssetID As deviceassetid,
  tblAssets1.AssetName As deviceassetname,
  tblSNMPAssetMac.IfIndex As SwitchPort,
  tblSNMPInfo.IfDescription As PortDescription,
  tblSNMPInfo.IfSpeed / 1000000 As ifSpeedMb,
  tblAssets.Lastseen
From tblAssets
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Left Join tblSNMPAssetMac On tblAssets.Mac = tblSNMPAssetMac.AssetMacAddress
  Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblSNMPAssetMac.AssetID
  Left Join tblSNMPInfo On tblAssets1.AssetID = tblSNMPInfo.AssetID And
      tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Where tblAssets.Assettype = -1 And tblAssets.OScode Is Null

[Mercedes_O]

Thanks for sharing @Mister_Nobody !

[Mister_Nobody]

Event: Disks need to be CHKDSKed

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 = 'ntfs' And tblNtlog.Eventcode = '55'
Group By tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblNtlogSource.Sourcename,
  tblNtlog.Eventcode,
  tblNtlogMessage.Message,
  tsysIPLocations.IPLocation
Order By lastTimeGenerated Desc

[Mister_Nobody]

I think it is standard report.
Asset: Hard drive S.M.A.R.T. status not OK

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblFloppy.Manufacturer As [HD Manufacturer],
  tblFloppy.Model As [HD Model],
  tblFloppy.Size,
  tblFloppy.FirmwareRevision,
  tblFloppy.Status,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblFloppy
  Inner Join tblAssets On tblFloppy.AssetID = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tsysIPLocations On tblAssets.LocationID =
      tsysIPLocations.LocationID
Where tblFloppy.Status Is Not Null And tblFloppy.Status <> 'OK' And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Mister_Nobody]

Event: Disks with BADs

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

[Mister_Nobody]

Asset: Duplicate Asset MAC address

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Mac,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Assettype
From tblAssets
  Inner Join (Select Top 1000000 Count(tblAssets.AssetID) As MACCount,
      tblAssets.Mac
    From tblAssets
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Where tblAssets.Mac Not Like '0A:00:27:%' And tblAssetCustom.State = 1 And
      Len(RTrim(LTrim(tblAssets.Mac))) > 0
    Group By tblAssets.Mac
    Having Count(tblAssets.AssetID) > 1) DuplicateMAC On tblAssets.Mac =
      DuplicateMAC.Mac
Order By tblAssets.Mac

Report contains filter for Virtual box VMs MACs