Cribl: Send Data to Google Sheets

 

What

This is the second of a series of Posts where I want to show some Private and/or  "Smart Home" use cases that involve Cribl.

Cribl (Stream) is a Log/Data Streaming Platform that allows you to route, enrich, reduce (and more) the data that you send through it. Check cribl.io, their Docs, University and Community for more details. 

Here's the first Post, about Slack Notifications.

What Exactly

For some of my Use Cases / Data sources I was looking for a free longterm storage solution. Not for big data volumes - basically for summary statistics. In this case the daily Energy Consumption and Production (Photovoltaic) in/at my house. 

I don't have Splunk (or similar) 24/7 running in the Cloud or OnPrem. For midterm Analysis and Visualization I use Humio / Crowdstrike Falcon Logscale, but it only has a retention of 7 days.

Than I thought about Google Sheets. It was one of my first Cloud services and I still use it for some of my private statistics. I have sheets in my account from 2006 and they are still in good shape :) Talking about longterm...

In the past I tried to write to Google Sheets for another use case via their API and failed due to me not in the mood (or lag of skills) for the process of registering and handling the accounts, tokens and stuff around "officially" using the API.

Coincedentally I found a hassle-less way of writing to Google Sheets by (miss-)using Google Forms as an Interface

How

1. Create a Google Forms questionaire with just „Text“-type answers.
2. Find out the Field IDs of the answer inputs. (See hint below)
3. Use them (the IDs) to build the URL in your Pipeline.
4. Send „faked Form replies“ as Webhook to Google Forms.
   => Forms creates a Google Sheet with all „responses“.

Details for 1-3 in this Tutorial. Thanks to Johnmu for this idea and write up:

https://johnmu.com/using-curl-to-add-rows-to-google/

(Step 4 is done in Cribl, not with Curl.)

Hint for Step 2 (Field IDs):
Go to the "Live view" of your Forms, open Developer Tools and search in there for "entry." [string "entry" + dot "." ]

Somewhere in there will be a list of entry.somenumber things. We will need those IDs later.

The sort order is the same as your "Questions" in the form:

  => My field ID for "gridconsumed_kWh" is "entry.709194751"

Hint for Step 4 (Cribl Part of things):

Summarised you need to have 4 things in Cribl:

1. Source
1. The data that you need to send to Google Sheets needs to come from somewhere, right?
   It's up to you/based on the use case.  Note that it's not meant to store 5 GB of XML WinEvent Logs per day. More for summary stats like described here...
2. Extract/Create 1 field per "Forms Response" created earlier.
2. Route
1. A simple Route pointing the data from the Source to the Destination via the Pipeline
3. Pipeline
1. You need to parse/extract the data that you want to send and also eval some fields to construct the final URL
4. Destination
1. A simple, almost empty Webhook Destination

I don't explain 1 and 2 any further, as it's really based on what you have. 
For 3 (Pipeline) the first part is again very use case based. For the second part, to build the Webhook URL an example:

The field __url will be used by the Webhook Destination to make the call (= overwrite whatever is configured as Webhook URL). So everything needs to end up in it.
As it didn't fit in the Screenshot, here is the full __url definition of my example:

'https://docs.google.com/forms/d/e/'+formsID+'/formResponse?ifq&entry.1176554563='+tach+'&entry.709194751='+gridconsumed_kWh+'&entry.2103966350='+produced_kWh+'&entry.1062163135='+eingespeist+'&submit=Submit'

The formsID is part of the URL path and also each of the fields and their values.

As you can see I received the value for "gridconsumed:_kWh" from the JSON structure of my Source and evaled it to a field. In the __url I use this field to set the corresponding "forms response id". As we learned earlier, the field/response has the ID "709194751"

=> '&entry.709194751='+gridconsumed_kWh

For 4 (Destination), there is nothing special needed. As mentioned earlier, the URL you configure here is not relevant as it will be overwritten with the internal __url field that we created in the Pipeline.

That's how my Webhook Destination looks like:

Each Event that goes to the Webhook creates one new line in Google Sheets:

If you just need simple Charts, you can do it directly in GSheets. No need for Big Data tools, if you have Smal Data :)

Conclusion

• With this setup you can store low volume/long retention time data to Google Sheets
• No need for Google API business.
  
• Not meant to be a big data storage, just a free and (once setup) simple way to store Statistics/Summaries.
  
• Feel free to comment with how you utilized this setup.
  

References/Links

• Record of Cribl User Group Meeting Oct. 2023 where I explain this (and some other) use case. 
  https://www.youtube.com/watch?v=IopYZ2JbUeE&t=1548s
• Cribl Docs: Webhook Destination