DDNS customer app

Get results if a port is open from a list of DDNS on your Google sheet.

Attach DDNS to a client or company.
Select what clients are allowed to see in the app. Like tabs, ports, other DDNS from company.

Admin can view everything.
users can only see what’s there.
see ext can only see voip tab.

This video dives in on how the sheet works. including some script (not shown in video) after we understand the sheet process we dive into the app.

If your in a company you can send DDNS instructions to a user withen your company. (works well with outlook) I have sean issues with other mail apps.
Admin can send email instructions to anyone.

Users can see there job sites (DDNS) in a map view. admin can see all jobs on the map.

Currently only the owner of the sheet can import Voip info and choice what users can see.

If the provisioning tab is allowed. a user can request a new auto provision for a voip phone.

A admin can Manage and choice what other users can see in the Admin Panel, as well adding Items to the DB.

Admin can add clients directly from the app.

A user can open a ticket or quote. a user will only see there own quote they listed. admin can see all customer qoute, and change status of the quote, and even send reminders to a user.

if a DDNS has a address EG 1234SomthingST.ddna.net the script will put 1234 SomethingSt in the address field automatically. to overwrite that click manual next to address.

You will need to get approved. to view the app.

UPDATE Now You can approve yourself very easily and change the view as a admin or user for app testing.

Please Please use FAKE info at signup while testing our app. DO NOT SHARE ANY REAL PASSWORD TO ANY DDNS.

Future plans for the app.

  1. Plan is to get the DDNS port checker to work live on the test app. So you guys can test this live.
  2. clean up Datebase
  3. Fix some minor issues.
  4. Allow user to change company name from a list while testing the app.
  5. Mager bug to look into: when a user signs up there info goes in the bottom of the Google sheet list instead of next line.
  6. Fix buttons in testing app (by cleaning up CSS)

https://DDNSAPP.glideapp.io/

1 Like

@ThinhDinh @Darren_Murphy what do you think of the app? Think i am going to sell it in the store, Or not ready yet?

From a quick look, that’s a lot of CSS inside the app isn’t it. CSS-heavy app won’t get approved as far as I aware.

3 Likes

If the welcome page is css and i remove all other css Will it be approved? or do i need to remove all css?

To Do: Cleanup Sheet and in app> Explain more in details how the app works. Show more examples.

cleanup cleanup.

Thinking of removing voip, provision tab. As part of public Cleanup.

Nice idea, Months ago I had a similar idea but you took this to another level. :upside_down_face:

What I’d put to make it more powerful and interesting is (based on my personal idea):

  1. Create a Map View in order to monitor your NVR/DVR/routers status and know if they are ON or OFF (or at least, if they have internet connection).
    You can create a script by using this good code to know their status at anytime
    Ping a Google Spreadsheet of urls. Used in http://mashe.hawksey.info/2011/08/open-data-equals-open-scrutiny-but-doesnt-always-equal-all-of-the-answer/ · GitHub .

As you should know, you can’t work or receive ICMP Ping status using GAS but you can handle/use HTTP status codes instead. In this way, you might have something like I have on my IIoT APP

  1. Try to view the cameras video (realtime) into APP using Web View component:
    It’s a little more complicated but I think it is possible. Of course, the DVR/NVR brand is important and can help or not. I think HIKVision o Dahua devices can help to get this.
    Most brands use an ActiveX control to get a realtime video on web browsers so, you need to create a standard/template web page, insert the ActiveX control associated to device brand and pass/send parameters dynamically to get the connection and video.

I made this years ago using IP cameras along with software supporting VBA (ActiveX containers) and worked fine but I have never tried it using my own web web page. If you are able or dare to do it, I could help you, no problems.

Saludos @abe.sherman, don’t give up!

Diffidently going to look into this more in the upcoming weeks. also believe i tried gas. well at least a (script that pings IP in the sheet) but the issue was timeouts. and lag. Any idea of a workaround? The PI as you can see is a big detail of awesome. but since not everyone has Pi or Linux system. Maybe i will look for alternative with the reference you shared in the near future.

The pi uses a very powerful tool NMAP. Its a life saver. I will share the code here.

Credit for the script is XSE from a discord user.

#!/usr/bin/env node

/*
  TODO: 
   -test case if scan timeout
    (no ports, iirc nothing to tell it timeouted)

  NOTES:
    -update is performed twice, first with IPs and clear format, 2nd with notes and background colors
*/

//'use strict';
const dns = require('dns');
const nmap = require('libnmap');
const { GoogleSpreadsheet } = require('google-spreadsheet');

// dev dep
const fs = require('fs'); // devdep: save reports for analyse

const creds = require('./NOIP-Sheet-tmp.json'); // 6
const doc = new GoogleSpreadsheet(' YOUR SHEET ID'); // 
const ROW_OFFSET = 4;  //From Top

const headers = ['ddns', 'ip', 'http', 'https', 'rtsp', 'server', 'router']; 
const ipv4 = /^(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$/;
const ipv6 = /^[a-fA-F0-9]{1, 4}\:[a-fA-F0-9]{1, 4}\:[a-fA-F0-9]{1, 4}\:[a-fA-F0-9]{1, 4}\:[a-fA-F0-9]{1, 4}\:[a-fA-F0-9]{1, 4}\:[a-fA-F0-9]{1, 4}\:[a-fA-F0-9]{1, 4}$/;
const isIP = ip => (ip.match(ipv4) || ip.match(ipv6)) ? true : false;

const isUp = rdata => (rdata.runstats[0].hosts[0].item.up == 1) ? true : false;
const port = (rdata, port) => {
  if(!rdata.host[0].ports || !rdata.host[0].ports[0].port.map(e => e.item.portid).some(p => p == port))
    return null; // timeout
  const find = rdata.host[0].ports[0].port.filter(e => e.item.portid == port)[0];
  return {
    protocol: find.item.protocol,
    portid: find.item.portid,
    state: find.state[0].item,
    service: find.service[0].item
  };
};

// v4 wants an RGB(A) obj with values in a 0-1 range, totally discarding alpha here
const hex2RGBA = hexCode => {
  const hex = hexCode.replace('#', '');
  if(hex.length === 3)
    hex += hex
  return {red: parseInt(hex.substring(0, 2), 16)/255, green: parseInt(hex.substring(2, 4), 16)/255, blue: parseInt(hex.substring(4, 6), 16)/255};
};

const colors = {
  "up": hex2RGBA("#A9DFBF"), // green
  "down": hex2RGBA("#E74C3C"), // red
  "dns": hex2RGBA("#D3D3D3"), // grey
  "timeout": hex2RGBA("#c85103"), // the timeout color \o/
  "open": hex2RGBA("#A9DFBF"), // green
  "closed": hex2RGBA("#E74C3C"), // red
  "filtered": hex2RGBA("#F39C12"), // orange
  "unfiltered": hex2RGBA("#F7DC6F"), // yellow
  "open|filtered": hex2RGBA("#C39BD3"), // purple
  "closed|filtered": hex2RGBA("#873600") // brown
};

// dns lookup promise
async function lookup(domain){
  return new Promise((resolve, reject) => {
    dns.lookup(domain, (err, address, family) => {
      if(err) reject(err);
      resolve(address);
    });
  });
};

// nmap promise
async function scan(opts){
  return new Promise((resolve, reject) => {
    nmap.scan(opts, function(err, report) {
      if(err) reject(err);
      resolve(report);
    });
  });
};

(async () => {
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  console.log(`Loading spreadsheet: ${doc.title} (${doc.spreadsheetId})`);
  const sheet = doc.sheetsByIndex[0];
  await sheet.loadCells();

  const data = {};
  for(let i=ROW_OFFSET, len=sheet.rowCount; i<len; i++) {
    //let HTTP_up = sheet.getCell(i, 0);
    let ddns = sheet.getCell(i, 0);
    if(ddns.value != null) {
      data[ddns.value] = {};
      let ip;
      try {
        ip = await lookup(ddns.value);
      } catch(e) {
        ip = e.code || null;
      }
	 /// 
    //  UPDownStats.forEach((h, j) => data[HTTP_up.value][UPDownStats[j]] = sheet.getCell(i, j));
    //  data[HTTP_up.value].HTTPS_up.value = ip; // MOD VALUE
    //  data[HTTP_up.value]['HTT'] = HTTP_up; // just to save on calls
	  ///
      headers.forEach((h, j) => data[ddns.value][headers[j]] = sheet.getCell(i, j));
      data[ddns.value].ip.value = ip; // MOD VALUE
      data[ddns.value]['addr'] = ip; // just to save on calls
    }
  }
  console.table(data, ["addr"]); // log ips
  let ports = [];
  Object.keys(data).forEach(k1 => headers.slice(2).forEach(k2 => ports.push(data[k1][k2].value)));
  ports = Array.from(new Set(ports));
  let ips = Object.values(data).map(e => e.addr).filter(ip => isIP(ip));

  let opts = {
    timeout: 300, 
    //timeout: 15,
	// A List of commands can be fount in Nmap.txt  
    flags: ['-sV', '-sT', '-Pn w/Idlescan'], // This may be another optchion
    //flags: ['-sV', '-sT'], //XSE
    ports: ports.filter(Boolean).join(','),
    range: Array.from(new Set(ips))
  };
 
  console.log(opts);
  let reports;
  try {
    reports = await scan(opts);
    //fs.writeFileSync('reports.json', JSON.stringify(reports, null, 2)); // dev
  } catch(e) {
    reports = null;
    console.log('NMAP ERROR');
    console.log(e);
    process.exit(1);
  }

  Object.keys(data).forEach(domain => {
    headers.forEach(col => data[domain][col].clearAllFormatting());
  });
  await sheet.saveUpdatedCells(); // MOD VALUE RESET!

  Object.keys(data).forEach(domain => {
    if(isIP(data[domain].addr)) {
      /* dns ok */
      if(isUp(reports[data[domain].addr])) {
        /* host up */
        headers.slice(0, 2).forEach(col => {
          data[domain][col].backgroundColor = colors.up;
          data[domain][col].note = 'host seems up';
        /* host up COL */
	});
        headers.slice(2).forEach(p => {
          let pdata = port(reports[data[domain].addr], data[domain][p].value);
          (pdata == null)
            ? (data[domain][p].backgroundColor = colors.timeout, data[domain][p].note = 'nmap timeout')
            : (data[domain][p].backgroundColor = colors[pdata.state.state], data[domain][p].note = `${pdata.state.reason} (${pdata.service.name}/(${pdata.service.product})`);
        });
      } else {
        /* host down */
        headers.forEach(col => {
          data[domain][col].backgroundColor = colors.down;
          data[domain][col].note = 'down/filtered';
        });
      }

    } else {
      /* dns not ok */
      headers.forEach(col => {
        data[domain][col].backgroundColor = colors.dns;
        data[domain][col].note = 'dns FAILED';
      });
    }
  }); 
  sheet.getCell(1, 1).value = new Date().toLocaleString().replace('', '').substr(0, 23); // set the 'updated' cell
  await sheet.saveUpdatedCells();
})();

Then we run this on a timer.

If package is needed i may share that as well

image

@gvalero

Hola de nuevo @abe.sherman,

Do you mean PI= Raspberry Pi? :wink:

My humble advice: try to make things simple at the beginning and don’t try to do everything!
Not all devices are able to support scripts to send/receive data but most support TCP/IP and at least, they have some TCP/UDP port to monitor them. You must use this advantage.

The above script I put doesn’t use Ping, it only checks a TCP port (http) and if its reply is 4xx or 5xx, your device is offline or has some problem. It is what you need to start and make your life easier. Also, it scopes about 90% of failure cases (I think).

Flip the pyramid: make your GS script polls devices and controls how the data is received, not the other way around.

Saludos!

@gvalero Will this work with UDP and RTSP ports?

I can confirm the following
HTTP: Works
HTTPS: SSL Error
Mobile Port: Address unavailable:
The RTSP port just runs and runs.

Timeout issue. Any workaround for that?

Hola Abe,

To be honest, I haven’t used the script mentioned above but it looks fine and useful if we make some changes/improvements.

As I told you, I didn’t continue my idea nor APP then, I couldn’t test it.

If I have some free time :slight_smile: I might take a look at it and check what you mentioned about lags and timeout issues.

Take care!

1 Like

Perfect. I am going to see if I can run this script on my local windows computer using node.js and give everyone detail instructions if I am successful.

@ThinhDinh please recheck app. And read the top. I will be changing discrpchion in the next few days. And fix some minor things, since after some cleanup some minor functions stoped working. More cleanup to be done. And a deep explanation of how the glide table works. Also how can I at @disagreeable-shop here?

Bug: you need to scroll all the way down to click View the app and see how it looks. Planed to fix that. And as well clean up CSS a bit on that page.

@gvalero This is my bot on windows. Works well on linux.

UPDATE
I found something very interesting. For some reason Nmap on windows and linux gives you a slightly different results causing the script to not work. I wonder if it has to do with version of Nmap or how its being run. I have made a small change already got some better results.

If you have windows please go ahead and test and give give me your results.json file. Thank you

Found the Issue but why Nmap?

so from
const isUp = rdata => (rdata.runstats[0].hosts[0].item.up == 1) ? true : false;
To
const isUp = rdata => (rdata.runstats[0].hosts[0].item.up == 2) ? true : false;
Is that what i am supposed to do?

Update 2
Litrly 2 host in array . First host is down second is online. Windows Nmap issues :roll_eyes:
image

1 Like

Wao… you bot works better than any of my ideas!! :slightly_smiling_face:

I have to recognize that I’m learning from you how to create a bot on Windows!

I tested the code already mentioned before and I could see the issues associated to lag and timeout. If the connection is fine (code= 200) the script runs very fast but if not, the timeout is too long (40-50 sec) and becomes a headache.

I will save your video to continue learning about node.js

Thanks @abe.sherman

Cuídate y feliz día!
.

2 Likes

This script will send email if the alert_stats changes stats to either up or down. If it goes down the script will send a email in 5 hours after it has bean down. This is a very useful script. You can use it if a stats has changed. and to send multiple emails that’s listed. Enjoy.

function clr() { // Just to clear the store.
PropertiesService.getScriptProperties().deleteAllProperties();
}

function Alert_when_offline_Online() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('NoIP');
  let store = JSON.parse(PropertiesService.getScriptProperties().getProperty('sent'));
  if(!store) store = [];
  console.log(`store: ${JSON.stringify(store)}`);
  // PropertiesService.getScriptProperties().deleteAllProperties();
  const quota = MailApp.getRemainingDailyQuota(); // Remaining email allowed to send from google
  const data = sheet.getDataRange().getValues(); //Everything but the headers. 
  const headers = data.shift(); // All headers const index = Object.fromEntries(headers.filter(Boolean).map(e => [e, headers.indexOf(e)])); //All heders by number
  const now = new Date();
  const timestamp = Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy-MM-dd'T'HH:mm:ss'Z'");

  data.forEach((row,i) => {
  switch (row[index['HTTP_up']]) { 
  case (row[index['HTTP_up']].match(/^✅/) || {}).input:  //(source_col_val.match(/✅/) 

        if (row[index['Alert_state']].includes('👎')) { 
          data[i][index['Alert_state']] = '✅'; // ack state changed
          data[i][index['Alert_timestamp']] = timestamp; // state changed -> new timestamp
          console.log(`[${row[index['DDNS']]}] up state ack`);

          if(store.includes(row[index['DDNS']])) store.splice(store.indexOf(row[index['DDNS']]), 1); // remove sent mail from store if any

          let addr = data[i].slice(index['mail 1'], index['mail 6']+1).filter(Boolean);
          if(quota > addr.length)
            console.log(`[${row[index['DDNS']]}] down -> up mail to ${addr.join(',')} `)
            var emailAddress = `${addr.join(',')}`;
            var subject = 'DDNS Online';
 var message =  `\n\n${row[index['DDNS']]}:${row[index['HTTP']]}\n\nAll the following peaple receved a down -> up alert. \n\n${addr.join('\n')}`;

            MailApp.sendEmail(emailAddress, subject, message); 

            console.log("MailApp Sending Down: ",quota,MailApp,"More Info: ",emailAddress,subject,message)

        }

        if(row[index['Alert_state']] === "") {  data[i][index['Alert_state']] = '✅'; } // If empty cell UP

        break; 

case (row[index['HTTP_up']].match(/^👎/) || {}).input: 

        if(row[index['Alert_state']].includes('✅')) { 
          data[i][index['Alert_state']] = '👎'; // ack state changed
          data[i][index['Alert_timestamp']] = timestamp; // state changed -> new timestamp
          console.log(`[${row[index['DDNS']]}] down state ack`);
        } else { // Alert_state already '👎'
          let addr = data[i].slice(index['mail 1'], index['mail 6']+1).filter(Boolean);
          // send mail if not already sent if quota ok if down more than 5h
          if(!store.includes(row[index['DDNS']]) && quota > addr.length && Date.parse(row[index['Alert_timestamp']]) < now - 1000*60*60*5) {
            store.push(row[index['DDNS']]); // mail +5h sent
            console.log(`[${row[index['DDNS']]}] send down for +5h mail to ${addr.join(',')}`);

            var emailAddress = `${addr.join(',')}`; //Email
            console.log("Console log Email:",emailAddress);
            //if(emailAddress !== null) { if email is not Null 
            var subject = `DDNS Offline for about 5 Hours.`               var message = `\n\n${row[index['DDNS']]}:${row[index['HTTP']]}\n\nThe follwing receved a UP -> DOWN alert.\n\n${addr.join('\n')}`; 
            MailApp.sendEmail(emailAddress, subject, message);
          //}
          }
        }
        if(row[index['Alert_state']] === "") {  data[i][index['Alert_state']] = '👎'; } // If empty cell
        break;

      default:
  console.log(`unknown state ${row[index['HTTP_up']]} for ${row[index['DDNS']]}`);
      }
  });
  data.unshift(headers);
  console.log(`saving store: ${JSON.stringify(store)}`);
  PropertiesService.getScriptProperties().setProperty('sent', JSON.stringify(store));
  sheet.getRange(1,1,data.length,data[0].length).setValues(data);
}
2 Likes

Even though my bot runs every 15 minutes. Sometimes when I am at a job site I want to manually refresh it as I may be in a hurry. Has anyone bean able to accomplish the following, send a command via SSH, or http, in a script or in the app itself? What’s the right way of doing this? I can use a full guild on it step by step.
@ThinhDinh @gvalero

Hola Abe

  • Send a value (or Webhook) to your GS writing it into a cell (your flag) and later fire your script if cell has changed.

It’s what I made with this button

The red color means that updating process is running and when I receive -1, the color turns to blue: the udpating is over.

Saludos

2 Likes

Do you have a Sample script i can play with?

Sure!

Let me find one of mine and modify it a little to be used.

Saludos

1 Like

Here you can see @abe.sherman

function ReadOnChange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tab = ss.getSheetByName("YOUR_SHEET"); 
  //  Check Cell value (A2, F2 or H5...  whatever) as trigger/flag to start an updating
  if (tab.getRange("YOUR_CELL").getValue() >= 0) {
      /*
         Your code is here
      */
     tab.getRange("YOUR_CELL").setValue(-1); 
     return;
  }
}

Of course, you must create a trigger to your script based on a change in your GS.

Saludos

1 Like

Any sample or link to a sample of the trigger itself. That’s my main Q here.