cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
peckk
Engaged Sweeper II

See screenshot. I copied this report from lansweeper in "download reports" but for some reason this error will not go away and I don't know how to fix it. SQL is not my strong suit here.

1 ACCEPTED SOLUTION
Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

I will report this to our marketing team as there indeed appears to be an error with this report. Thanks for reporting it!

View solution in original post

8 REPLIES 8
peckk
Engaged Sweeper II

Yeah here is that link. https://www.lansweeper.com/report/windows-performance-counters-statistics/ and the code below.

Select Top 1000000 a.AssetID,
Min(a.AssetName) As AssetName,
Min(a.Domain) As Domain,
Min(Coalesce(os.Image, at.AssetTypeIcon10)) As icon,
Min(a.IPAddress) As IPAddress,
Min(ipl.IPLocation) As IPLocation,
Min(ac.Manufacturer) As Manufacturer,
Min(ac.Model) As Model,
Min(os.OSname) As OsName,
Min(a.Lastseen) As Lastseen,
Min(a.Lasttried) As Lasttried,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
Min(pcsm.Value) As MinValue,
Max(pcsm.Value) As MaxValue,
Min(pcsmi.Name) As Identifier
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tsysAssetTypes at On at.AssetType = a.Assettype
Inner Join tsysIPLocations ipl On ipl.LocationID = a.LocationID
Inner Join tblState On tblState.State = ac.State
Left Join tsysOS os On os.OScode = a.OScode
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where tblState.Statename = 'Active' And pcs.ScanDateTime > GetDate() - 30 And
a.Assettype = -1
Group By a.AssetID,
pcm.Id,
pcsmi.Id
Order By AssetName,
MetricName

Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

I will report this to our marketing team as there indeed appears to be an error with this report. Thanks for reporting it!

peckk
Engaged Sweeper II

Thank you. I also would like to point out this report as well as it does not seem to work either. 

Windows & Linux Performance Stats - Lansweeper IT Asset Management

Select Top 1000000 a.AssetID,
Min(a.AssetName) As AssetName,
Min(a.Domain) As Domain,
Min(Coalesce(os.Image, at.AssetTypeIcon10)) As icon,
Min(a.IPAddress) As IPAddress,
Min(ipl.IPLocation) As IPLocation,
Min(ac.Manufacturer) As Manufacturer,
Min(ac.Model) As Model,
Case Min(a.Assettype)
When -1 Then Min(os.OSname)
When 11 Then Min(ls.OSRelease)
Else ''
End As OsName,
Min(a.Lastseen) As Lastseen,
Min(a.Lasttried) As Lasttried,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
Min(pcsm.Value) As MinValue,
Max(pcsm.Value) As MaxValue,
Min(pcsmi.Name) As Identifier
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tsysAssetTypes at On at.AssetType = a.Assettype
Inner Join tsysIPLocations ipl On ipl.LocationID = a.LocationID
Inner Join tblState On tblState.State = ac.State
Left Join tsysOS os On os.OScode = a.OScode
Left Join tblLinuxSystem ls On ls.AssetID = a.AssetID
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where tblState.Statename = 'Active' And pcs.ScanDateTime > GetDate() - 30
Group By a.AssetID,
pcm.Id,
pcsmi.Id
Order By AssetName,
MetricName 

Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

So I see. I will add that one to the marketing ticket as well and there appears to be a broken image on that page as well. 🤔

peckk
Engaged Sweeper II

Thank you. Hopefully they can get it fixed. 

Mister_Nobody
Honored Sweeper

'at' is reserved word.

You must use always [at] or use another alias - my_at, for example.

Mister_Nobody
Honored Sweeper

Paste code not screenshot, please.

Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

Would you be able to provide a link to the report that you have downloaded?