Include thumbtack Location in report

[clay_williams]

Hello.

Currently we are using IP location but many of our clients are remote and connect via VPN. We also have a high percentage of client laptops which travel between countries. The result is IP location isn't specific enough to know the home office a PC is assigned in.

We'd like to add the thumbtack/asset location name into our client inventory report so we have an accurate location for our travelers.

This is what we have for our report today. Thanks for the help.


Select Top 1000000 tblAssets.AssetUnique,
SubQuery.Username As [Owned By],
SubQuery.EmployeeID As [Employee ID],
SubQuery.StartDate As [Owner Asssign Date],
tblAssets.Username As [Logged in User],
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.Lastseen,
tsysIPLocations.IPLocation As [IP Location],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblADComputers.AssetID,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblOperatingsystem.Caption As OS,
tblOperatingsystem.ServicePackMajorVersion,
tblAssetCustom.Custom1 As [Lease #],
tblAssetCustom.Custom2 As [PO #],
tblAssetCustom.Custom3 As [Lease Start],
tblAssetCustom.Custom4 As [Lease End],
tblAssetCustom.Custom7 As [Deployment State],
tblAssetCustom.Custom8 As [Spare/Stock Location],
tblAssetCustom.Custom9 As Region,
tblAssetCustom.Custom10 As [Remote/DC PC],
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join (Select Top 1000000 tblAssetUserRelations.AssetID,
tblADusers.EmployeeID,
tblAssetUserRelations.Username,
tblAssetUserRelations.StartDate
From tblAssetUserRelations
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetUserRelations.Type
Left Outer Join tblADusers On tblADusers.Username =
tblAssetUserRelations.Username And tblADusers.Userdomain =
tblAssetUserRelations.Userdomain
Where tsysAssetRelationTypes.Name = 'owned by') SubQuery On SubQuery.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2
Order By tblAssets.Lastseen Desc