‎01-04-2021 06:22 PM
‎06-14-2021 03:47 PM
Brandon wrote:
What reports do you look at on a daily basis? Are they built in or custom?
Daily I have Lansweeper email me the failed login report, software changes, newly discovered software, and Workstations that have less than 10% space.
‎06-09-2021 01:32 PM
‎01-06-2021 03:53 PM
‎01-06-2021 02:12 PM
‎01-06-2021 10:54 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Case
When Convert(nvarchar,DateDiff(day, tblAssets.Lastseen, GetDate())) > 7 Then
'No Connection'
Else 'OK'
End As Status,
tblState.Statename As Assetstate,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Firstseen,
tblAssetCustom.Custom4 As 'System Coordinator',
tblAssetCustom.Custom6 As 'System Administrator',
tblAssetCustom.Comments,
Case
When Convert(nvarchar,DateDiff(day, tblAssets.Lastseen, GetDate())) > 7 Then
'#ffadad'
Else '#d4f4be'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tsysAssetTypes.AssetTypename = 'Windows' And
Convert(nvarchar,DateDiff(day, tblAssets.Lastseen, GetDate())) < 3 And
tblAssetCustom.State != 1
Order By 'System Coordinator',
tblAssets.AssetName
‎01-06-2021 10:52 AM
Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssetCustom.Custom4 As [System Coordinator],
tblAssetCustom.Custom5 As [Network Administrator],
tblAssetCustom.Custom6 As [System Administrator],
tblServiceState.State As [RDP Service Status],
Case
When RDPConnectionState.Value = '0' Then 'Yes'
When RDPConnectionState.Value = '1' Then 'No'
Else 'No data'
End As [RDP Enabled],
Case
When NLAState.Value = '1' Then 'On'
When NLAState.Value = '0' Then 'Off'
Else 'No data'
End As [NLA (On/Off)],
RDPPort.Value As [RDP Port],
Case
When tblAssets.AssetID = HFQuery.AssetID Then ''
Else
'Install August 2019 Security Updates or later to comply with Bluekeep/Dejablue Vulnerability'
End As [Vulnerability Advisory],
tblOperatingsystem.Caption As 'OS',
tblAssets.SP,
Case
When tsysOS.OScode Like '10.0.10240%' Then '1507'
When tsysOS.OScode Like '10.0.10586%' Then '1511'
When tsysOS.OScode Like '10.0.14393%' Then '1607'
When tsysOS.OScode Like '10.0.15063%' Then '1703'
When tsysOS.OScode Like '10.0.16299%' Then '1709'
When tsysOS.OScode Like '10.0.17134%' Then '1803'
When tsysOS.OScode Like '10.0.17763%' Then '1809'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
When tsysOS.OScode Like '10.0.18363%' Then '1909'
When tsysOS.OScode Like '10.0.19041%' Then '2004'
End As Version,
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'UserAuthentication') NLAState On
tblAssets.AssetID = NLAState.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'fDenyTSConnections') RDPConnectionState On
tblAssets.AssetID = RDPConnectionState.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'PortNumber') RDPPort On tblAssets.AssetID =
RDPPort.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4512497', 'KB4512517',
'KB4512507', 'KB4512516', 'KB4512501', 'KB4511553', 'KB4512508',
'KB4512506', 'KB4512486', 'KB4512488', 'KB4512489', 'KB4512518',
'KB4512482', 'KB4516051', 'KB4516026', 'KB4516033', 'KB4516065',
'KB4516062', 'KB4516055', 'KB4516064', 'KB4516067', 'KB4516070',
'KB4516044', 'KB4516068', 'KB4516066', 'KB4516058', 'KB4512578',
'KB4515384', 'KB4520009', 'KB4520002', 'KB4520003', 'KB4519976',
'KB4519985', 'KB4520007', 'KB4519990', 'KB4520005', 'KB4520011',
'KB4093109', 'KB4519998', 'KB4520010', 'KB4520004', 'KB4520008',
'KB4519338', 'KB4517389', 'KB4525239', 'KB4525234', 'KB4525233',
'KB4525235', 'KB4525253', 'KB4525246', 'KB4525250', 'KB4525243',
'KB4525232', 'KB4093109', 'KB4525236', 'KB4520010', 'KB4525241',
'KB4525237', 'KB4523205', 'KB4524570', 'KB4530695', 'KB4530719',
'KB4530734', 'KB4530692', 'KB4530691', 'KB4530698', 'KB4530702',
'KB4530730', 'KB4530681', 'KB4530689', 'KB4530714', 'KB4530717',
'KB4530715', 'KB4530684', 'KB4534303', 'KB4534312', 'KB4534310',
'KB4534314', 'KB4534283', 'KB4534288', 'KB4534297', 'KB4534309',
'KB4534306', 'KB4534271', 'KB4534276', 'KB4534293', 'KB4534273',
'KB4528760', 'KB4537810', 'KB4537822', 'KB4537820', 'KB4537813',
'KB4537814', 'KB4537794', 'KB4537821', 'KB4537803', 'KB4537776',
'KB4537764', 'KB4537789', 'KB4537762', 'KB4532691', 'KB4532693',
'KB4532693', 'KB4541504', 'KB4541506', 'KB4541500', 'KB4540688',
'KB4540694', 'KB4541510', 'KB4541505', 'KB4541509', 'KB4540693',
'KB4540670', 'KB4540681', 'KB4540689', 'KB4538461', 'KB4540673',
'KB4551762', 'KB4550961', 'KB4550970', 'KB4550964', 'KB4550965',
'KB4550951', 'KB4550957', 'KB4550917', 'KB4550971', 'KB4550930',
'KB4550929', 'KB4550927', 'KB4550922', 'KB4549949', 'KB4549951',
'KB4556836', 'KB4556843', 'KB4556860', 'KB4556854', 'KB4556813',
'KB4556840', 'KB4556852', 'KB4556846', 'KB4556853', 'KB4556826',
'KB4556812', 'KB4556807', 'KB4551853', 'KB4556799', 'KB4561645',
'KB4561670', 'KB4561643', 'KB4561669', 'KB4561612', 'KB4561674',
'KB4561666', 'KB4561673', 'KB4561649', 'KB4561616', 'KB4561602',
'KB4561621', 'KB4561608', 'KB4560960', 'KB4557957', 'KB4565536',
'KB4565529', 'KB4565524', 'KB4565539', 'KB4565537', 'KB4565535',
'KB4565541', 'KB4565540', 'KB4565513', 'KB4565511', 'KB4565508',
'KB4565489', 'KB4558998', 'KB4565483', 'KB4565503', 'KB4571730',
'KB4571746', 'KB4571729', 'KB4571719', 'KB4571736', 'KB4571702',
'KB4571703', 'KB4571723', 'KB4571692', 'KB4571694', 'KB4571741',
'KB4571709', 'KB4565349', 'KB4565351', 'KB4566782', 'KB4577064',
'KB4577070', 'KB4577051', 'KB4577053', 'KB4577038', 'KB4577048',
'KB4577071', 'KB4577066', 'KB4577049', 'KB4577015', 'KB4577041',
'KB4577032', 'KB4570333', 'KB4574727', 'KB4571756', 'KB4580385',
'KB4580378', 'KB4580387', 'KB4580345', 'KB4580353', 'KB4580382',
'KB4580358', 'KB4580347', 'KB4580327', 'KB4580346', 'KB4580328',
'KB4580330', 'KB4577668', 'KB4577671', 'KB4577671', 'KB4579311',
'KB4586807', 'KB4586817', 'KB4586827', 'KB4586805', 'KB4586834',
'KB4586808', 'KB4457129', 'KB4586823', 'KB4586787', 'KB4586830',
'KB4586785', 'KB4586793', 'KB4586786', 'KB4586781', 'KB4592498',
'KB4592504', 'KB4592471', 'KB4592503', 'KB4592468', 'KB4592497',
'KB4592484', 'KB4592495', 'KB4592464', 'KB4593226', 'KB4592446',
'KB4592440', 'KB4592449', 'KB4592438')) As HFQuery On
tblAssets.AssetID = HFQuery.AssetID
Where tblServicesUni.Name Like '%TermService%' And
tsysAssetTypes.AssetTypename Like 'Windows%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
‎01-05-2021 03:08 PM
‎01-05-2021 09:25 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now