ELVira: Excel Log Viewer for Vera

Developer's Corner General Dev Discussions
#1

Executive Summary: “Single Button Push” access to the vera log locally or remotely. The end result is the log presented in Excel based on pre-defined filters set-up by the user.

More details / Background:
So I’m not a native linux / unix guy…my roots are in Windows (and DOS before that), and MS Office products. During development cycles for Vera I was never really happy with the process for retrieval and inspection of logs and wanted something more “streamlined”. I’ve been using something like this for a while, and so decided to formalize it a bit and slap a basic UI on it. The result is ELVira…the Exel Log Viewer for Vera.

(Yes, the acronym is cheesy…that’s the way I like 'em!)

Anyway, this app will preserve the log’s coloring (if that’s what you want, or you can just strip out the color coding), and the performance is actually better than I expected when handling large files. The elapsed time from pushing the “refresh log” button to the time a filtered log is displayed in Excel is about 10 seconds on my local network. (For a log with about 13,000 lines. )

Because the file transfer helper app (PSCP) uses SSH, you can use ELVira remotely if you are forwarding the appropriate port on your router to your Vera IP.

Requirements:

[ol][li]Microsoft Excel…with macros enabled when you open up the file.[/li]
[li]Windows based PC. (This can be modified to work with Mac versions of office, but this is not in place at this time.)[/li]
[li]PSCP (optional): Download from the Putty Download Page. This is a command line secure file-copy utility for transferring files between your PC and Vera. The file is not “installable”, it is simply the excecutable. Just download it to any location on your PC. Without PSCP, you can still use ELVira as a log viewer / filtering app, but you’ll need to obtain a copy the log file to you PC by some other means.[/li]
[li]Your Vera root password.[/li][/ol]

Setup:

[ol][li]Download ELVira (attached to this post in the ZIP file). [/li]
[li]Download a copy of PSCP. (If using the auto-transfer feature). Doesn’t matter where you keep the file, but you’ll probably want to keep it in the same place you have Putty, or if you don’t have putty, in the same folder that you have ELVira.[/li]
[li]Make sure you know you Vera SSH password. If not, follow the directions here to get it.[/li]
[li]Open ELVira in Excel. This is a macro enabled Excel spreadsheet, so when you open the file you will need to “enable macros” when presented with a security warning.[/li]
[li]Click on the File Transfer Setup button, and enter in your settings, or select the “Prompt for file on local PC” option.[/li][/ol]

I will more document the settings and how they work in the next post.

Version Notes:
Version 0.5: Jan 25, 2015. Initial beta release .

I have not tried this yet with verbose logging enabled, so there may be some color escape codes that are used that are not yet supported.

I also have not put much (ok any) effort into validating the input for the settings and filters. So if you don’t put in a valid IP, forget to enter your password, put non numbers in numeric fields, etc, you’ll likely to just end up getting arcane error messages for the time being.

#2

ELVira Workflow and Settings

The idea behind ELVira is basically to accommodate this workflow:

[ol][li]I want to retrieve the log file from Vera.[/li]
[li]I want to look at some subset of that log file in Excel, but:[/li][/ol]

        a) I want to filter out some of the log records right from the start.  Don't even import them into Excel.
        b) I may want to apply some other filters in Excel once the log is there...so I still want to have them accessible, just be able to show / hide them at will on the fly basd on how I change my filter.

  3.  And then as I can press the "refresh log" button at any time to grab the latest copy of the log with my filter values in place.

There is one dialog box in the app where you enter in / modify your settings and filters. This dialog box has three tabs:

[ol][li]File I/O Setup: This is where you tell ELVira all about where the log file that want to get (how to get it, where your VERA is, etc.), and where you want it to go on your PC once its transferred.[/li]
[li]Pre-Import Filters: This is where you tell ELVira what log records wou want filtered out before importing any of the data into Excel.[/li]
[li]Post-Import Filters: This is where you tell ELVira how you want your log to be filtered once it’s in Excel. After the file is imported, you can change this at any time and reapply the filter at will.[/li][/ol]

[hr]

File I/O Set-up (See attached screenshot)

[ul][li]Log File Source: Choose weather you’ll be importing from VERA automatically, or if you want ELVira to prompt you for a local log file on your PC to import. If you choose to prompt for a file, no additional set-up is needed.[/li]
[li]PSCP Location: Browse to the location where your copy of PSCP.EXE is stored.[/li]
[li]VERA Server: Enter in the IP address, port, SSH user name and password. If you check-off “do not save password…” then whenever you save the excel workbook, the password will be erased so that it is not stored in the excel file.[/li]
[li]Files: Enter in the path (default provided) for the VERA log file to import, and browse to the folder where you want the log files downloaded to on your PC. You can opt to overwrite the local PC log file every time you import it, or have ELVira create new log files with a time-stamp every time you request a download.[/li][/ul]

[hr]

Pre-Import Filters (see attached screenshot)

[ul][li]Filter Options: Quick way to indicate you want no per-processing done, just get the raw log file and import it into Excel. If you choose No filtering, there are no other applicable options on this tab.[/li]
[li]Time Based Filter: You can elect one of 3 time based filters. Pretty Self explanatory, but you can specify to retrieve only the last X minutes of the log, retrieve only the entries since the last start-up, or retrieve entries starting with the last X minutes prior to the most recent crash. Any records not meeting the time filter are automatically filtered out, before any additional filters (below) are applied.[/li]
[li]Strip out non-conforming entries: A “conforming entry” to ELVira is a log line that includes a log level number, a timestamp, and then the log entry. You typically see “non-conforming” entries during start-ups and crashes to document the boot process. Checking this box will remove these non-conforming entries. Note, the conforming log entry that indicates there was a crash / restart does not itself get removed.[/li]
[li]Strip out escape (color) codes: If you prefer not to see the log coloring in Excel, you can strip out the color codes here. This way you won’t see the colors, nor will you see the escape character sequence. [/li]
[li]Only include entries for device(s): You can provide a comma delimited list of vera device numbers, and if you enable this filter, all log entries that don’t relate to hose device numbers will be eliminated. [/li]
[li]Only include entries for specific log levels: You can select one or more log levels. If you enable this filter, all records that don’t match one of the selected log levels are eliminated. Note: unless you enable verbose logging on your vera unit, the log will only contain entries for levels 1-9 and 50. [/li][/ul]

[hr]

Post-Import Filters (see screenshot)

The Post-Import filters largely mirror the same options as the pre-import filters. The difference is when the filter is applied. The pre-import filters are applied prior to the log file data being imported into Excel. the Post-Import filters apply the log that is already imported into Excel.

In addition to the filter settings themselves, you can elect whether or not you want the post-import filter to be applied automatically every time a new log is imported.

#3

Waiting to see the app icon for this one. :wink:

#4

;D

#5

Getting a runtime error 9
SubScript out of range when trying to retrieve the logs

**** Found a random . entered with the IP address of the device ****

#6

Just to be clear…you got it working after correcting the IP address, right?

Like I said earlier, I haven’t yet put in the effort to validate input. :slight_smile: I’ll get around to it at some point. Any feedback is appreciated.

#7

Getting a runtime error: 13 “Type mismatch” now when trying to apply filters. (reapply) after refresh. This also has issue pulling logs when logging is in verbose mode.

#8

Hi wezley69,

When I get some time I’ll add some more verbose error trapping so I can see what’s going on. I am able to pull in verbose logs myself, so I’d be curious to know what’s going on.

#9

Nice utility. Thx
It is now scary to see how many “red” I have in my log… I guess my system is not as “healthy” as I thought :wink:
Claude

#10

Nice initiative JoeyD! I’ll be following this thread and try out your creation when I find some time…

#11

Thanks for your work!
Tried it and works well.

#12

Thank you for this very handy tool to quickly analyze the log file.

#13

Thanks all for your comments. Since this appears to now be getting some use I’ll strive to make a few updates over the next few days:

  1. Proper data entry validation
  2. Allow you so save “profiles” for the File Transfer set-up and for the filters (so you can quickly recall your favorite settings)
  3. Include some “default” type options for the log level filters.
  4. Splash-screen with a logo, just for BOFH. :slight_smile:

Although with Valentine’s day approaching…that’s going to be one of those days that I really do owe my wife some quality time. :slight_smile:

#14

@JoeyD - would it be easy to make this work on a Mac if i scp the log files from vera to the mac manually? Thanks !

#15

Yes, if you restrict use to the “prompt for file on local PC” option from the Tile Transfer Setup menu, then it should not be too hard for me to get this to work on the Mac. (Primarily, I think I just need to account for the difference in file system delimiters when grabbing files.)

Having said that, am I correct that Macs have built-in functionality to SCP (and so don’t need a helper program like PSCP.EXE?). If that’s the case then at some point I would like to integrate support for that as well…although since I don’t have a mac to test it could be difficult.

#16

Validating input has got to be the most painful part of programming. Even where I work the programmers seem to miss it from time to time.

Sent from my iPad using Tapatalk

#17

Yep, “prompt for file” would work fine.

Having said that, am I correct that Macs have built-in functionality to SCP (and so don't need a helper program like PSCP.EXE?). If that's the case then at some point I would like to integrate support for that as well.

Yep, Mac is a Unix OS so scp is built in.

I don't have a mac to test it could be difficult

Happy to help test any time. Thanks !

#18

@JoeyD

Do you know if anyone has gotten this to work on Mac yet?

Thanks,

-e

#19

My logs are on the USB drive I attached. The default location doesn’t seem to work with my Edge. “Path not found”

#20

Have a look at the second post at I/O errors.