Searching for Specific and Details Software Asset Reporting

[Male]

Hi,

I searching for a Special Report with following criterias:
Dont know it is possible,

• Device Name
  AD User
  Domain/Workgroups
  First Seen
  Last Time active
  MAC
  IP
  OEM Serial
  Virtual / Physical
  Operating SYS
  Operating SYS Language
  Software 01 = Office Suite Installation (product, version, Edition)
  Software 01.1 = Office Suite Installation 2 (product, version, Edition) -> for parallel Installations of different Office Suites
  Software 02 = Visio Installation (product, version, Edition)
  Software 03 = Project Installatin (product, version, Edition)
  Software 05 = Visual Studio Installation (Highest product, version, Edition)
  Software 07 = Citrix ICA Client (product, version)
  

Can somebody help?

THx!

All The best, Marcel

[Susan_A · Accepted solution]

A sample report can be seen below. Instructions for adding this report to your installation can be found here. Keep in mind that:

• Lansweeper only scans the edition information of SQL Server, not of other software packages. The general software scanning procedure mimics what is listed in Add/Remove Programs on the client machine itself.
  
• The software packages are listed as lines, not columns, as this is how SQL displays table records.

If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Updating to Lansweeper 5.3, if you haven't already. Lansweeper 5.3 includes a database dictionary, which is linked at the top of the report builder. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.Name,
  tblADusers.Displayname,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.IPAddress,
  tblAssets.Mac,
  tblOperatingsystem.Caption As OS,
  tblLanguages.Language As OSLanguage,
  tblOperatingsystem.SerialNumber As ProductID,
  tblSerialnumber.ProductKey,
  Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
  End As VirtualCheck,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblLanguages On tblLanguages.LanguageCode =
    tblOperatingsystem.OSLanguage
  Inner Join tblSerialnumber On tblSerialnumber.AssetID =
    tblOperatingsystem.AssetID And tblSerialnumber.ProductID =
    tblOperatingsystem.SerialNumber
Where (tblSoftwareUni.softwareName Like 'microsoft office%' Or
    tblSoftwareUni.softwareName Like '%visio%' Or tblSoftwareUni.softwareName
    Like '%project%' Or tblSoftwareUni.softwareName Like '%visual studio%' Or
    tblSoftwareUni.softwareName Like '%citrix%') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Software