04-21-2023 02:01 PM - edited 04-01-2024 12:44 PM
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
04-26-2023 06:10 AM
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
04-26-2023 01:56 PM
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!
04-24-2023 04:31 PM
@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?
04-24-2023 06:16 AM
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
04-24-2023 03:53 PM
Thanks for sharing @Mister_Nobody !
04-24-2023 06:15 AM
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
04-24-2023 06:13 AM
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
04-24-2023 06:11 AM
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
04-24-2023 06:04 AM - edited 04-24-2023 06:04 AM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now