Jump to content

Welcome to Smart Home Forum by FIBARO

Dear Guest,

 

as you can notice parts of Smart Home Forum by FIBARO is not available for you. You have to register in order to view all content and post in our community. Don't worry! Registration is a simple free process that requires minimal information for you to sign up. Become a part of of Smart Home Forum by FIBARO by creating an account.

 

As a member you can:

  •     Start new topics and reply to others
  •     Follow topics and users to get email updates
  •     Get your own profile page and make new friends
  •     Send personal messages
  •     ... and learn a lot about our system!

 

Regards,

Smart Home Forum by FIBARO Team


  • 0

Transmitted FibaroHC2 event data in Excel sheets


h.fuckar@aon.at

Question

(Vienna, 28.10.2015)

 

On YouTube (recommend): "

Please login or register to see this link.

"

and Download (zip): "

Please login or register to see this link.

"

 

This is my way to transmit the fibaro-event-data in Excel spreadsheets.

 

Since I am a FibaroHC2-newbie, I have to read a lot of forum posts first:

Please login or register to see this link.

(english)  

Please login or register to see this link.

(german)
And the descriptions in the developer documentation are an important tool:

Please login or register to see this link.

(english)
The forum members @petergebruers (expert of forum.fibaro.com) and @crissxcross (moderator of siio.de/board) told me several times friendly the right way.

 

I have found in Fibaro HC2 system is no way to write the event data in an external file.

One option was to send the data via email to me.
But this grief I did not want to do to the Fibaro server.

Therefore, I use as a temporary store global variables.
The global variables of Fibaro system are very powerful.
In the global variable "selEventData" I have saved 1,024 kilobytes of data for testing purposes.
These were 20,749 records. With this value (20,000 event-records), I have limited my system.
Where the real limits lie, everyone can try out for themselves.

After the selection, so the data is available in the local variable "selBasicData" (sections, rooms and devices) and "selEventData" (events) are available.
A local variable can be exported with the "HC2 Toolkit program" in a plain text file. Click the right mouse button on the line with the desired variables and select "Export".
Remove the unnecessary parts at the beginning and end of the file and give it the extension ".csv".
Now the data can be easily imported into an Excel spreadsheet and processed further.

 

This is perhaps not the best solution.

But for me the development was interesting and I learned a lot about the FibaroHC2-system.
I wish you also so much fun with your Fibaro system!

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

It's always good to learn something.

 

I'm not sure though, why you would want an Excel with the events? Just curious.

 

A tip would be that the event data is readily available, without setting a global variable. At this URL: 

Please login or register to see this link.

IP>/api/panels/event?last=10&type=id. This actually show the last 10 events, but you can get more if you want. Documentation for this feature is located at developer.fibaro.com. I bet you can import that into Excel directly, without an intermediate export tool (just a hunch, haven't really done that myself). Anyway, thanks for sharing, and enjoy!

Link to comment
Share on other sites

  • 0
  • Inquirer
  • @HansS is right. The solution is complicated. I did not see the forest for the trees. The solution, the @HansS describes outputs a JSON table. I'll try the event data directly from Excel (Visual Basic Macro) to retrieve a HTTP request. But the creation of this first version in LUA was not meaningless for me. I have learned a lot about the Fibaro system and LUA scenes.
    And for you it was an example of how not to do it

    Please login or register to see this image.

    /emoticons/default_icon_wink.gif" alt=";-)" />

    Why Excel? Calculations, Graphical statistics, pivot tables, etc.

    Link to comment
    Share on other sites

    • 0

    Oh, pivot tables. Draaitabellen? They are magical

    Please login or register to see this image.

    /emoticons/default_wink.png" alt=";)" srcset="https://forum.fibaro.com/uploads/emoticons/[email protected] 2x" width="20" height="20" />

    Link to comment
    Share on other sites

    • 0

    @HansS: many thanks for the hint. It really worked...in couple of second I was able to get the last 100.000 entries.

    One question, sorry I'm still discovering things...How should I interpret the timestamp? A number like "timestamp":1453829941 doesn;t look quite friendly.

    Thanks

    Link to comment
    Share on other sites

    • 0

    @HansS: many thanks for the hint. It really worked...in couple of second I was able to get the last 100.000 entries.

    One question, sorry I'm still discovering things...How should I interpret the timestamp? A number like "timestamp":1453829941 doesn;t look quite friendly.

    Thanks

    This is the amount of seconds since 1.1.1970.

    To convert this number:

    Please login or register to see this code.

    Link to comment
    Share on other sites

    • 0

    (Vienna, 28.10.2015)

     

    On YouTube (recommend): "

    Please login or register to see this link.

    "

    and Download (zip): "

    Please login or register to see this link.

    "

     

    This is my way to transmit the fibaro-event-data in Excel spreadsheets.

     

    Since I am a FibaroHC2-newbie, I have to read a lot of forum posts first:

    Please login or register to see this link.

    (english)  

    Please login or register to see this link.

    (german)

    And the descriptions in the developer documentation are an important tool:

    Please login or register to see this link.

    (english)

    The forum members @petergebruers (expert of forum.fibaro.com) and @crissxcross (moderator of siio.de/board) told me several times friendly the right way.

     

    I have found in Fibaro HC2 system is no way to write the event data in an external file.

    One option was to send the data via email to me.

    But this grief I did not want to do to the Fibaro server.

    Therefore, I use as a temporary store global variables.

    The global variables of Fibaro system are very powerful.

    In the global variable "selEventData" I have saved 1,024 kilobytes of data for testing purposes.

    These were 20,749 records. With this value (20,000 event-records), I have limited my system.

    Where the real limits lie, everyone can try out for themselves.

    After the selection, so the data is available in the local variable "selBasicData" (sections, rooms and devices) and "selEventData" (events) are available.

    A local variable can be exported with the "HC2 Toolkit program" in a plain text file. Click the right mouse button on the line with the desired variables and select "Export".

    Remove the unnecessary parts at the beginning and end of the file and give it the extension ".csv".

    Now the data can be easily imported into an Excel spreadsheet and processed further.

     

    This is perhaps not the best solution.

    But for me the development was interesting and I learned a lot about the FibaroHC2-system.

    I wish you also so much fun with your Fibaro system!

     

    hello

    excellent work. i have managed to get this working first time but after i get my first set of data the VD doesnt update the labels properly at all?

    eg

    when i change the device ID or roomID or sectionID the labels do not update.

    this also happens with the time selection ie To and From times i cannot see them change?

     

    however they do update within the global variables themselves as i pressed the button and refreshed the data in the toolkit and i saw the values being updated.

     

    so is there some issue with my HC2 or browser because the labels are not being updated?

    i have tried on chrome and safari, i also used the fibaro app on my phone and it still didnt update the label texts?

    thanks

    Link to comment
    Share on other sites

    • 0
    On 10/28/2015 at 10:48 PM, HansS said:

    It's always good to learn something.

     

    I'm not sure though, why you would want an Excel with the events? Just curious.

     

    A tip would be that the event data is readily available, without setting a global variable. At this URL: 

    Please login or register to see this link.

    IP>/api/panels/event?last=10&type=id. This actually show the last 10 events, but you can get more if you want. Documentation for this feature is located at developer.fibaro.com. I bet you can import that into Excel directly, without an intermediate export tool (just a hunch, haven't really done that myself). Anyway, thanks for sharing, and enjoy!

    Hi,

    how can i see the log for only one device Id?

    thanks

     

    Chris

    Link to comment
    Share on other sites

    Join the conversation

    You can post now and register later. If you have an account, sign in now to post with your account.

    Guest
    Answer this question...

    ×   Pasted as rich text.   Paste as plain text instead

      Only 75 emoji are allowed.

    ×   Your link has been automatically embedded.   Display as a link instead

    ×   Your previous content has been restored.   Clear editor

    ×   You cannot paste images directly. Upload or insert images from URL.

    ×
    ×
    • Create New...