Custom Reports with Software Search Variable.

[RMcDowell]

Hello and forgive me for what I am sure is a very simple task, but as I am new to LanSweeper and short on time I am posting for help.

I need a report that will list the basic stats of my systems as well as certain installed software packages, currently I am looking for Ms Office Enterprise 2007 but I would like the report to be flexible.
So..
A system scan showing Name, IP, OS, Mem, Proc, Last Seen, Last tried, last user logged in and then a variable that will allow me to search for a particular bit of software.

Thank you in advanced for your time.

[Daniel_B · Accepted solution]

Data about installed software is stored in tables tblSoftware and tblSoftwareUni. If you would like to list all your Windows computers and include information about only specific software, you need to use a subquery. Please find an example report below which lists all MS Office and MS Project installations. You can easily modify it by changing the criteria under the WHERE clause of the subquery.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblOperatingsystem.Caption As OS,
  tSoftware.softwareName,
  tSoftware.softwareVersion,
  tblAssets.Memory,
  tblAssets.Processor,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
    tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName,
    tblSoftware.softwareVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where (tblSoftwareUni.softwareName Like 'Microsoft Office %' And
    tblSoftwareUni.softwareName Not Like '%viewer%' And
    tblSoftwareUni.softwareName Not Like '%designer%' And
    SubString(tblSoftwareUni.softwareName, Len(tblSoftwareUni.softwareName) - 4,
    3) = ' 20') Or
    (tblSoftwareUni.softwareName Like 'Microsoft Office Project 20%')) tSoftware
    On tSoftware.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  tSoftware.softwareName,
  tSoftware.softwareVersion