→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
lowtone
Engaged Sweeper
I have a custom registry value that is either 0 or 1. I would like to report the count of tblOperatingsystem.Caption where tblregistry.value =1 and count of tblOperatingsystem.Caption where tblregistry.value =0 for a chart.


Can this be done on the same chart?




""
Select Top 1000000 tblOperatingsystem.Caption,
Count(tblRegistry.Value) As count
From tblRegistry
Inner Join tblAssets On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblRegistry.Value = 1 And tblOperatingsystem.Caption Not Like '%Server%'
And tblRegistry.Regkey Like '%Firewall%'
Group By tblOperatingsystem.Caption

""

Thanks,
T
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
A chart report should only contain two columns. The easiest way to build your report would be with three columns: one for the OS caption, one for the registry value (0 or 1) and one for the count. The only way to get everything into one chart (i.e. two columns) is to merge the first two fields into a single expression, like so:
Select Top 1000000 'OS: ' + tblOperatingsystem.Caption + ' / Value: ' +
tblRegistry.Value As [OS + Value],
Count(tblRegistry.AssetID) As Count
From tblRegistry
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblRegistry.AssetID
Where tblRegistry.Regkey Like '%firewall%' And tblOperatingsystem.Caption Not Like '%server%'
Group By 'OS: ' + tblOperatingsystem.Caption + ' / Value: ' + tblRegistry.Value
Order By [OS + Value]

View solution in original post

1 REPLY 1
Hemoco
Lansweeper Alumni
A chart report should only contain two columns. The easiest way to build your report would be with three columns: one for the OS caption, one for the registry value (0 or 1) and one for the count. The only way to get everything into one chart (i.e. two columns) is to merge the first two fields into a single expression, like so:
Select Top 1000000 'OS: ' + tblOperatingsystem.Caption + ' / Value: ' +
tblRegistry.Value As [OS + Value],
Count(tblRegistry.AssetID) As Count
From tblRegistry
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblRegistry.AssetID
Where tblRegistry.Regkey Like '%firewall%' And tblOperatingsystem.Caption Not Like '%server%'
Group By 'OS: ' + tblOperatingsystem.Caption + ' / Value: ' + tblRegistry.Value
Order By [OS + Value]