→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SouthySuper
Engaged Sweeper III
We use OpenDNS Umbrella client (both x86 and x64 versions)

This report seems to be partly accurate for telling me which laptop/tablets do not have the software present, but upon auditing a handful I've found that several do have the file(s) present

I have file scanning enabled for both:
%programfiles%\OpenDNS\Umbrella Roaming Client\ERCService.exe
%programfiles(x86)%\OpenDNS\Umbrella Roaming Client\ERCService.exe


Select Top 10000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged As [Files last changed],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Where tblFileVersions.FilePathfull Not Like '%ercservice.exe%' And
tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

Attached is my output:

Any Suggestions?
1 ACCEPTED SOLUTION
Tom_P
Lansweeper Employee
Lansweeper Employee
Hi,

The following report (based on the SQL you supplied) will give back all active laptops that don't have the mentioned value in the registry.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.Assettype,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblAssetCustom.Location,
tblAssets.Description,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
TsysChassisTypes.ChassisName,
TsysChassisTypes.Chassistype,
tblAssets.Lastseen,
tblAssets.Lasttried
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
Inner Join tblSystemEnclosure on tblSystemEnclosure.AssetID = tblAssets.AssetID
Inner Join TsysChassisTypes on TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes
Inner Join (Select Top 1000000 tblRegistry.AssetID, tblRegistry.Regkey, tblRegistry.Valuename, tblRegistry.Value, tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%Umbrella Roaming Client' And tblRegistry.Valuename = 'DefaultRecoveryFolderPath') as registry on registry.AssetID = tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active'
and (TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10 Or TsysChassisTypes.Chassistype = 11)
Order By tblAssets.AssetName, tblAssets.Domain

View solution in original post

3 REPLIES 3
SouthySuper
Engaged Sweeper III
Well that's what I needed. Thank you.
Tom_P
Lansweeper Employee
Lansweeper Employee
Hi,

The following report (based on the SQL you supplied) will give back all active laptops that don't have the mentioned value in the registry.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.Assettype,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblAssetCustom.Location,
tblAssets.Description,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
TsysChassisTypes.ChassisName,
TsysChassisTypes.Chassistype,
tblAssets.Lastseen,
tblAssets.Lasttried
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
Inner Join tblSystemEnclosure on tblSystemEnclosure.AssetID = tblAssets.AssetID
Inner Join TsysChassisTypes on TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes
Inner Join (Select Top 1000000 tblRegistry.AssetID, tblRegistry.Regkey, tblRegistry.Valuename, tblRegistry.Value, tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%Umbrella Roaming Client' And tblRegistry.Valuename = 'DefaultRecoveryFolderPath') as registry on registry.AssetID = tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active'
and (TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10 Or TsysChassisTypes.Chassistype = 11)
Order By tblAssets.AssetName, tblAssets.Domain
SouthySuper
Engaged Sweeper III
I found a better way (not absolute), but less of a completely bad report I guess.

Using the registry entry instead.


Here is my report:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.SP As SP,
tblAssets.Lastseen,
SubQuery1.Regkey,
tblAssetCustom.State,
SubQuery1.Valuename,
TsysChassisTypes.Chassistype,
tblAssets.Assettype,
tblAssets.AssetUnique
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%Umbrella Roaming Client' And
tblRegistry.Valuename = 'DefaultRecoveryFolderPath') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssets.Lastseen <> '' And tblAssetCustom.State = 1 And
(TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or
TsysChassisTypes.Chassistype = 10 Or TsysChassisTypes.Chassistype = 11) And
tblAssets.Assettype = -1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.AssetUnique