Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-15-2021 07:47 PM
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
And this report shows apparently one software installed and one not installed.
I would like a report that says, yes this specific network card is installed and this software is also 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
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
‎12-15-2021 10:51 PM
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.
If you do want to see software details, you'll need to JOIN that.
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.
...
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.
2 REPLIES 2
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-15-2021 10:51 PM
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.
If you do want to see software details, you'll need to JOIN that.
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.
...
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.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-15-2021 11:04 PM
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.