I would like to export the logs from devices - specifically the Home Energy Monitor.
I can view it in the application, but an 'export to excel' feature would be great!
Log export
- 243 Views
- Last Post 30 November 2015
evldave
posted this
25 November 2015
treeVt6
posted this
30 November 2015
Here is a powershell script that will dump the level data into a csv file. It requires the SQL Server Compact dll "System.Data.SqlServerCe.dll". For some reason I have been unable to get it to work by using the file that InControl comes with. Instead, first install the 64bit version of Microsoft SQL Server Compact 4.0 from https://www.microsoft.com/en-us/download/details.aspx?id=17876. Then, set the DB path and csv path at the top and run it.
I was using something similar to upload directly to GroveStreams which supports a step-style of charting that works well with this kind of ad hoc change-based data (instead of regular intervals), but the free amount of data they give you didn't last long.
If anyone knows a better way or can get the dll that comes with InControl to work so you don't have to install sql please let me know!
[code]# requires Microsoft SQL Server Compact 4.0
# SSCERuntime_x64-ENU.exe
# https://www.microsoft.com/en-us/download/details.aspx?id=17876
$DBPath = "c:\ProgramData\ZWave\zwave.sdf"
$outputFilePath = "c:\temp\zwave.csv"
[Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private\System.Data.SqlServerCe.dll")
$connectionString = "Data Source='$DBPath';"
$connection = New-Object "System.Data.SqlServerCe.SqlCeConnection" $connectionString
$command = New-Object "System.Data.SqlServerCe.SqlCeCommand"
$command.CommandType = [System.Data.CommandType]"Text"
$command.Connection = $connection
$connection.Open()
$command.CommandText = "SELECT LevelLogs.LevelValue, levelLogs.logDate, ZWaveDevices.deviceName FROM ZWaveDevices INNER JOIN LevelLogs ON ZWaveDevices.deviceID=LevelLogs.deviceID;"
$sqlReader = $command.ExecuteReader()
$logs = while ($sqlReader.Read()) {
[pscustomobject] @{
"deviceName" = $sqlReader["deviceName"]
"logDate" = $sqlReader["logDate"]
"LevelValue" = $sqlReader["LevelValue"]
}
}
$logs | Export-Csv -NoTypeInformation $outputFilePath
$command.Dispose()
$connection.Close()
$connection.Dispose[/code]
treeVt6
posted this
30 November 2015
It's also worth noting that incontrol should be shutdown when accessing the DB in this way since it can lock the DB and prevent incontrol from accessing it.
