→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tholmes
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

2 REPLIES 2
RCorbeil
Honored Sweeper II
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
Engaged Sweeper III

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.