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
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