Need Report for Microsoft True Up

[jblair606]

I need to get a report out of LanSweeper and I am not familiar with writing SQL queries. I have a very limited time to provide this data and would very much appreciate getting a report for the following details:

Operating System
Service Pack
# of CPU
# of cores per CPU
# of cores total
Memory
Serial #



I also need to know if any of the software is installed on these systems and relevant version information:
Terminal Service/Remote Desktop, Failover Cluster, SQL Server, SharePoint, MS Office, Exchange, Forefront, Skype, BizTalk, Visual Studio.

[MikeMc]

Unfortunately, I don't know where that version of Lansweeper stores that info, if at all. You can try reaching out to support to see if they point you in the right direction. At the absolute worst, you may need to look outside of LS to get this information. Here are some reference links:

• https://social.technet.microsoft.com/Forums/office/en-US/add84c06-1443-4d15-acec-980b2786ba8d/powershell-script-to-find-all-terminal-servers-in-domain?forum=winserverTS
  
• http://stackoverflow.com/questions/28709500/powershell-query-servers-with-roles-installed

[jblair606]

Mike,

We are running v5242 at this time. We are planning to upgrade in the near future. I was able to get the Cluster information via built-in Service report. I am still unable to get the remote desktop services/terminal services information at this current version. If you have any ideas on that I would be interested in hearing any input you have.

Thanks a lot,
Jordan

[MikeMc]

The error indicates that the tblFeature table may not exist in your Lansweeper instance. What version of LS are you running? If it's older than the most recent version, I'm not sure how the info was stored.

[jblair606]

These reports are pretty much exactly what I needed and are super useful. Thanks a lot.

I'm still having some trouble getting the Features/Roles information, specifically Failover Cluster and Remote Desktop. If I can get this final piece I'll be all set.

Thanks again everyone,
Jordan

[jblair606]

Invalid SELECT statement. Unknown object name: "tblFeature".: Unexpected token "tblFeature" at line 0, column -1

Getting this on the Features and Roles Info portion of the code you provided. What needs to be changed?

Thanks,
Jordan

[jblair606]

Mike, thank you so much for your timely response. That means a lot.
Let me give this a shot.

Thanks again
Jordan

[MikeMc]

These reports won't cover everything you need but it should get you started:

Asset Info

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblOperatingsystem.Caption As OS,
  tblOperatingsystem.ServicePackMajorVersion As SP,
  tblAssets.NrProcessors As CPUCount,
  T1.CoreCount,
  (tblAssets.NrProcessors * T1.CoreCount) As TotalCoreCount,
  tblAssets.Memory,
  tblAssetCustom.Serialnumber
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
    tblAssets.AssetID
  Inner Join (Select tblProcessor.AssetID,
    Max(tblProcessor.NumberOfCores) As CoreCount
  From tblProcessor
  Group By tblProcessor.AssetID) T1 On T1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

Features and Roles Info

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblFeatureUni.featureName,
  tblFeatureUni.featureCaption
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblFeature On tblFeature.AssetId = tblAssets.AssetID
  Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
Where (tblFeatureUni.featureCaption Like '%Remote Desktop%' Or
    tblFeatureUni.featureCaption Like '%Failover Cluster%') And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName

SQL Server Info

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblSqlServers.displayVersion,
  tblSqlServers.skuName As Edition,
  tblSqlServers.version
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblSqlServers On tblSqlServers.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

Installed Software Info

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.softwareVersion,
  tblSoftware.Installdate
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%SharePoint%' Or
    tblSoftwareUni.softwareName Like 'Microsoft Office%' Or
    tblSoftwareUni.softwareName Like '%Exchange%' Or
    tblSoftwareUni.softwareName Like '%Forefront%' Or
    tblSoftwareUni.softwareName Like '%Skype%' Or tblSoftwareUni.softwareName
    Like '%BizTalk%' Or tblSoftwareUni.softwareName Like '%Visual Studio%') And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Brucem02]

Hi Mike I need a MS True SQl query for desktop

OS (version & edition), ☐  Last scanned date, ☐  Assigned to,☐  Is virtual,☐  Power state (if virtual),☐  Number of Processors and cores (if required) ☐  Environment (if applicable)

Installed software (Desktop)

☐  Computer name, ☐  Software name, version, edition, publisher, ☐  Product type, ☐  Last used date, ☐  Discovery source

Servers

☐   Server Name (and naming convention – if possible), ☐   OS, ☐   Last scanned date, ☐   Discovery source, ☐   Server role (environment ex. Prod, Dev, QA, Test, DR, etc.), ☐   Is virtual, ☐   Power state (On or Off), ☐  Number and type of CALs (Get CAL Usage report for RDS CALs if not using Azure AD Domain)

Installed Software Server

☐   Server name, ☐  Software name, Publisher, Version, and Edition, ☐  Product type, ☐   Last used date, ☐  Number and type of CALs (if applicable), 

Host Relationship

☐   Host name (physical and/or virtual), ☐   Cluster names from vCenter/Hyper-V, ☐   Number of processors and cores, ☐   Environment (ex. Prod, Dev, QA, Test, DR, etc.), ☐   Power state (On or Off), ☐   Is vMotion used.