01-13-2023 08:46 AM
Hello,
I've been using this report to get all mac addresses of device for all existing adapters
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tsysOS.OSname,
tblassets.IPAddress,
tblNetwork.Description,
tblNetwork.MACaddress,
tblassets.Lastseen,
tblADComputers.OU
From tblassets
Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
Inner Join tblNetwork On tblassets.AssetID = tblNetwork.AssetID
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
Where tblNetwork.Description Like '%' And tblassetcustom.State = 1
But I would love to see if specific software is installed in the same report as well.
Is there a way to integrate that it would show if e.g. "Firefox" is installed in the same report for that device. If it's not installed the column should just be blank as I still need adapters MAC addresses (computers can't be removed from this list - main focus are adapters MAC addresses)
Is it possible to achieve this ?
Solved! Go to Solution.
01-13-2023 05:01 PM
This should get you started :
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tsysOS.OSname,
tblassets.IPAddress,
tblNetwork.Description,
tblNetwork.MACaddress,
tblassets.Lastseen,
tblADComputers.OU,
Case
When FireFox.AssetID Is Not Null Then 'Yes'
Else 'No'
End As [firefor Installed]
From tblassets
Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
Inner Join tblNetwork On tblassets.AssetID = tblNetwork.AssetID
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Mozilla Firefox%') FireFox On
FireFox.AssetID = tblassets.AssetID
Where tblNetwork.Description Like '%' And tblassetcustom.State = 1
01-13-2023 05:01 PM
This should get you started :
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tsysOS.OSname,
tblassets.IPAddress,
tblNetwork.Description,
tblNetwork.MACaddress,
tblassets.Lastseen,
tblADComputers.OU,
Case
When FireFox.AssetID Is Not Null Then 'Yes'
Else 'No'
End As [firefor Installed]
From tblassets
Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
Inner Join tblNetwork On tblassets.AssetID = tblNetwork.AssetID
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Mozilla Firefox%') FireFox On
FireFox.AssetID = tblassets.AssetID
Where tblNetwork.Description Like '%' And tblassetcustom.State = 1
01-14-2023 11:57 AM
Thank You! this is exactly what I needed.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now