→ 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: 
RMcDowell
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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