Request: Report assets and software grouped by software

[maria_aiva]

Hello all.
I would like to create a report for software installed like this:

Acrobat reader | PCEmily, Administrator, SupportPC, PCMark, PCCarl, PCJustine
Microsoft Office 2000 | PCEmily, PCMark, Administrator, SupportPC
Microsoft Office 2007 | PCCarl, PCJustine
Mozilla Firefox 35 | PCCarl, PCJustine, PCEmily
Mozilla Firefox 48 | PCMark, Administrator
MOzilla Firefox 48.1 | SupportPC
...
and so on.
On other words:
a certain software (and version) in what PC's is installed?

Any hint?

Thanks in advantage
M.A.

[maria_aiva]

Hello.
Yeah, Lansweeper was running on SQL Compact.
Upgraded to SQL express (advanced installation, as per Nick.VDB and mine hint)
Everything goes fine!

Report running time: one second
Scenario:
26 overall assets; 13 PCs; 463 "different" softwares
one LAN 192.168.1.1 => 192.168.1.254; 2 switch
scanserver: Veriton L4630G, WIN7pro, I3, 8GBram

Thanks a lot to all!
M.A.

[Nick_VDB]

You will indeed get an error when running MikeMc's report on SQL Compact. You can check which SQL you have installed by going to Configuration\You Lansweeper License.

As you mentioned you can move your SQL Compact Database to SQL Server by following the step in that article.

[maria_aiva]

Hello.
Thanks for your answer.
Unfortunaltely, when I copy/paste the code onto a new report, I receive
"There was an error parsing the query. [ Token line number = 1,Token line offset = 151,Token in error = Select ]"

I suppose because Lansweeper is running on SQL compact...

Looking to migrate database to full SQL
(http://www.lansweeper.com/kb/73/moving-your-database-from-sql-compact-to-sql-server.html)


Thanks for efforts

M.A.

[MikeMc · Accepted solution]

This report will list all software inventoried by Lansweeper installed on Active computers along with a comma delimited list of them.

• This query will only work if you are running your database off full SQL server and not SQL server CE.
  
• This query can take a while to run depending on the installed software and asset counts.

Select Top 1000000 *
From (Select Top 1000000 tblSoftwareUni.softwareName As Software,
    tblSoftware.softwareVersion As Version,
    Reverse(Stuff(Reverse((Select a.AssetName + ', '
    From tblSoftware s Inner Join tblSoftwareUni su On su.SoftID = s.softID
      Inner Join tblAssets a On a.AssetID = s.AssetID Inner Join
      tblAssetCustom ac On ac.AssetID = a.AssetID
    Where ac.State = 1 And su.softwareName = tblSoftwareUni.softwareName And
      s.softwareVersion = tblSoftware.softwareVersion Order By a.AssetName
    For Xml Path(''))), 1, 2, '')) As Computers
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Group By tblSoftwareUni.softwareName,
    tblSoftware.softwareVersion) T1
Where T1.Computers Is Not Null
Order By T1.Software,
  T1.Version