→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
CyberCitizen
Honored Sweeper
Hi Guy's I have a report as per below.

Its a battery life report etc. I am wanting to calculate the battery life, for example 44004 is the Design Capacity, Warranty terms are 25% of the batter per year etc, I was wanting to see if there is a way to calculate the from the figures Design Capacity vs Current Capacity to see what percentage of battery drain has occurred.

So eg in the screenshot 11901 of 44004 is 27%



Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
SubQuery2.Value As [Battery Design Capacity],
SubQuery1.Value As [Battery Capacity],
Case
When SubQuery1.Value < '30000' Then '#ffadad'
When SubQuery1.Value < '35000' Then '#fff1ad'
When SubQuery1.Value > '35000' Then '#d4f4be'
End As backgroundcolor,
tblWarrantyDetails.WarrantyEndDate,
SubQuery1.Lastchanged,
TsysLastscan.Lasttime As LastRegistryScan,
tblAssets.Lastseen,
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.'
End As Comment
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\lansweeper' And
tblRegistry.Valuename = 'BatteryCapacity') SubQuery1 On
SubQuery1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\lansweeper' And
tblRegistry.Valuename = 'BatteryDesignedCapacity') SubQuery2 On
SubQuery2.AssetID = tblAssets.AssetID
Inner Join lansweeperdb.dbo.tblWarranty On tblAssets.AssetID =
tblWarranty.AssetId
Inner Join lansweeperdb.dbo.tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Where Case
When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
Then 'Yes'
Else 'No'
End = 'Yes' And tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By [Battery Capacity],
tblAssets.Domain,
tblAssets.AssetName
1 REPLY 1
Hendrik_VE
Champion Sweeper III
Something like this should work I guess:

Ceiling(SubQuery1.Value / SubQuery2.Value * 100) As [%CapacityLeft]