Notification

Icon
Error

Assets and Owner listing help - SQL list Window servers only, need all Window devices listed

Posted: Tuesday, December 10, 2019 6:48:16 PM(UTC)
ssmarr5

ssmarr5

Member Original PosterPosts: 4
0
Like
How do I get this report to list ALL window asset and not just servers? Great report, just limited to server only. Thanks.Brick wall

Select Top 1000000 tblADusers.Company,
tblAssets.AssetName,
tblState.Statename,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tsysAssetTypes.AssetTypename,
tblAssets.SP,
tblAssets.Domain,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblADComputers.AssetID,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblOperatingsystem.InstallDate,
tblOperatingsystem.PlusProductID,
SubQuery.Name As Relation,
SubQuery.Username As RelatedUser,
SubQuery.Userdomain As RelatedUserdomain,
SubQuery.Comments As RelationComments,
SubQuery.StartDate As RelationStartdate
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,
tsysAssetRelationTypes.Name,
tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain,
tblAssetUserRelations.Comments,
tblAssetUserRelations.StartDate
From tblAssetUserRelations
Inner Join tsysAssetRelationTypes On
tsysAssetRelationTypes.RelationTypeID = tblAssetUserRelations.Type
Where tsysAssetRelationTypes.Name = 'owned by') SubQuery On
SubQuery.AssetID = tblAssets.AssetID
Order By tblADusers.Company,
tblState.Statename,
tsysIPLocations.IPLocation
RC62N
#1RC62N Member Posts: 411  
posted: 12/10/2019 7:55:03 PM(UTC)
Since you don't have a WHERE clause filtering anything out, more than likely one of the inner joins is filtering out your non-servers.

Do you have an IP range defined for your non-servers? (Configuration > Asset Groups > IP Address Range locations) That's the first obvious possibility that leaps out at me. The rest of the tables you're linking to should all be suitably populated, so shouldn't cause non-servers to be filtered out. If you want to test to be sure, change the INNER JOIN tsysIPLocations to a LEFT JOIN. If that's the ticket, you should see your non-servers show up with no value in the IPLocation column.
ssmarr5
#2ssmarr5 Member Original PosterPosts: 4  
posted: 12/10/2019 9:23:09 PM(UTC)
Thanks, that was the ticket, I 'Left Join' all the 'Inner Joins' and got all the information I needed. Much appreciated.

Active Discussions

Lansweeper Lansweeper Webserver Error
by  Bruce.B   Go to last post Go to first unread
Last post: Today at 3:19:01 AM(UTC)
Lansweeper LsAgent install issue
by  Susan.A  
Go to last post Go to first unread
Last post: Yesterday at 8:46:19 PM(UTC)
Lansweeper Is a Common Table Expression possible?
by  PatrickWolf   Go to last post Go to first unread
Last post: Yesterday at 6:52:24 PM(UTC)
Lansweeper Tracking assets like docking station, mouse and keyboards
by  cycleheat  
Go to last post Go to first unread
Last post: Yesterday at 5:00:39 PM(UTC)
Lansweeper Update to V. 7.2.107.4
by  cycleheat   Go to last post Go to first unread
Last post: Yesterday at 4:57:38 PM(UTC)
Lansweeper Windows Defender AV
by  Rob B  
Go to last post Go to first unread
Last post: Yesterday at 4:55:19 PM(UTC)
Lansweeper Scan Server Scanning wrong IP range
by  wayneRex   Go to last post Go to first unread
Last post: Yesterday at 3:49:36 PM(UTC)
Lansweeper Lansweeper support levels
by  RobertB  
Go to last post Go to first unread
Last post: 1/23/2020 10:39:05 PM(UTC)