Audit Report based on Publisher/DisplayName

[quagmire0]

Not very handy with SQL, so I could use some help with the following requirements:

Please provide inventory output from Lan Sweeper (using keywords: Microsoft, SQL, Citrix, Office against the Publisher and DisplayName fields).
For each machine, please provide:
• Machine Name
• Software Publisher
• Operating System (Version and Edition)
• Software Name
• Software Version (2007, 2010, 2012 etc.)
• Software Edition (Enterprise, Standard, Premium etc.)
• Last Scan Date
• Last Logon
• Location
• Processor Count
• Core Count
• Processor Vendor Brand

Thanks in advance!

[Daniel_B · Accepted solution]

For this you'll need to use several subqueries. We recommend as well to use multiple reports instead of only one as otherwise you will get a lot of duplicate rows.

For SQL server:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblOperatingsystem.Caption As OS,
  tblSqlServers.displayVersion As [SQL server version],
  tblSqlServers.skuName As [SQL server edition],
  tblAssets.Lastseen,
  tblAssets.Username,
  tLastLogon.[last logon],
  tblAssetCustom.Location,
  CPUCount.[Count physical CPUs],
  CPUCount.[Count CPU cores],
  tblAssets.Processor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Left Join (Select Max(tblCPlogoninfo.logontime) As [last logon],
    tblCPlogoninfo.AssetID
  From tblCPlogoninfo
  Group By tblCPlogoninfo.AssetID) tLastLogon On tblAssets.AssetID =
    tLastLogon.AssetID
  Left Join (Select tblProcessor.AssetID,
    Sum(tblProcessor.NumberOfLogicalProcessors) As [Count logical CPUs],
    Sum(tblProcessor.NumberOfCores) As [Count CPU cores],
    Count(tblProcessor.Caption) As [Count physical CPUs]
  From tblProcessor
  Group By tblProcessor.AssetID) CPUCount On tblAssets.AssetID =
    CPUCount.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

For Office and Citrix:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tSoftware.SoftwarePublisher,
  tblOperatingsystem.Caption As OS,
  tSoftware.softwareName,
  tSoftware.softwareVersion,
  tblAssets.Lastseen,
  tblAssets.Username,
  tLastLogon.[last logon],
  tblAssetCustom.Location,
  CPUCount.[Count physical CPUs],
  CPUCount.[Count CPU cores],
  tblAssets.Processor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName,
    tblSoftwareUni.SoftwarePublisher,
    tblSoftware.softwareVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Where (tblSoftwareUni.softwareName Like '%Microsoft office standard 20%') Or
    (tblSoftwareUni.softwareName Like '%Microsoft office proplus 20%') Or
    (tblSoftwareUni.softwareName Like '%Microsoft office ultimate 20%') Or
    (tblSoftwareUni.softwareName Like '%Microsoft office home and business 20%')
    Or
    (tblSoftwareUni.softwareName Like '%Microsoft office professional plus 20%')
    Or
    (tblSoftwareUni.softwareName Like '%Microsoft office professional 20%') Or
    (tblSoftwareUni.SoftwarePublisher Like 'Citrix%')) tSoftware
    On tSoftware.AssetID = tblAssets.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join (Select Max(tblCPlogoninfo.logontime) As [last logon],
    tblCPlogoninfo.AssetID
  From tblCPlogoninfo
  Group By tblCPlogoninfo.AssetID) tLastLogon On tblAssets.AssetID =
    tLastLogon.AssetID
  Left Join (Select tblProcessor.AssetID,
    Sum(tblProcessor.NumberOfLogicalProcessors) As [Count logical CPUs],
    Sum(tblProcessor.NumberOfCores) As [Count CPU cores],
    Count(tblProcessor.Caption) As [Count physical CPUs]
  From tblProcessor
  Group By tblProcessor.AssetID) CPUCount On tblAssets.AssetID =
    CPUCount.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  tSoftware.SoftwarePublisher,
  tSoftware.softwareName