
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-01-2015 08:56 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-04-2015 04:51 PM
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.
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".
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".
1 REPLY 1

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-04-2015 04:51 PM
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.
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".
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".
