Report all Dell PC's and OS

[Chad]

Hello everyone, I am new to building reports with Lansweeper and was curious if someone could point me in the right direction.

I would like to be able to run a report of all "Dell" branded PC's regardless of model and show the OS that they are running as well.

[RCorbeil]

I got fed up trying to link to the software directly without returning too many or too few results so opted to join to a sub-select.

First, I suggest you run this:

SELECT Distinct
  softwarename
FROM
  tblSoftwareUni
WHERE
  SoftwarePublisher LIKE 'Microsoft%'
  AND softwareName LIKE 'Microsoft Office%'
  AND softwareName NOT LIKE '%Viewer%'
  AND softwareName NOT LIKE '%Visio %'
  AND softwareName NOT LIKE '%Project%'
  AND softwareName NOT LIKE '%web comp%'
  AND softwareName NOT LIKE '%connector%'
  AND softwareName NOT LIKE '%click-to-run%'
  AND softwareName NOT LIKE '%validat%'
  AND softwareName NOT LIKE '%live meet%'

Microsoft has named way too many programs and components "Microsoft Office something-or-other" that aren't actually Microsoft Office. I've included several substrings to search for that are in my inventory. If you turn up anything other than actual Microsoft Office results with the above, you'll want to add to the list of exceptions. You'll need that list for the actual query you're after.

Adding to the "list me the Dells" query:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysOS.OSname,
  CASE
    WHEN tblComputersystem.SystemType Like 'X86%' THEN '32-bit'
    WHEN tblComputersystem.SystemType Like 'x64%' THEN '64-bit'
    ELSE ''
  END AS Bitness,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  msoffice.SoftwarePublisher,
  msoffice.softwareName,
  msoffice.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 tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  LEFT JOIN ( SELECT
                tblSoftware.AssetID,
                tblSoftwareUni.SoftwarePublisher,
                tblSoftwareUni.SoftwareName,
                tblSoftware.SoftwareVersion
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                    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%'
            ) AS msoffice ON msoffice.AssetID = tblAssets.AssetID
WHERE
  tblAssetCustom.State = 1
  AND tblAssetCustom.Manufacturer LIKE 'Dell%'

(Inclusion of the publisher and version number are optional, of course.)

If you came up with more exceptions that you had to add to the short query above, add your new exceptions to the list in the big query.

[Chad]

If I wanted to keep all of the data that is on there so far as well as add the ability to see if Microsoft Office is on the machine, is that possible as well?

[RCorbeil · Accepted solution]

Using the default base query as a starting point:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysOS.OSname,
  CASE
    WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
    WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
    ELSE ''
  END AS Bitness,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber
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 tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
  tblAssetCustom.State = 1
  AND tblAssetCustom.Manufacturer LIKE 'Dell%'

[Chad]

RC62N wrote:
Using the default base query as a starting point:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysOS.OSname,
  CASE
    WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
    WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
    ELSE ''
  END AS Bitness,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber
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 tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
  tblAssetCustom.State = 1
  AND tblAssetCustom.Manufacturer LIKE 'Dell%'

Incredible, did exactly what I wanted. This will help me immensely, thank you very much!