Nice job Michael! Now I know that it's time for a new battery for my laptop 🙂
We regularly use the same approach here, writing powershell output to regkeys or eventlogs. Unfortunately, there's no 'standard' way for custom scanning.
Can't you add 'batterydesignedcapacity' as a second subquery?
And for colour coding, I think you must use this:
Case
When SubQuery1.Value < '30000' Then '#ffadad'
When SubQuery1.Value < '35000' Then '#fff1ad'
When SubQuery1.Value > '40000' Then '#d4f4be'
End As backgroundcolor
And congrats on the 4 years Esben, still feels like yesterday
We regularly use the same approach here, writing powershell output to regkeys or eventlogs. Unfortunately, there's no 'standard' way for custom scanning.
Can't you add 'batterydesignedcapacity' as a second subquery?
And for colour coding, I think you must use this:
Case
When SubQuery1.Value < '30000' Then '#ffadad'
When SubQuery1.Value < '35000' Then '#fff1ad'
When SubQuery1.Value > '40000' Then '#d4f4be'
End As backgroundcolor
And congrats on the 4 years Esben, still feels like yesterday
Hendrik.VE wrote:
Nice job Michael! Now I know that it's time for a new battery for my laptop 🙂
We regularly use the same approach here, writing powershell output to regkeys or eventlogs. Unfortunately, there's no 'standard' way for custom scanning.
Can't you add 'batterydesignedcapacity' as a second subquery?
And for colour coding, I think you must use this:
Case
When SubQuery1.Value < '30000' Then '#ffadad'
When SubQuery1.Value < '35000' Then '#fff1ad'
When SubQuery1.Value > '40000' Then '#d4f4be'
End As backgroundcolor
And congrats on the 4 years Esben, still feels like yesterday
Thank you but that goes above my skill set, that report code was stolen from another report and tweaked and got it working some how :P.
Thanks Esben & congrats on the 4 years at Lansweeper.
Unfortunately, that report mostly ended up with blank data for me.
I did end up creating a deployment package that saves the data to the registry.
I then have the following report which checks against these keys to see what the current battery status max charge is. I would like to have the max capacity in the report as well but don't think you can have custom scanning results on separate columns without having seperate entries / rows for the machine, is that correct?
Any that show on this report with limited capacity I am then running a Windows 10 Battery Report to look at battery capacity drops etc.
powercfg /batteryreport /output "\\SERVERNAME\Apps$\Battery Reports\%COMPUTERNAME%.Battery.Report.html"
What I am having trouble with is colour coding this report.
Was trying to do something like this but couldn't get the case requirements in the correct location, SQL I have no real experience with.
Unfortunately, that report mostly ended up with blank data for me.
I did end up creating a deployment package that saves the data to the registry.
<?xml version="1.0" encoding="utf-8"?>
<Package>
<Name>CMD.Write Battery Info to Registry</Name>
<Description></Description>
<ShutdownOption>0</ShutdownOption>
<ShutdownTime>0</ShutdownTime>
<MaxDuration>900</MaxDuration>
<Rescan>True</Rescan>
<RunMode>2</RunMode>
<Steps>
<Step>
<Nr>1</Nr>
<Name>Writes Battery Designed Capacity</Name>
<Type>2</Type>
<ReturnCodes>0,1641,3010</ReturnCodes>
<Success>-1</Success>
<Failure>-1</Failure>
<Path></Path>
<Parameters></Parameters>
<MSIParameters></MSIParameters>
<MSIName></MSIName>
<MSIVersion></MSIVersion>
<Command>cd %tmp% & Powershell Get-WmiObject -Namespace 'root\wmi' -Query 'select DesignedCapacity from BatteryStaticData' > battery.txt & if %errorlevel%==0 (findstr /l /b " DesignedCapacity " battery.txt > id.txt & for /f "tokens=2 delims=: " %a in (id.txt) do (reg add HKLM\Software\Lansweeper /v BatteryDesignedCapacity /t REG_SZ /d %a /f)) & del /f id.txt & del /f battery.txt</Command>
<EditMode>False</EditMode>
<Conditions />
</Step>
<Step>
<Nr>2</Nr>
<Name>Writes Battery Capacity</Name>
<Type>2</Type>
<ReturnCodes>0,1641,3010</ReturnCodes>
<Success>-2</Success>
<Failure>-3</Failure>
<Path></Path>
<Parameters></Parameters>
<MSIParameters></MSIParameters>
<MSIName></MSIName>
<MSIVersion></MSIVersion>
<Command>cd %tmp% & Powershell Get-WmiObject -Namespace 'root\wmi' -Query 'select FullChargedCapacity from BatteryFullChargedCapacity' > battery.txt & if %errorlevel%==0 (findstr /l /b " FullChargedCapacity " battery.txt > id.txt & for /f "tokens=2 delims=: " %a in (id.txt) do (reg add HKLM\Software\Lansweeper /v BatteryCapacity /t REG_SZ /d %a /f)) & del /f id.txt & del /f battery.txt</Command>
<EditMode>False</EditMode>
<Conditions />
</Step>
</Steps>
<SoftwareVersion>8.4.100.9</SoftwareVersion>
</Package>
I then have the following report which checks against these keys to see what the current battery status max charge is. I would like to have the max capacity in the report as well but don't think you can have custom scanning results on separate columns without having seperate entries / rows for the machine, is that correct?
Any that show on this report with limited capacity I am then running a Windows 10 Battery Report to look at battery capacity drops etc.
powercfg /batteryreport /output "\\SERVERNAME\Apps$\Battery Reports\%COMPUTERNAME%.Battery.Report.html"
What I am having trouble with is colour coding this report.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
SubQuery1.Value As [Battery Capacity],
tblWarrantyDetails.WarrantyEndDate,
SubQuery1.Lastchanged,
TsysLastscan.Lasttime As LastRegistryScan,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
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
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 tblAssets.Domain,
tblAssets.AssetName
Was trying to do something like this but couldn't get the case requirements in the correct location, SQL I have no real experience with.
Case
When tblRegistry.Valuename < '30000' Then '#ffadad'
When tblRegistry.Valuename < '35000' Then '#fff1ad'
When tblRegistry.Valuename > '40000' Then '#d4f4be'
End As backgroundcolor,
