→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

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

View solution in original post

4 REPLIES 4
maria_aiva
Engaged Sweeper
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
Champion Sweeper III
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
Engaged Sweeper
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
Champion Sweeper II
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