All computers with selected Software

[jwwclublink]

Hello
I would like to create a report that lists all the computers running a specific version of software but I would like a complete listing in the report. example below. I would then export it to excel to manipulate.

application Computers
-------------------------------
Dropbox -computer123
-computer877

software app2 -computer888
-computer5536

Any assistance would be appreciated
thank you

[TimH]

Daniel,

Thank You very much! This is exactly what I was looking for!

Your help is greatly appreciated!

Regards,

Tim

[Daniel_B]

For this you'll need to join several queries, like the following example:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  SW1.SW1Name,
  SW1.SW1Version,
  SW2.SW2Name,
  SW2.SW2Version
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype

  Inner Join (Select tblSoftware.softwareVersion As SW1Version,
    tblSoftwareUni.softwareName As SW1Name,
    tblSoftware.AssetID
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like 'Software 1%') SW1 On SW1.AssetID =
    tblAssets.AssetID

  Inner Join (Select tblSoftware.softwareVersion As SW2Version,
    tblSoftwareUni.softwareName As SW2Name,
    tblSoftware.AssetID
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like 'Software 2%') SW2 On SW2.AssetID =
    tblAssets.AssetID

Where tblAssetCustom.State = 1

[TimH]

I hope it is ok to revive an old thread because my question is very similar. If not, please pardon my indiscretion 🙂

What if I wanted to run this report but instead of software name 1 or software name 2, I wanted software name 1 and software name 2?

Any assistance is appreciated.

Thanks!

[RCorbeil · Accepted solution]

For a vanilla report, take the default report editor query and add tblSortware, then tblSoftwareUni.

Add the fields tblSoftwareUni.Publisher (if you care about the publisher), tblSoftwareUni.SoftwareName and tblSoftware.SoftwareVersion to your report.

If you want the output sorted on machine, then software, add those fields in the order you want, e.g.

ORDER BY
  tblSoftwareUni.SoftwareName,
  tblAssets.AssetName

From there, export to Excel and manipulate.

If there only certain software titles you're interested in, add conditions to the WHERE clause, e.g.

WHERE
  tblAssetCustom.State = 1
  AND ( tblSoftwareUni.SoftwareName = 'software name 1'
        OR tblSoftwareUni.SoftwareName = 'software name 2'
        OR tblSoftwareUni.SoftwareName LIKE 'softw%'
      )

or if you have a specific list.

WHERE
  tblAssetCustom.State = 1
  AND tblSoftwareUni.SoftwareName IN ('software 1', 'software 2', 'software 3')

The "reports" created in LANSweeper are just queries run against the database, so you're not going to get the banded output in your example. You'll have to use Excel to pretty things up if that's what you need.