Script help needed

[jimbob]

Hi there

Please excuse this request, as I'm a little inexperienced when it comes to writing scripts for lansweeper.

I'm looking for a script to run that will display the following
Model
OS
Version of Microsoft Office
Location

is there a simple way to run a report for this information?

Any guidance is massively appreciated.

thanks
Mo

[jimbob]

fantastic guys.
really appreciate your assistance and guidance on this.

[RCorbeil · Accepted solution]

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysOS.OSname,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
  INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
Where
  tblAssetCustom.State = 1
  AND tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft Corp%'
  AND tblSoftwareUni.softwareName     LIKE '%Microsoft Office%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Visio %'
  AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
  AND tblSoftwareUni.softwareName NOT LIKE '%web comp%'
  AND tblSoftwareUni.softwareName NOT LIKE '%connector%'
  AND tblSoftwareUni.softwareName NOT LIKE '%click-to-run%'
  AND tblSoftwareUni.softwareName NOT LIKE '%validat%'
  AND tblSoftwareUni.softwareName NOT LIKE '%live meet%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Add-in%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Time Zone Data Update Tool%'

Microsoft names a lot of bits and pieces that aren't Office proper as "Microsoft Office something-or-other", hence all the "NOT LIKE" filters.

[jimbob]

thanks for that Jeffrey

It works pretty much.. just that the OS version isnt displayed, the AssetTypeName displays Windows - and I'm looking for it to list the version of Windows.

I've tried looking for the value in any applicable tables, but just hitting brick walls with my limited knowledge.

any help is very much appreciated.

[sukaitsu]

Hey Jimbob,

This may get moved to a new area since its more of a report request, but I'll still post an answer for ya. You need to create a new report and replace the SQL code auto-generated with the code below.

Report Name: Software: Microsoft Office ()

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tblSoftwareUni.softwareName,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like 'Microsoft Office Pro%') Or
  (tblSoftwareUni.softwareName Like 'Microsoft Office 365%' And
  tblAssetCustom.State = 1)
Order By tblAssets.AssetName

Thank you,

Jeffrey