cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
skz000
Engaged Sweeper

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 ?

 

 

 

 

1 ACCEPTED SOLUTION
ASismey
Engaged Sweeper III

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

View solution in original post

2 REPLIES 2
ASismey
Engaged Sweeper III

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
skz000
Engaged Sweeper

Thank You! this is exactly what I needed.