This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject

→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Full Scan time per asset report

moakyz03
Engaged Sweeper II

‎12-24-2021 04:58 AM

I've created this report and maybe someone else will find it useful - we need a "FullScanTime per asset" report. I created this report using multilevel subquery. Hopefully useful for those who need it.

Select Top 1000000 SubQuery2.AssetID,
  SubQuery2.AssetName,
  SubQuery2.Domain,
  SubQuery2.IPAddress,
  Case
    When SubQuery2.HOURS > 0 And SubQuery2.MINUTES > 0 And SubQuery2.SECONDS >=
      0 And Right(SubQuery2.FullScanTime, 2) >= 0 Then Format(SubQuery2.HOURS,
      '##') + ' hour(s) ' + Format(SubQuery2.MINUTES, '##') + ' minute(s) ' +
      '0' + Right(SubQuery2.FullScanTime, 3) + ' second(s)'
    When SubQuery2.HOURS > 0 Then Format(SubQuery2.HOURS, '##') + ' hour(s) ' +
      Format(SubQuery2.MINUTES, '##') + ' minute(s) ' +
      Format(SubQuery2.SECONDS, '##') + Right(SubQuery2.FullScanTime, 3) +
      ' second(s)'
    When SubQuery2.MINUTES > 0 And SubQuery2.SECONDS = 0 And
      Right(SubQuery2.FullScanTime, 2) > 0 Then Format(SubQuery2.MINUTES,
      '##') + ' minute(s) ' + '0' + Right(SubQuery2.FullScanTime, 3) +
      ' second(s)'
    When SubQuery2.MINUTES > 0 Then Format(SubQuery2.MINUTES, '##') +
      ' minute(s) ' + Format(SubQuery2.SECONDS, '##') +
      Right(SubQuery2.FullScanTime, 3) + ' second(s)'
    When SubQuery2.SECONDS > 0 Then Format(SubQuery2.SECONDS, '##') +
      Right(SubQuery2.FullScanTime, 3) + ' second(s)'
    Else '0' + Right(SubQuery2.FullScanTime, 3) + ' second(s)'
  End As FullScanTime,
  SubQuery2.Date
From tblAssets
  Inner Join (Select Top 1000000 Subquery.AssetID,
      Subquery.AssetName,
      Subquery.Domain,
      Subquery.IPAddress,
      Subquery.FullScanTime,
      Subquery.Date,
      Ceiling(Floor(Convert(integer,Subquery.FullScanTime) / 3600 %
      24)) As HOURS,
      Ceiling(Floor(Convert(integer,Subquery.FullScanTime) % 3600 /
      60)) As MINUTES,
      Ceiling(Floor(Convert(integer,Subquery.FullScanTime) % 60)) As SECONDS
    From tblAssets
      Inner Join (Select Top 1000000 tblAssets.AssetID,
          tblAssets.AssetName,
          tblAssets.Domain,
          tblAssets.IPAddress,
          Sum(TsysLastscan.Scantime) As FullScanTime,
          Convert(Date,TsysLastscan.Lasttime) As Date
        From tblAssets
          Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
          Inner Join TsysWaittime On TsysLastscan.CFGcode = TsysWaittime.CFGCode
        Group By tblAssets.AssetID,
          tblAssets.AssetName,
          tblAssets.Domain,
          tblAssets.IPAddress,
          Convert(Date,TsysLastscan.Lasttime)) As Subquery On
          Subquery.AssetID = tblAssets.AssetID) As SubQuery2 On
      SubQuery2.AssetID = tblAssets.AssetID
Order By SubQuery2.Date Desc,
  SubQuery2.FullScanTime Desc
3 REPLIES 3
moakyz03
Engaged Sweeper II

‎02-22-2022 01:34 AM

simpler reports without multilevel subquery
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tAssettype.[Asset Type],
  tAssettype.[Manufacturer & Model],
  tsysOS.OSname As OS,
  tblAssets.SP,
  Case
    When tscantime.HOURS > 0 And tscantime.MINUTES > 0 And tscantime.SECONDS >=
      0 And Right(tscantime.FullScanTime, 2) >= 0 Then Format(tscantime.HOURS,
      '##') + ' hour(s) ' + Format(tscantime.MINUTES, '##') + ' minute(s) ' +
      '0' + Right(tscantime.FullScanTime, 3) + ' second(s)'
    When tscantime.HOURS > 0 Then Format(tscantime.HOURS, '##') + ' hour(s) ' +
      Format(tscantime.MINUTES, '##') + ' minute(s) ' +
      Format(tscantime.SECONDS, '##') + Right(tscantime.FullScanTime, 3) +
      ' second(s)'
    When tscantime.MINUTES > 0 And tscantime.SECONDS = 0 And
      Right(tscantime.FullScanTime, 2) > 0 Then Format(tscantime.MINUTES,
      '##') + ' minute(s) ' + '0' + Right(tscantime.FullScanTime, 3) +
      ' second(s)'
    When tscantime.MINUTES > 0 Then Format(tscantime.MINUTES, '##') +
      ' minute(s) ' + Format(tscantime.SECONDS, '##') +
      Right(tscantime.FullScanTime, 3) + ' second(s)'
    When tscantime.SECONDS > 0 Then Format(tscantime.SECONDS, '##') +
      Right(tscantime.FullScanTime, 3) + ' second(s)'
    Else '0' + Right(tscantime.FullScanTime, 3) + ' second(s)'
  End As FullScanTime,
  tscantime.Date,
  tblAssets.Scanserver,
  tblAssets.ServiceVersion,
  tblAssets.LsAgentVersion,
  tblSoftware.Installdate
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join (Select tblAssets.AssetID,
      Case
        When tblAssets.Assettype = -1 Then Case
            When tblComputersystem.Domainrole > 1 Then 'Server'
            Else Case
                When (TsysChassisTypes.ChassisName = 'Notebook' Or
                  TsysChassisTypes.ChassisName = 'Laptop' Or
                  TsysChassisTypes.ChassisName = 'Portable') Then 'Laptop'
                Else 'Desktop'
              End
          End
        When tsysAssetTypes.AssetTypename = 'Media system' Then 'NAS'
        When tsysAssetTypes.AssetTypename = 'Battery' Then 'UPS'
        Else tsysAssetTypes.AssetTypename
      End As [Asset Type],
      Case
        When tblAssetCustom.Manufacturer = 'Hewlett-Packard' Or
          tblAssetCustom.Manufacturer = 'microC Design SRL' Then Case
            When tblAssetCustom.Model Like 'HP%' Then ''
            Else 'HP'
          End
        When tblAssetCustom.Manufacturer = 'TP-Link' And
          tblAssetCustom.Model Like 'HP%' Then tblAssetCustom.Model
        When tblAssetCustom.Manufacturer = 'Lenovo' Then Case
            When tblAssetCustom.Model Like 'Lenovo%' Then ''
            Else 'Lenovo'
          End
        When tblAssetCustom.Manufacturer = 'Canon inc.' Then Case
            When tblAssetCustom.Model Like 'Canon%' Then ''
            Else 'Canon'
          End
        When tblAssetCustom.Manufacturer = 'FUJIFILM Business Innovation Corp.'
        Then Case
            When tblAssetCustom.Model Like 'FUJI%' Then ''
            Else 'FUJIFILM'
          End
        When tblAssetCustom.Manufacturer = 'Cisco Systems' Then Case
            When tblAssetCustom.Model Like 'Cisco%' Then ''
            Else 'Cisco'
          End
        When tblAssetCustom.Manufacturer = 'VMware, Inc.' Then Case
            When tblAssetCustom.Model Like 'VMware%' Then ''
            Else 'VMware'
          End
        When tblAssetCustom.Manufacturer = 'Ubiquiti Networks, Inc.' Or
          tblAssetCustom.Manufacturer = 'Ubiquiti Networks Inc.' Then 'Ubiquiti'
        When tblAssetCustom.Manufacturer = 'Dell Inc.' Then 'Dell'
        When tblAssetCustom.Manufacturer = 'Brother Industries, Ltd.' Then ''
        When tblAssetCustom.Manufacturer = 'FUJITSU' Then 'Fujitsu'
        When tblAssetCustom.Manufacturer = 'emachines' Then 'Acer'
        When tblAssetCustom.Manufacturer = 'Gigabyte Technology Co., Ltd.' Then
          'Gigabyte'
        When tblAssetCustom.Manufacturer = 'BIOSTAR Group' Then 'Biostar'
        When tblAssetCustom.Manufacturer = 'ASUSTeK COMPUTER INC.' Then 'Asus'
        When tblAssetCustom.Manufacturer = 'ELITEGROUP Computer Systems' Then
          'ECS'
        When tblAssetCustom.Manufacturer = 'American Power Conversion Corp.'
        Then 'APC'
        Else tblAssetCustom.Manufacturer
      End + '  ' + tblAssetCustom.Model As [Manufacturer & Model],
      tsysAssetTypes.AssetTypeIcon10 As icon
    From tblAssets
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
      Left Join tblComputersystem On tblComputersystem.AssetID =
          tblAssets.AssetID
      Left Join tblSystemEnclosure On
          tblAssets.AssetID = tblSystemEnclosure.AssetID
      Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
          TsysChassisTypes.Chassistype) tAssettype On tAssettype.AssetID =
      tblAssets.AssetID
  Inner Join (Select subquery.AssetID,
      Ceiling(Floor(Convert(integer,subquery.FullScanTime) / 3600 %
      24)) As HOURS,
      Ceiling(Floor(Convert(integer,subquery.FullScanTime) % 3600 /
      60)) As MINUTES,
      Ceiling(Floor(Convert(integer,subquery.FullScanTime) % 60)) As SECONDS,
      subquery.FullScanTime,
      subquery.Date
    From tblAssets
      Inner Join (Select tblAssets.AssetID,
          Sum(TsysLastscan.Scantime) As FullScanTime,
          Convert(Date,TsysLastscan.Lasttime) As Date
        From tblAssets
          Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
        Group By tblAssets.AssetID,
          Convert(Date,TsysLastscan.Lasttime)) subquery On subquery.AssetID =
          tblAssets.AssetID) As tscantime On tscantime.AssetID =
      tblAssets.AssetID
Where tblAssets.LsAgentVersion Is Not Null And tblState.State > 0 And
  tblSoftwareUni.softwareName = 'LsAgent'
Order By tscantime.Date Desc,
  tscantime.FullScanTime Desc
0 Kudos
Hendrik_VE
Champion Sweeper III

‎12-24-2021 12:16 PM

Really great report !
I often get the question how long a Lansweeper scan takes, this gives me a perfect overview. I'll definitely integrate this output in my PowerBI reports 🙂

I've added the following where clause to only include the scan from the last 24h (we scan once a day).

WHERE subquery2.date >= DATEADD(day, -1, GETDATE())

PS. My longest scan time is 1 min 43 seconds 😉
0 Kudos
moakyz03
Engaged Sweeper II
In response to Hendrik_VE

‎12-28-2021 02:12 AM

Slightly modified "FullScanTime per asset" report by adding an asset type field.
Select Top 1000000 SubQuery2.AssetID,
  SubQuery2.AssetName,
  SubQuery2.Domain,
  SubQuery2.IPAddress,
  SubQuery2.[Asset Type],
  SubQuery2.icon,
  SubQuery2.Scanserver,
  SubQuery2.ServiceVersion,
  Case
    When SubQuery2.HOURS > 0 And SubQuery2.MINUTES > 0 And SubQuery2.SECONDS >=
      0 And Right(SubQuery2.FullScanTime, 2) >= 0 Then Format(SubQuery2.HOURS,
      '##') + ' hour(s) ' + Format(SubQuery2.MINUTES, '##') + ' minute(s) ' +
      '0' + Right(SubQuery2.FullScanTime, 3) + ' second(s)'
    When SubQuery2.HOURS > 0 Then Format(SubQuery2.HOURS, '##') + ' hour(s) ' +
      Format(SubQuery2.MINUTES, '##') + ' minute(s) ' +
      Format(SubQuery2.SECONDS, '##') + Right(SubQuery2.FullScanTime, 3) +
      ' second(s)'
    When SubQuery2.MINUTES > 0 And SubQuery2.SECONDS = 0 And
      Right(SubQuery2.FullScanTime, 2) > 0 Then Format(SubQuery2.MINUTES,
      '##') + ' minute(s) ' + '0' + Right(SubQuery2.FullScanTime, 3) +
      ' second(s)'
    When SubQuery2.MINUTES > 0 Then Format(SubQuery2.MINUTES, '##') +
      ' minute(s) ' + Format(SubQuery2.SECONDS, '##') +
      Right(SubQuery2.FullScanTime, 3) + ' second(s)'
    When SubQuery2.SECONDS > 0 Then Format(SubQuery2.SECONDS, '##') +
      Right(SubQuery2.FullScanTime, 3) + ' second(s)'
    Else '0' + Right(SubQuery2.FullScanTime, 3) + ' second(s)'
  End As FullScanTime,
  SubQuery2.Date As ScanDate
From tblAssets
  Inner Join (Select Top 1000000 Subquery.AssetID,
      Subquery.AssetName,
      Subquery.Domain,
      Subquery.IPAddress,
      Subquery.[Asset Type],
      Subquery.icon,
      Subquery.Scanserver,
      Subquery.ServiceVersion,
      Subquery.FullScanTime,
      Subquery.Date,
      Ceiling(Floor(Convert(integer,Subquery.FullScanTime) / 3600 %
      24)) As HOURS,
      Ceiling(Floor(Convert(integer,Subquery.FullScanTime) % 3600 /
      60)) As MINUTES,
      Ceiling(Floor(Convert(integer,Subquery.FullScanTime) % 60)) As SECONDS
    From tblAssets
      Inner Join (Select Top 1000000 tblAssets.AssetID,
          tblAssets.AssetName,
          tblAssets.Domain,
          tblAssets.IPAddress,
          tAssettype.[Asset Type],
          tAssettype.icon,
          tblAssets.Scanserver,
          tblAssets.ServiceVersion,
          Sum(TsysLastscan.Scantime) As FullScanTime,
          Convert(Date,TsysLastscan.Lasttime) As Date
        From tblAssets
          Left Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
          Inner Join tblAssetCustom On
              tblAssets.AssetID = tblAssetCustom.AssetID
          Inner Join tblState On tblAssetCustom.State = tblState.State
          Left Join TsysWaittime On TsysLastscan.CFGcode = TsysWaittime.CFGCode
          Inner Join (Select tblAssets.AssetID,
              Case
                When tblAssets.Assettype = -1 Then Case
                    When tblComputersystem.Domainrole > 1 Then 'Server'
                    Else Case
                        When (TsysChassisTypes.ChassisName = 'Notebook' Or
                          TsysChassisTypes.ChassisName = 'Laptop' Or
                          TsysChassisTypes.ChassisName = 'Portable') Then
                          'Laptop'
                        Else 'Desktop'
                      End
                  End
                When tsysAssetTypes.AssetTypename = 'Media system' Then 'NAS'
                When tsysAssetTypes.AssetTypename = 'Battery' Then 'UPS'
                Else tsysAssetTypes.AssetTypename
              End As [Asset Type],
              tsysAssetTypes.AssetTypeIcon10 As icon
            From tblAssets
              Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
                  tblAssets.Assettype
              Left Join tblComputersystem On tblComputersystem.AssetID =
                  tblAssets.AssetID
              Left Join tblSystemEnclosure On tblAssets.AssetID =
                  tblSystemEnclosure.AssetID
              Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
                  TsysChassisTypes.Chassistype) tAssettype On
              tAssettype.AssetID = tblAssets.AssetID
        Group By tblAssets.AssetID,
          tblAssets.AssetName,
          tblAssets.Domain,
          tblAssets.IPAddress,
          tAssettype.icon,
          tAssettype.[Asset Type],
          tblAssets.Scanserver,
          tblAssets.ServiceVersion,
          Convert(Date,TsysLastscan.Lasttime)) As Subquery On
          Subquery.AssetID = tblAssets.AssetID) As SubQuery2 On
      SubQuery2.AssetID = tblAssets.AssetID
Where SubQuery2.[Asset Type] <> 'location'
Order By ScanDate Desc,
  SubQuery2.FullScanTime Desc


0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now