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

[rader]

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

[rader]

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

[Mister_Nobody]

It seems only me use reports daily...🤔

[Mercedes_O]

Somehow I doubt that 😊 

[Mister_Nobody]

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

[Mister_Nobody]

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

[Mister_Nobody]

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 

[Mister_Nobody]

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

[Mister_Nobody]

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