I need a report to find discover a specific NIC and also a specific software

[Tholmes]

I am unsure how to combine them to make the report say, yes this laptop has this specific NIC and also this software installed.

Specific NIC report

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblNetwork.Description As [Network adapter],
tblNetwork.IPAddress As [IP Address],
tblNetwork.MACaddress As [MAC Address],
Case
When tblNetwork.DHCPenabled = 1 Then 'True'
Else 'False'
End As [DHCP enabled],
tblNetwork.DefaultIPGateway,
tblNetwork.DNSHostname,
tblNetwork.IPSubnet
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.Description Like '%OUR SPECIFIC NETWORK ADAPTER%'
And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2
Order By tblAssets.AssetName,
[Network adapter]

And this report shows apparently one software installed and one not installed.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Software not installed%') And
tblSoftwareUni.softwareName Like '%Software installed%' And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

I would like a report that says, yes this specific network card is installed and this software is also installed

[RCorbeil · Accepted solution]

If all you want to report is your NIC details, triggering on the presence of the software but no need to display details, just add a check to the WHERE clause to see if the software match exists.

...
Where
  tblNetwork.Description Like '%OUR SPECIFIC NETWORK ADAPTER%'
  And tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
  And Exists (SELECT tblSoftware.AssetID
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftware.AssetID = tblAssets.AssetID
                AND tblSoftwareUni.softwareName Like '%OUR SOFTWARE TITLE%')
...

If you do want to see software details, you'll need to JOIN that.

...
  tblNetwork.DNSHostname,
  tblNetwork.IPSubnet,
  OurSoftware.SoftwareName,
  OurSoftware.SoftwareVersion
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  INNER JOIN (SELECT
                tblSoftware.AssetID,
                tblSoftwareUni.SoftwarePublisher,
                tblSoftwareUni.SoftwareName,
                tblSoftware.softwareVersion
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftwareUni.SoftwareName LIKE '%OUR SOFTWARE TITLE%') AS OurSoftware ON OurSoftware.AssetID = tblAssets.AssetID
Where
  tblNetwork.Description Like '%OUR SPECIFIC NETWORK ADAPTER%'
  And tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
...

The downside to including the software details is that if you're not precise enough in your filtering, you could end up with multiple rows of output.

[Tholmes]

Thank you so much, this worked perfectly

RC62N wrote:
If all you want to report is your NIC details, triggering on the presence of the software but no need to display details, just add a check to the WHERE clause to see if the software match exists.

...
Where
  tblNetwork.Description Like '%OUR SPECIFIC NETWORK ADAPTER%'
  And tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
  And Exists (SELECT tblSoftware.AssetID
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftware.AssetID = tblAssets.AssetID
                AND tblSoftwareUni.softwareName Like '%OUR SOFTWARE TITLE%')
...

If you do want to see software details, you'll need to JOIN that.

...
  tblNetwork.DNSHostname,
  tblNetwork.IPSubnet,
  OurSoftware.SoftwareName,
  OurSoftware.SoftwareVersion
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  INNER JOIN (SELECT
                tblSoftware.AssetID,
                tblSoftwareUni.SoftwarePublisher,
                tblSoftwareUni.SoftwareName,
                tblSoftware.softwareVersion
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftwareUni.SoftwareName LIKE '%OUR SOFTWARE TITLE%') AS OurSoftware ON OurSoftware.AssetID = tblAssets.AssetID
Where
  tblNetwork.Description Like '%OUR SPECIFIC NETWORK ADAPTER%'
  And tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
...

The downside to including the software details is that if you're not precise enough in your filtering, you could end up with multiple rows of output.