Transmitted FibaroHC2 event data in Excel sheets

[[email protected] 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!

[HansS 3]

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!

[[email protected] 0]

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

[HansS 3]

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" />

[erikhaverkamp 0]

@HansS where did you find the ?last=10&type=id part in the API documentation? I only see the api/event call without parameters??

 

Thanks,

 

Erik

[cristimv 2]

@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

[Alex 31]

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

[morpheus75 76]

(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

[Fuchs86 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