Report for all assets

[jipearce]

I'm trying to create a report that includes asset name, OS, IP, network mask, default gateway, DNS server(s), contact and location. I've gotten a report working with the code below, but it only includes Windows assets. I think it might be an inner join vs. left join issue based on what I've read on the site, but I've had no luck figuring out specifically what I need to change. Any help appreciated.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblNetwork.IPAddress,
tblNetwork.IPSubnet,
tblNetwork.DefaultIPGateway,
tblNetwork.DNSServerSearchOrder As [DNS server],
tblAssetCustom.Contact,
tblAssetCustom.Location
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Order By tblAssets.AssetName

Thanks,
Jim

[Daniel_B · Accepted solution]

Table tblNetwork only contains data for Windows assets. Some of the other asset types have their own tables with data about network adapters. A report which lists all of them would be quite a large query. Please find an example in the following spoiler.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tNetwork.Description As NIC,
  tNetwork.IPAddress As IP
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join (Select tblNetwork.AssetID,
    tblNetwork.IPAddress,
    tblNetwork.Description
  From tblNetwork
  Where tblNetwork.IPEnabled = 1) tNetwork On tblAssets.AssetID =
    tNetwork.AssetID
Where tblAssetCustom.State = 1
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblMacNetwork.Name As NIC,
  tblMacNetwork.Ipv4 As IP
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblMacNetwork On tblAssets.AssetID = tblMacNetwork.AssetID
Where tblAssetCustom.State = 1
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblLinuxNetworkDetection.Name As NIC,
  tblLinuxNetworkDetection.Ipv4 As IP
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblLinuxNetworkDetection On tblAssets.AssetID =
    tblLinuxNetworkDetection.AssetID
Where tblAssetCustom.State = 1
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  Cast(tblSNMPInfo.IfIndex As nvarchar) As NIC,
  tblSNMPInfo.IfIPAddress As IP
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Where tblAssetCustom.State = 1
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  Null As NIC,
  tblAssets.IPAddress As IP
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename Not In ('Windows', 'Apple Mac', 'Linux',
  'Unix', 'VMware server') And tblAssetCustom.State = 1 And
  Coalesce(tblAssets.IPAddress, '') <> ''
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  Cast(tblVmwareNetwork.Name As nvarchar) As NIC,
  tblVmwareNetwork.IPAddress As IP
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblVmwareNetwork On tblVmwareNetwork.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By AssetName,
  NIC

Your report would list all assets if you change the join between tables tblAssets and tblNetwork. Right click on it and hit "Select all rows from tblAssets".