Report - SQL Formatting

[keys_it]

Hi,

We have purchased Lansweeper a couple of months back and are extremely happy with it. We are in the process of creating a standard template for our Licensed software and I am stuck on the SQL formatting part of this report. What I have so far is working great and I am about 90% there.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysIPLocations.IPLocation,
  tsysOS.OSname,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
    tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Acrobat%') And tblAssetCustom.State =
  1
Order By tsysIPLocations.IPLocation,
  tblAssets.AssetName,
  tblAssets.Lastseen Desc

All I want to add to this report is to have the softwareName and softwareVersion displayed on the report as well as all the above information. Anytime I try to incorporate these two into my report I get all software for each computer with the specified search criteria.

Maybe down the road, even include serial numbers once we get all of that info loaded into LS.

Any help would be greatly appreciated!

[keys_it]

This worked great! Thank you for the quick reply. All I had to do is change the search criteria and I am able to get the reports that I wanted.

Thanks again!

[Hemoco]

If you only want to list Acrobat installations, then you need something like this instead:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysIPLocations.IPLocation,
tsysOS.OSname,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%acrobat%' And tblAssetCustom.State = 1
Order By tsysIPLocations.IPLocation,
tblAssets.AssetName,
tblAssets.Lastseen Desc