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
06-09-2023 05:25 PM
Report to email morning digest for software changes in the past 3 days. I choose 3 days due to the weekend, but you can adjust to our needs.
Set your export type to Excel so it's easily readable in the email.
Software: Changes in the last 3 days
Select Top (1000000) tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
Case tblSoftwareHist.Action
When '1' Then '+'
When '2' Then '-'
End As [+/-],
tblSoftwareUni.softwareName As Software,
tblSoftwareHist.softwareVersion As Version,
(Select Case tblSoftware.MsStore
When 0 Then 'Desktop app'
Else 'Microsoft Store app'
End) As 'Type',
tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftwareHist.Installdate,
tblSoftwareHist.Lastchanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssets
Inner Join tblSoftwareHist On tblAssets.AssetID = tblSoftwareHist.AssetID
Inner Join tblSoftwareUni On tblSoftwareHist.softid = tblSoftwareUni.SoftID
Inner Join tblSoftware On tblSoftwareHist.softid = tblSoftware.softID And
tblAssets.assetid = tblSoftware.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblSoftwareHist.Lastchanged > GetDate() - 1 And tblAssetCustom.State = 1
Order By Software,
tblAssets.AssetName,
tblSoftwareHist.Lastchanged Desc
06-09-2023 05:19 PM - edited 06-09-2023 05:26 PM
I use this one to notify me when a new asset is detected with Asset Radar. I've got it setup to email me every hour in the Email Alerts.
Report: New assets seen in the past hour
Select Top (1000000) tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.Firstseen > GetDate() - 1 And tblAssets.Firstseen >
DateAdd(HOUR, -1, GetDate()) And tblAssetCustom.State = 1 And
tblAssets.Assettype <> -1 And tblAssets.Assettype <> 66
Order By tblAssets.AssetName
05-19-2023 10:08 AM - edited 05-19-2023 10:08 AM
It seems only me use reports daily...🤔
05-19-2023 11:49 AM
Somehow I doubt that 😊
05-11-2023 07:39 AM
Report for events "service terminated unexpectedly" on Servers:
Select Top 10000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Username,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlog.TimeGenerated,
tblNtlogMessage.Message,
tblAssets.OScode
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 tblNtlogSource.Sourcename = 'service control manager' And
tblNtlog.Eventcode = '7034' And tblAssets.OScode Like '%s' And
tblAssetCustom.State = 1
Order By tblNtlog.TimeGenerated Desc
04-27-2023 08:14 AM - edited 04-27-2023 08:22 AM
Ultrafast report!!!
Asset: Duplicate Asset Names
Select Top 1000000 t.*
From (Select e.AssetID,
e.AssetName,
e.Assettype,
e.Mac,
e.Username,
e.Userdomain,
e.Domain,
e.FQDN,
e.IPAddress,
e.OScode,
e.Firstseen,
e.Lastseen,
e.Lasttried,
Count(1) Over (Partition By Replace(e.AssetName, '.your_domain.loc',
'')) cnt
From tblAssets e) t
Where t.cnt > 1
Order By t.AssetName
Custom version for multiple domains and set of assettypes (windows, hyper-v, vmware, linux)
Select Top 1000000 t.*
From (Select e.AssetID,
e.AssetName,
e.Assettype,
e.Mac,
e.Username,
e.Userdomain,
e.Domain,
e.FQDN,
e.IPAddress,
e.OScode,
e.Firstseen,
e.Lastseen,
e.Lasttried,
Count(1) Over (Partition By Replace(Replace(e.AssetName, '.1domain.loc', ''),
'.2domain.loc', '')) cnt
From tblAssets e
Where e.assettype In ( -1, 11, 92, 70)) t
Where t.cnt > 1
Order By t.AssetName
04-27-2023 07:57 AM
Asset: Windows Duplicate MAC by Network
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.AssetName,
tblNetwork.Description,
tblNetwork.IPAddress,
Duplicates.MACaddress,
Duplicates.Total,
tblNetwork.Lastchanged,
tblAssets.Lastseen
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join (Select Top 1000000 tblNetwork.MACaddress,
Count(Distinct tblNetwork.AssetID) As Total
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPEnabled = 1
Group By tblNetwork.MACaddress
Having tblNetwork.MACaddress Not In ('9C:7D:A3:28:6F:F9',
'02:00:4C:4F:4F:50') And
tblNetwork.MACaddress Not Like '0A:00:27:__:__:__' And
tblNetwork.MACaddress Not Like '00:50:56:C0:__:__' And
Count(Distinct tblNetwork.AssetID) > 1) Duplicates On
tblNetwork.MACaddress = Duplicates.MACaddress
Order By Duplicates.Total Desc,
Duplicates.MACaddress,
tblAssets.AssetName
*Filter for VMware Workstation, Virtual Box, some VPNs
04-27-2023 07:49 AM
Asset: Windows Duplicate MAC by network adapter
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.AssetName,
tblAssets.IPAddress As Asset_IP,
tblAssets.MAC As Asset_MAC,
tblNetworkAdapter.Name As AdapterName,
tblNetworkAdapter.NetEnabled,
Duplicates.MACAddress As Adapter_MAC,
Duplicates.Total,
tblNetworkAdapter.Lastchanged As AdapterLastChanged,
tblNetwork.IPAddress As Network_IP,
tblNetwork.Lastchanged As NetLastchanged,
tblAssets.Lastseen
From tblAssets
Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
Inner Join (Select Top 1000000 tblNetworkAdapter.MACAddress,
Count(Distinct tblNetworkAdapter.AssetID) As Total
From tblAssets
Inner Join tblNetworkAdapter On
tblAssets.AssetID = tblNetworkAdapter.AssetID
Group By tblNetworkAdapter.MACAddress
Having tblNetworkAdapter.MACAddress Not Like '0A:00:27:__:__:__' And
tblNetworkAdapter.MACAddress Not Like '00:50:56:C0:__:__' And
tblNetworkAdapter.MACAddress Not In ('9C:7D:A3:28:6F:F9',
'54:80:E3:16:FF:01', '49:4E:53:00:00:30') And
Count(Distinct tblNetworkAdapter.AssetID) > 1) Duplicates On
tblNetworkAdapter.MACAddress = Duplicates.MACAddress
Left Join tblNetwork On tblNetwork.MACaddress = tblNetworkAdapter.MACAddress
And tblNetworkAdapter.AssetID = tblNetwork.AssetID
Order By Duplicates.Total Desc,
Adapter_MAC,
tblAssets.AssetName
Filter for VMware Workstation, Virtual Box, some VPNs
04-27-2023 05:58 AM
Server: Unexpected shutdown
Select Top 10000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Username,
tblNtlogSource.Sourcename,
tblNtlog.Eventcode,
tblNtlog.TimeGenerated,
tblNtlogMessage.Message,
tblAssets.OScode
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 tblNtlogSource.Sourcename = 'eventlog' And tblNtlog.Eventcode = '6008' And
tblAssets.OScode Like '%s' And tblAssetCustom.State = 1
Order By tblNtlog.TimeGenerated Desc
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now