Report assistance

[matthew1g]

Trying to write a report with the following info:

Asset OU
Switch Name (snmp asset name)
Asset IP Location
Asset AD description
Asset Name
Asset Last Logged in user
Asset OS version
Asset Make / Model
Asset IP address
Asset Chassis type (EG laptop, desktop)



Here is what I have so far:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tsysOS.OSname As [Operating System],
tblAssetCustom.Model,
tblAssets.Lastseen,
tsysOS.Image As icon,
tsysIPLocations.IPLocation,
tblAssets.IPAddress,
tblAssets_1.AssetName As Switch,
TsysChassisTypes.ChassisName,
tblADComputers.OU
From tblAssets
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Full Join tblAssetMacAddress On tblAssets.AssetID = tblAssetMacAddress.AssetID
Full Outer Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Full Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.Lastseen > GetDate() - 40 And tblAssetCustom.State = 1



I am having trouble with the switch snmp name. If I change the joins to full or left joins I get 4 copies of each asset. However, if I change the joins to inner joins the report excludes any assets that do no have a switch snmp connection listed. I want it to show each asset and just have a blank field if there is no switch snmp name found.

Any help would be appreciated, thanks!

[matthew1g · Accepted solution]

I think I actually figured this one out. Updated report:


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tsysOS.OSname As [Operating System],
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssets.Lastseen As [Last Seen],
tsysOS.Image As icon,
tblADusers.Name As [Last Logged On User],
tsysIPLocations.IPLocation,
tblAssets.IPAddress,
tblAssets_1.AssetName As Switch,
tblADComputers.OU
From tblAssets
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblSNMPAssetMac On tblAssets.Mac = tblSNMPAssetMac.AssetMacAddress
Left Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Left Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Where tblAssets.Lastseen > GetDate() - 45 And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique