MS Audit - Report Needed

[asldesktop]

Hi,

We are currently undergoing a review/audit with Microsoft. I am looking to have a 90% completion and I am struggling to get some data using the MS tool provided as some machines are turned on.

However, I should have this data available in Lansweeper already! I am not very good with SQL and was wondering if anyone could provide assistance on writing a report to include the following information please?

MachineName
UserName
OrganizationalUnit
LastHWScanDate
LastSWScanDate
OSName
InstalledSoftware(All)
Vendor
Version
NumberOfProcessors
NumberOfLogicalProcessors
NumberOfCores
IPAddress
Manufacturer
ComputerModel
SerialNumber
DomainName
BIOSManufacturer
BIOSDescription
BIOSVersion
BIOSDate
BIOSSerialNumber

I have tried for many hours today to make a report myself but I have been banging my head against a wall without success. Any help/information would be appreciated.

Many Thanks.

[Daniel_B · Accepted solution]

The following report lists the data you require. If for your Microsoft SAM audit you only need to list Microsoft software, you might change the query to list only software from the publisher Microsoft or alternatively filter after running the report.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblState.Statename As State,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Username,
  tblADComputers.OU,
  tblAssets.Lastseen As [Last HW scan date],
  tblAssets.Lastseen As [Last SW scan date],
  tblOperatingsystem.Caption As OS,
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.softwareVersion,
  tblSoftware.Lastchanged As [last SW change],
  CPUCount.[Count physical CPUs],
  CPUCount.[Count logical CPUs],
  CPUCount.[Count CPU cores],
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssets.Domain,
  tBIOS.Manufacturer As [BIOS manufacturer],
  tBIOS.Description,
  tBIOS.Version,
  tBIOS.ReleaseDate,
  tBIOS.[BIOS serialnumber]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  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
  Left Join (Select Max(tblBIOS.Lastchanged) As lastchanged,
    tblBIOS.Caption As Description,
    tblBIOS.Manufacturer,
    tblBIOS.ReleaseDate,
    tblBIOS.SerialNumber As [BIOS serialnumber],
    tblBIOS.SMBIOSBIOSVersion As Version,
    tblBIOS.AssetID
  From tblBIOS
  Group By tblBIOS.Caption,
    tblBIOS.Manufacturer,
    tblBIOS.ReleaseDate,
    tblBIOS.SerialNumber,
    tblBIOS.SMBIOSBIOSVersion,
    tblBIOS.AssetID) tBIOS On tblAssets.AssetID = tBIOS.AssetID
  Inner Join tblState On tblAssetCustom.State = tblState.State
Where tsysAssetTypes.AssetType = -1
Order By tblAssets.AssetName,
  tblSoftwareUni.softwareName