Reports Total - History

[EtiBel]

Greetings Sweepers,

I am using  a Lansweeper Custom report for my Conference rooms, i monitor all sorts of data everyday.

i have 164 Conference rooms total and it works well for the information i am looking for.

There is a ''but'',  some mornings, my reports drops to 163 sometimes 162. which is odd enough.

for me to find the missing machine, i have to export the day before the list and compare it to list today. it is quite time consuming. 

is it possible to have an history on total returned devices on a report ? 
is there a easier way for me to find out which devices suddenly is popping in my report?

Thank you

[EtiBel]

BUMP

[Hendrik_VE]

It would help if you could post the report query you use 🙂
Are you talking about Windows machines, or devices speaking SNMP?

[EtiBel]

Hi Hendrik_VE

here is my custom report:

Select Distinct Top 1000000 tblassets.AssetName,
tblassetcustom.Serialnumber,
tsysIPLocations.IPLocation,
tblassets.Description,
Case
When Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblassets.Uptime) /
3600 / 24))) >= 1 Then '#ffce33'
When Left(tblassets.Description, 6) = '.Salle' And
Left(tblSoftware.softwareVersion, 3) = '1.5' And
tblOperatingsystem.Version = '10.0.19044' And tblassetcustom.State = '1'
Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor,
Case
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Sereno Camera, NMX-VCC-1000') Then '#000000'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Polycom Studio Video') Then '#000000'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
(tblUSBDevices.Name = 'Logitech HD Webcam C270' Or
tblUSBDevices.Name = 'Logitech HD Webcam C310')) Then '#000000'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
(tblUSBDevices.Name = 'USB Video Device' Or tblUSBDevices.Name =
'USB Camera' Or tblUSBDevices.Name = 'Logitech USB Input Device'))
Then '#000000'
Else '#1919BD'
End As foregroundcolor,
Case tblOperatingsystem.Version
When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511'
When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703'
When '10.0.16299' Then '1709'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '2004'
When '10.0.19042' Then '20H2'
When '10.0.19043' Then '21H1'
When '10.0.19044' Then '21H2'
When '10.0.19045' Then '22H2'
Else ''
End As WinBuild,
tblSoftware.softwareVersion As TeamsVer,
tblassetcustom.Model,
tblassetcustom.Manufacturer,
tblassets.LastIPScan,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblassets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblassets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblassets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot,
tblassets.AssetID,
Case
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Sereno Camera, NMX-VCC-1000') Then 'Sereno Camera, NMX-VCC-1000'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Polycom Studio Video') Then 'Polycom Studio Video'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
(tblUSBDevices.Name = 'Logitech HD Webcam C270' Or
tblUSBDevices.Name = 'Logitech HD Webcam C310')) Then
'Logitech HD Webcam'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
(tblUSBDevices.Name = 'USB Video Device' Or tblUSBDevices.Name =
'USB Camera' Or tblUSBDevices.Name = 'Logitech USB Input Device'))
Then 'USB Video Device'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Poly Studio P15 Audio') Then 'Poly Studio P15 Audio'
Else ''
End As CameraDetected,
Case
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Plantronics Calisto 7200') Then 'Plantronics Calisto 7200'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Polycom Studio Audio') Then 'Polycom Studio Audio'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
(tblUSBDevices.Name = 'Microphone (HD Webcam C270)' Or
tblUSBDevices.Name = 'Microphone (HD Webcam C310)')) Then
'Microphone (HD Webcam C270)'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
(tblUSBDevices.Name = 'DELL PROFESSIONAL SOUND BAR AE515' Or
tblUSBDevices.Name =
'Interphone avec annulation d''écho (2- DELL PROFESSIONAL SOUND BAR AE515)')) Then 'DELL PROFESSIONAL SOUND BAR AE515'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Logitech USB Input Device') Then 'Logitech USB Input Device'
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And tblUSBDevices.Name =
'Poly Studio P15 Video') Then 'Poly Studio P15 Video'
Else ''
End As MicrophoneDetected,
CountMonitor.MonitorModelConcat,
CountMonitor.NumberMonitors,
Case
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
(tblUSBDevices.Name = 'HID Keyboard Device' Or tblUSBDevices.Name =
'Microsoft Hardware USB Keyboard' Or tblUSBDevices.Name =
'USB Input Device' Or tblUSBDevices.Name = 'Designer Keyboard'))
Then 'USB Keyboard'
Else ''
End As USBKeyboard,
Case
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
tblUSBDevices.Manufacturer = 'Microsoft') Then 'Microsoft'
Else ''
End As KeyBoardManufacturer,
Case
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
(tblUSBDevices.Name = 'HID-compliant mouse' Or tblUSBDevices.Name =
'Lenovo Optical Mouse (HID)' Or tblUSBDevices.Name =
'Microsoft Hardware USB mouse' Or tblUSBDevices.Name =
'Microsoft USB Wheel Mouse Optical' Or
tblUSBDevices.Name Like 'Microsoft USB Basic Optical Mouse%')) Then
'USB Mouse'
Else ''
End As USBMouse,
Case
When Exists(Select Top 1 tblUSBDevices.AssetID From tblUSBDevices
Where tblUSBDevices.AssetID = tblassets.AssetID And
tblUSBDevices.Manufacturer = 'Microsoft') Then 'Microsoft'
Else ''
End As MouseManufacturer,
tblassets.IPAddress,
tblState.Statename
From tblassets
Left Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Left Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblassets.LocationID
Left Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tblState On tblState.State = tblassetcustom.State
Left Join (Select tblMonitor.AssetID,
STRING_AGG(tblMonitor.MonitorModel, ', ') As MonitorModelConcat,
COUNT(*) As NumberMonitors
From tblMonitor
Group By tblMonitor.AssetID) As CountMonitor On CountMonitor.AssetID =
tblassets.AssetID
Inner Join tblOperatingsystem On
tblassets.AssetID = tblOperatingsystem.AssetID
Where tblassets.AssetName Like '%-SC-%' And tblSoftwareUni.softwareName Like
'Teams Machine-Wide Installer'
Group By tblassets.AssetName,
tblassetcustom.Serialnumber,
tsysIPLocations.IPLocation,
tblassets.Description,
tblSoftware.softwareVersion,
tblassetcustom.Model,
tblassetcustom.Manufacturer,
tblassets.LastIPScan,
tblassets.AssetID,
CountMonitor.MonitorModelConcat,
CountMonitor.NumberMonitors,
tblassets.IPAddress,
tblState.Statename,
tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblassets.Uptime,
tblassets.Lastseen,
tblOperatingsystem.Version,
tblassetcustom.State
Order By tsysIPLocations.IPLocation,
UptimeSinceLastReboot Desc