cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
scoomans
Engaged Sweeper
I have found the following report which gives me the PC names, but I need it with the location map names. So we have created a location map here with all the pc's on it and I would like to see this name from the location map in my report.

Select tblAssets.AssetID,
tblAssets.AssetName,
T1.Domain,
T1.Username,
T1.LastLogonTime
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join (Select tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogonTime
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) T1 On T1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
T1.LastLogonTime Desc



The following code gives the map location instead of the PC name, but I also need the distinct users so it should be a mix but I can't get it right.


Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName As Location,
tblAssets1.AssetID,
tblAssets1.AssetName,
tblAssets1.Domain,
tsysAssetTypes1.AssetTypename As AssetType,
tblAssets1.Username,
tblAssets1.Userdomain,
tblAssets1.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
As OS,
tblAssets.SP,
tblAssets1.Lastseen,
tblAssets1.Lasttried
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetRelations
On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetRelations.ChildAssetID
Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
tblAssets1.Assettype
Inner Join tblAssetCustom On tblAssets1.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets1.LocationID
Left Join tblLinuxSystem On tblAssets1.AssetID = tblLinuxSystem.AssetID
Left Join tblMacOSInfo On tblAssets1.AssetID = tblMacOSInfo.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets1.OScode
Where tsysAssetRelationTypes.ReverseName = 'has location of' And
tblState.Statename = 'Active'
Order By Location,
tblAssets1.IPNumeric,
tblAssets1.Domain,
tblAssets1.AssetName



I hope that someone here can help me because I am really stuck.

Thanks,

Steven
0 REPLIES 0

New to Lansweeper?

Try Lansweeper For Free

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

Try Now