Is there a way to download the forwarding logs as a .csv file via the command line using RTL?

[clarketowson]

Hi Everyone,

I have recently been doing some work with Grafana and have created a dashboard based on my nodes forwarding logs.

The CSV file that can be downloaded from RTL is very useful for this.

Is there a way to download the forwarding logs as a .csv file via the command line using RTL or if not how exactly does RTL interact with the underlying c-lightning in order to create the CSV file?

Best Regards,

Clarke

[ShahanaFarooqui]

@clarketowson

RTL cannot be used directly to convert CLN response into .csv without going to the UI.

Steps RTL takes to create .csv:
1: RTL sends the REST request to clnrest's listforwards
2: It receives json response back from above request.
3: RTL backend sends the data back to the UI.
4: UI consumes this json data and converts it into a downloadable .csv file.

I have not used Grafana personally but ChatGPT confirms that it can directly consume json response also. In that case, you can remove RTL from the stack and directly communicate with Core Lightning via clnrest. You can also try clnrest's requests from CLN demo node here.

[clarketowson]

Thank you @ShahanaFarooqui for the detailed response on this very much appreciated.

May I ask you what the specific RTL REST request to clrests listforwards is so I can make a bash script to do the exact same thing to create the JSON data file which I will then convert to a CSV file via the command line so I can automate my forwards data and insert it into my MySQL database directly from the command line?

I want to make sure the CSV file is consistent with the one which is generated via RTL.

With very best regards,

Clarke

[ShahanaFarooqui]

@clarketowson Not sure if I understood you correctly, but I am just calling simple listforwards directly.

So if you check the request url in RTL, it is a simple POST listforwards REST call to get the data.

Then RTL filters out all records except latest 1,000 if the forwarding status was failed or local_failed.

In curl, the request should look something like:

curl -k -X 'POST' 'https://localhost:3001/v1/listforwards' -H 'accept: application/json' -H 'Rune: <cln-node-rune>' -H 'Content-Type: application/json' -d '{}'

Where you can adjust https://localhost:3001 url and headers according to your clnrest configurations.

[clarketowson]

Thanks @ShahanaFarooqui

I have got used to working with the bitcoin-lightning-cli command as I run RTL in a docker container with BTCPay.

As we know the bitcoin-lightning-cli.sh listforwards command gives JSON output like this:

{
"forwards": [
{
"in_channel": "828658x1532x0",
"in_htlc_id": 10,
"out_channel": "827986x1218x0",
"out_htlc_id": 8,
"in_msat": 50016000,
"out_msat": 50014500,
"fee_msat": 1500,
"status": "settled",
"style": "tlv",
"received_time": 1707126362.387,
"resolved_time": 1707126368.737
},

But does not include the in_channel_alias and out_channel_alias.

Basically i'm looking to create a bash script to give:

{
"forwards": [
{
"in_channel_alias": "$nodeAliasA",
"in_htlc_id": 10,
"out_channel_alias": "$nodeAliasB",
"out_htlc_id": 8,
"in_msat": 50016000,
"out_msat": 50014500,
"fee_msat": 1500,
"status": "settled",
"style": "tlv",
"received_time": 1707126362.387,
"resolved_time": 1707126368.737
},

where $nodeAliasA and $nodeAliasB are the aliases of the nodes involved in the forwarding transactions.

then I could create a CSV file from the JSON combined data like:

828836x514x1 | 11432 | 811606x1334x0 | 3135 | 80002680 | 80000880 | 1800 | settled | 1707454330 | 1707454338 | Taeping | ⚡AdicaMinibolt⚡

which then matches the same format/layout of the CSV file generated by RTL. Then I could create a bash script to call MariaDB and insert those forwarding details into a separate database I have setup which i'm using Grafana to pull the forwarding details from. I populated the separate database manually by importing the CSV file from RTL and I keep it up to date manually but using a cron script would automated this.

I'm running RTL from within a docker container on a BTCPay Server. But i'm running all my scripts on the server host outside of the docker containers. Being able to add it as a simple cron job on the host would be ideal.

I have been chatting with the admin of the lightningnetwork+ website and he has expressed interest in the idea of node operators on his site being able to display forwarding logs like I have. I'm still fine tuning the dashboard at the moment:

https://grafana.intjbilling.com/d/ba0e94bc-3b0c-465f-bab6-fc640da6997b/chillycalendar-node-statistics?orgId=1

Best Regards,

Clarke

[ShahanaFarooqui]

To map forwarding channel ids with their aliases, I am using already stored channels data in RTL.

For script, you might want to fetch all channels first then map them with these channel ids.