Laptops with and without a software title

[Mr__Anderson]

Trying to audit a specific software title that only is installed on laptops. Trying to make a report that shows laptops that have it installed but also laptops that have it missing.

Thanks!
Chris

[Mr__Anderson]

That's excellent!


2 questions -

The software that I'm trying to get a report on has 2 names, the newer version has a different name. How would I change to see both?

Also is it possible to show Software name and version in the report?

Thanks so much!
Chris

[brandon_jones]

Chris Anderson wrote:
That's excellent!


2 questions -

The software that I'm trying to get a report on has 2 names, the newer version has a different name. How would I change to see both?

Also is it possible to show Software name and version in the report?

Thanks so much!
Chris

I was able to get the 2nd software name, but I am having issues figuring out how to get the software name in. I will keep looking at it.

Select Distinct Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblSystemEnclosure.ChassisTypes,
TsysChassisTypes.ChassisName,
Case
When tblassets.AssetID In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where (tblSoftwareUni.softwareName Like 'Software Name1%') Or
(tblSoftwareUni.softwareName Like 'Software Name2%')) Then '#d4f4be'
When tblassets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Name of software%') Then
'#f7cfca'
End As backgroundcolor
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tblSystemEnclosure On tblassets.AssetID =
tblSystemEnclosure.AssetID
Inner Join lansweeperdb.dbo.TsysChassisTypes On
tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Inner Join lansweeperdb.dbo.tblSoftware On tblassets.AssetID =
tblSoftware.AssetID
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where (TsysChassisTypes.ChassisName = 'laptop' And tblassetcustom.State = 1) Or
(TsysChassisTypes.ChassisName = 'portable') Or
(TsysChassisTypes.ChassisName = 'notebook')

[brandon_jones]

Try this and see if it works. You will need to edit the name of the software. Search for the text name of software and replace it with the name of the software you are looking for. The laptops that have the software will be highlighted in green and the ones that do not will be highlighted in red.

Select Distinct Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblSystemEnclosure.ChassisTypes,
TsysChassisTypes.ChassisName,
Case
When tblassets.AssetID In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Name of software%') Then
'#d4f4be'
When tblassets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Name of software%') Then
'#f7cfca'
End As backgroundcolor
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tblSystemEnclosure On tblassets.AssetID =
tblSystemEnclosure.AssetID
Inner Join lansweeperdb.dbo.TsysChassisTypes On
tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Inner Join lansweeperdb.dbo.tblSoftware On tblassets.AssetID =
tblSoftware.AssetID
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where (TsysChassisTypes.ChassisName = 'laptop' And tblassetcustom.State = 1) Or
(TsysChassisTypes.ChassisName = 'portable') Or
(TsysChassisTypes.ChassisName = 'notebook')