cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Mister_Nobody
Honored Sweeper

LS has one powerful chart. It is line-diagram. It can show more than 2 column data.

I have developed SQL-query for BIOS and Monitor Ages. It have to be used with this type chart.

You should use report's name with prefix "Chart:".

Chart: BIOS and Monitor Ages

Query:

 

Select Top 1000000 Coalesce(monitor_t.MonitorYear, bios_t.BiosYearReleased) As
  ReleasedYear,
  Coalesce(monitor_t.TotalM, 0) As monitor_count,
  Coalesce(bios_t.TotalB, 0) As bios_count
From (Select DatePart(yyyy, tblMonitor.ManufacturedDate) As MonitorYear,
      Count(tblMonitor.MonitorID) As TotalM
    From tblMonitor
    Where DatePart(yyyy, tblMonitor.ManufacturedDate) <> 1990
    Group By DatePart(yyyy, tblMonitor.ManufacturedDate)) monitor_t
  Full Join (Select DatePart(yyyy, tblBIOS.ReleaseDate) As BiosYearReleased,
      Count(tblAssets.AssetID) As TotalB
    From tblAssets
      Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
    Group By DatePart(yyyy, tblBIOS.ReleaseDate)) As bios_t On
      monitor_t.MonitorYear = bios_t.BiosYearReleased
Order By ReleasedYear

 

After create report you can add Chart Report on Tab via drag&drop it

Mister_Nobody_0-1706601149399.png

Choose line-type and show label. My result:

Mister_Nobody_1-1706601243070.png

5 REPLIES 5
Hendrik_VE
Champion Sweeper III

Here is my result (industrial environment), monitors are not scanned:

Hendrik_VE_0-1706703896768.png

 

I have another SQL-report with using "UNION ALL"

Chart: Bios Age Linux and Windows

Select Top 1000000 DatePart(yyyy, tBios.ReleaseDate) As BiosYearReleased,
  Count(tblAssets.AssetID) As Total
From tblAssets
  Inner Join (Select tblBIOS.AssetID,
      tblBIOS.ReleaseDate
    From tblBIOS
    Union All
    Select tblLinuxBios.AssetID,
      tblLinuxBios.ReleaseDate
    From tblLinuxBios) tBios On tblAssets.AssetID = tBios.AssetID
Group By DatePart(yyyy, tBios.ReleaseDate)
Order By BiosYearReleased

 

One of the goals to create this report was to show that LS can create multi-sources chart!

You have only one source of info.

Mister_Nobody
Honored Sweeper

Changelog: 
+Add two  filters for monitors with incorrect EDID and remove VMs BIOSes.
-Use less tables for report.

 

Select Top 1000000 Coalesce(monitor_t.MonitorYear, bios_t.BiosYearReleased) As
  ReleasedYear,
  Coalesce(monitor_t.TotalM, 0) As monitor_count,
  Coalesce(bios_t.TotalB, 0) As bios_count
From (Select DatePart(yyyy, tblMonitor.ManufacturedDate) As MonitorYear,
      Count(tblMonitor.MonitorID) As TotalM
    From tblMonitor
    Where tblMonitor.ManufacturedDate Not In ('01/01/1990', '03/06/1992',
      '01/12/2011')
    Group By DatePart(yyyy, tblMonitor.ManufacturedDate)) monitor_t
  Full Join (Select DatePart(yyyy, tblBIOS.ReleaseDate) As BiosYearReleased,
      Count(tblBIOS.AssetID) As TotalB
    From tblBIOS
    Where tblBIOS.manufacturer Not In ('VMware, Inc.', 'Microsoft Corporation')
    Group By DatePart(yyyy, tblBIOS.ReleaseDate)) As bios_t On
      monitor_t.MonitorYear = bios_t.BiosYearReleased
Order By ReleasedYear

 

 

Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

Thanks for sharing this! 👍

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now