Report Request: Lifecycle/Rollout Project

[Hobbs13]

My company will be planning a large lifecycling project and we need a report to build around.
I am using Lansweeper 5.0.0.69 (just updated this morning) The goal of the report is to gain a better understanding of the age of the environment and list information about each PC so we can target groups of systems for a rollout/upgrade. I am hoping someone can help me with the following report as it is crucial to our planning.

AssetName
Type(Desktop, Laptop)
Manufacturer
Model
Serialnumber
User(Last Login)
OS (Looking For Base OS - Example: Windows 8, Windows 7 - Not versions/SP)
Microsoft Office Version (Looking For Base Office Version - Example: Office 2010 - Not versions/SP)
LastKnownIPAddress
LastSeenDate
Finally, I would like the report to be sorted by the Age of the PC determined by the BIOS date of the machine. Age should show up as Year.Months if possible.

I have been unable to piece together this report, but I know the mighty LanSweeper is capable of this as other reports have been posted here that show what I need, just not all together in one super report! Thanks in advance for the help on this one...

[Hemoco]

Try:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tsysOS.OSname,
tblAssets.SP,
tblAssetCustom.Serialnumber As AssetTag,
tblAssets.Lastseen,
tblAssets.IPAddress,
Round((Cast((DateDiff(mm, tblBIOS.ReleaseDate, GetDate())) As float) / 12),
2) As AgeInYears
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Order By AgeInYears Desc

[Hobbs13]

Ok. Anyway you can edit this report to remove the office part?
I'll just run two reports for what I need...

[Hobbs13]

This worked almost perfectly. One catch. If the machine has two different office products installed Example: Office 2007 Professional + Microsoft Visio Professional the report lists the same PC on multiple rows.

Is there anyway to eliminate the duplicate rows by listing multiple office products on a single line?

I realize this can probably be done after the fact when exported to excel, but I have not figured out the proper filtering yet. I was hoping to find a solution here without researching that step.

[Hemoco]

Hobbs13 wrote:
Is there anyway to eliminate the duplicate rows by listing multiple office products on a single line?

No, this is not possible. SQL displays table records as lines, not columns.

[Hemoco · Accepted solution]

Please try the report below for the information you are after.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tsysOS.OSname,
tblAssets.SP,
tblAssetCustom.Serialnumber As AssetTag,
tblAssets.Lastseen,
tblAssets.IPAddress,
tblSoftwareUni.softwareName As OfficeVersion,
Round((Cast((DateDiff(mm, tblBIOS.ReleaseDate, GetDate())) As float) / 12),
2) As AgeInYears
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Where tblSoftwareUni.softwareName Like 'Microsoft%office%20%'
Order By AgeInYears Desc

[Hobbs13]

Here’s the code I have so far….

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblAssets.SP,
tblAssetCustom.Serialnumber As AssetTag,
tblAssets.Lastseen,
tblAssets.IPAddress
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode

Looking for help on:
• Office Version Installed (Only interested in base install Example: Office 2010 or Office 2007 and not the version numbers or SP info)
• Sorting Report by Age (Based on BIOS Date) Age should show up on report as Years.Months
• Desktop or Laptop