Vera - Smarter Home Control Forum

Advanced => Programming => General => Topic started by: JoeyD on January 25, 2015, 05:30:40 pm

Title: ELVira: Excel Log Viewer for Vera
Post by: JoeyD on January 25, 2015, 05:30:40 pm
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:

Setup:

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.


Title: Re: ELVira: Excel Log Viewer for Vera
Post by: JoeyD on January 25, 2015, 05:31:37 pm
ELVira Workflow and Settings

The idea behind ELVira is basically to accommodate this workflow:

            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:




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



Pre-Import Filters (see attached screenshot)



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.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: BOFH on January 25, 2015, 07:38:39 pm
Waiting to see the app icon for this one. ;)
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: JoeyD on January 25, 2015, 08:27:22 pm
Waiting to see the app icon for this one. ;)

 ;D
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: wezley69 on February 01, 2015, 03:25:22 pm
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  ****
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: JoeyD on February 01, 2015, 04:09:30 pm
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.  :)  I'll get around to it at some point.  Any feedback is appreciated.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: wezley69 on February 10, 2015, 07:27:52 am
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.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: JoeyD on February 10, 2015, 07:56:29 am
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.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: fouclo62 on February 10, 2015, 09:03:12 am
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  ;)
Claude
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: korttoma on February 12, 2015, 06:29:26 am
Nice initiative JoeyD! I'll be following this thread and try out your creation when I find some time...
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: C4Vette on February 12, 2015, 11:51:35 am
Thanks for your work!
Tried it and works well.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: ranneman on February 12, 2015, 01:00:20 pm
Thank you for this very handy tool to quickly analyze the log file.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: JoeyD on February 12, 2015, 01:19:29 pm
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. :)

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. :)
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: mda on February 12, 2015, 01:46:00 pm
@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 !
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: JoeyD on February 12, 2015, 02:02:31 pm
@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 !

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.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Grwebster on February 12, 2015, 06:02:34 pm
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
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: mda on February 12, 2015, 10:42:34 pm


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.

Yep, "prompt for file" would work fine.

Quote
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.

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

 Happy to help test any time. Thanks !
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: emyrold on May 24, 2015, 11:18:36 am
@JoeyD

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

Thanks,

-e
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: wezley69 on May 27, 2015, 07:40:16 pm
My logs are on the USB drive I attached.  The default location doesn't seem to work with my Edge.  "Path not found"
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Brientim on May 28, 2015, 10:07:19 am
Have a look at the second post at I/O errors.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: wezley69 on May 28, 2015, 05:08:11 pm
I must be dense.  I still don't see what you are trying to refer to.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Brientim on May 28, 2015, 05:11:04 pm
I must be dense.  I still don't see what you are trying to refer to.

You need to establish the setup as per the design as detailed below, the default if they are not working will need to be changed to match your deployment.

File I/O Set-up (See attached screenshot)
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.
PSCP Location: Browse to the location where your copy of PSCP.EXE is stored.
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.
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.

Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on May 28, 2015, 06:15:43 pm
Add me to the dense list. I get a run time error 9 out of range. Something about an array not defined.

I have quadrupled checked my setup, got the files in the same spot, checked IP/port/password(root), and I know how to ssh into the Vera. When I run it I see the pscp come up and just stays on my screen for a long time and does nothing. If I x out of it I get the run time error. There is nothing in the temp text file that is created.

What other info can I give you?
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Brientim on May 28, 2015, 06:18:34 pm
Add me to the dense list. I get a run time error 9 out of range. Something about an array not defined.

I have quadrupled checked my setup, got the files in the same spot, checked IP/port/password(root), and I know how to ssh into the Vera. When I run it I see the pscp come up and just stays on my screen for a long time and does nothing. If I x out of it I get the run time error. There is nothing in the temp text file that is created.

What other info can I give you?
Never call you "dense", that would not be my way.... I will fire up a windows session and go through the setup. If you have errors, screen captures of configuration and error reports may assist in narrowing in on the root cause.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on May 28, 2015, 06:23:55 pm
Add me to the dense list. I get a run time error 9 out of range. Something about an array not defined.

I have quadrupled checked my setup, got the files in the same spot, checked IP/port/password(root), and I know how to ssh into the Vera. When I run it I see the pscp come up and just stays on my screen for a long time and does nothing. If I x out of it I get the run time error. There is nothing in the temp text file that is created.

What other info can I give you?
Never call you "dense", that would not be my way.... I will fire up a windows session and go through the setup. If you have errors, screen captures of configuration and error reports may assist in narrowing in on the root cause.

LOL, thanks. Screenshots attached.

Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Brientim on May 28, 2015, 06:24:33 pm
I have just download, and configured as per the first few post and encountered no errors.
The configuration I used is attached
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on May 28, 2015, 06:27:01 pm
The error message..
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on May 28, 2015, 06:28:12 pm
LOL!!!! Port 22 I'm calling myself dumb. I thought it was 3480. Jeez......works now. :(
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Brientim on May 28, 2015, 06:28:24 pm
Looking quickly at the screen capture PSCP path = does that contain the file or is it contained the the same folder where you downloaded putty? By default it should be with putty. And, the blank screen indicates it is not finding it
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on May 28, 2015, 06:29:39 pm
Looking quickly at the screen capture PSCP path = does that contain the file or is it contained the the same folder where you downloaded putty? By default it should be with putty. And, the blank screen indicates it is not finding it

Thanks, see my post above. Port 22, not sure why I was fixated on 3480.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Brientim on May 28, 2015, 06:30:16 pm
Did not even notice the port error. I never said I would not call you that - lol.... Easy mistakes.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on May 28, 2015, 06:42:03 pm
Did not even notice the port error. I never said I would not call you that - lol.... Easy mistakes.
Lol, thanks. Sometimes I have limited tech play time so I was rushing too. Thanks again!
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Brientim on May 28, 2015, 07:13:24 pm
I must be dense.  I still don't see what you are trying to refer to.

If you can do the same as tomtcom when time permits and put screen grab of configuration and errors, we could hopefully work through your issues as well... Now that tomtcom has had his tech time and full bottle for the setup, no doubt he could assist you too...

Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on May 28, 2015, 08:20:30 pm
@JoeyD - A lot of times we forget our device id's and I know the excel can filter on those id's but what I pasted below is from a post on this forum that would convert the friendly name of the devices. Any way to insert/convert the device id using the information below into the excel or pre-populate the filter to pick devices rather than device ID's?

Full Device ID List
In the VERA UI go to the APPS tab
Click the Develop Apps tab
Then click 'Test LUUP code (LUA)' in the left-hand menu
Copy the LUA code (below) and paste it into the Code window
Click the blue 'GO' button to make the code run
View your device list at - http://"your Vera IP address"/devlist.txt

local file = io.open("/www/devlist.txt", "w")

file:write("[DeviceNo / id] device names on " .. os.date() .. "\n")

for deviceNo,d in pairs(luup.devices) do

if d.id ~= "" then

file:write(string.format('[%03d / %s] %s \n', deviceNo, d.id, d.description))

end

end

file:close()
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: Brientim on May 29, 2015, 02:05:36 am
@JoeyD - A lot of times we forget our device id's and I know the excel can filter on those id's but what I pasted below is from a post on this forum that would convert the friendly name of the devices. Any way to insert/convert the device id using the information below into the excel or pre-populate the filter to pick devices rather than device ID's?


@JoeyD has not been on the forum for around 2 months now.
Create a scene and run a cyclic schedule or that will always be available or place it is start up lua.



Title: Re: ELVira: Excel Log Viewer for Vera
Post by: clippermiami on May 29, 2015, 07:55:54 am
I finally got around to trying ELVira. Once I got the kinks out and the correct configuration it works just fine. Thanks for a useful log tool.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on May 29, 2015, 08:36:29 am
@JoeyD - A lot of times we forget our device id's and I know the excel can filter on those id's but what I pasted below is from a post on this forum that would convert the friendly name of the devices. Any way to insert/convert the device id using the information below into the excel or pre-populate the filter to pick devices rather than device ID's?


@JoeyD has not been on the forum for around 2 months now.
Create a scene and run a cyclic schedule or that will always be available or place it is start up lua.
Yeah I noticed, we were last working on the MyQ project.

Ok, was just throwing out enhancement ideas, he was always game for that.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: fhreid on August 01, 2015, 06:12:33 am
JoeyD.  This is an awesome Tool.  So simple but a big time saver.

Props!
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: rmroth on February 03, 2016, 06:50:11 pm
Hello,
Is your UI still available. I can't seem to find and/or download the attachment, every time I click on it it just takes me back to original of your post..
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: mrwildbob on June 27, 2016, 11:31:19 pm
Where can I download ELVira?  Can't seem to find it.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: korttoma on June 27, 2016, 11:54:48 pm
The ELVira.zip is downloadable from the first post -> http://forum.micasaverde.com/index.php/topic,30265.0.html
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: mrwildbob on June 28, 2016, 12:47:41 am
oops...I didn't see the file hiding there.  thanks
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: pit on February 23, 2017, 12:46:38 pm
this tool is easy to install and super helpful !!!!
thank you!
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on February 23, 2017, 08:19:14 pm
this tool is easy to install and super helpful !!!!
thank you!

LOL, I literally have it opened right now. :) Although JoeyD has left this forum, I try to steer others to this log viewer. It's definitely used a lot.
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tinman on October 18, 2017, 11:07:10 am
I've been using this tool for quite a while and I've recommended it often. Today I'm trying to track down a problem that's shown up after installing the latest firmware (1.7.3232) on my Vera Plus. It looks like the ELVira tool is ignoring (skipping) over most of the log file. The raw log has more than 75k lines but the log in Excel only has 1686 lines. I've disabled all pre and post import filtering. The workbook macros are of course password protected so I can't try to find out what the problem is. Has anyone else seen this kind of thing?
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tinman on February 13, 2018, 03:20:22 pm
It looks like some kind of default is in effect. I had pre-import filtering set to "No pre-import filtering...just get the raw file". But it was only including entries beginning with the last luup startup. Once I enabled pre-import filtering and selected "No time based filter (process entire log file" and then retrieved the log it worked as expected. Then I could go back and select "No pre-import filtering...just get the raw file".
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: casm on August 01, 2018, 01:06:16 pm
Hello

Sorry, I am newbee in this systems. I made all the settings well but after download the log file when i clixk on Press ok to continue to process entire log, the excel give me the error program not responding. Can someone help me?

Thanks in advance

Carlos
Title: Re: ELVira: Excel Log Viewer for Vera
Post by: tomtcom on August 01, 2018, 04:09:04 pm
I think you need to make sure you have 4.1 .net framework for will down which is old. I can't recall but it is in the main thread for requirements. That might be the issue.

Did you install pscp and putty from the directions also?

Sent from my VS995 using Tapatalk