Try this out.
Just to be clear, you're wanting to see only FlexNetInventroyAgent 2, right? Your code suggests you don't want to see FlexNetInventroyAgent 1. If you want to see both, you can adjust the WHERE clause in the software SELECT.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Software.softwareName As Software,
Software.softwareVersion As Version,
Software.SoftwarePublisher As Publisher,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From
tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
LEFT JOIN (SELECT
tblSoftware.AssetID,
tblSoftware.SoftwareVersion,
tblSoftwareUni.SoftwareName,
tblSoftwareUni.SoftwarePublisher
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
tblSoftwareUni.SoftwareName LIKE '%FlexNetInventroyAgent 2%') AS Software ON Software.AssetID = tblAssets.AssetID
Where
tblAssetCustom.State = 1
--AND tblState.Statename = 'Active' -- redundant
And tblDomainroles.Domainrolename In ('Stand-alone server', 'Member server', 'Primary domain controller', 'Backup domain controller')
And tblComputersystem.SystemType Like 'x64%'
Order By
tblAssets.Domain,
tblAssets.AssetName
The software sub-select limits the list of software to only what you want to see. Using a left join ensures that even servers without the software will be included in the results.