cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jwwclublink
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

4 REPLIES 4
TimH
Engaged Sweeper III
Daniel,

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

Your help is greatly appreciated!

Regards,

Tim
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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
Honored Sweeper II
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.