Sunday JSONata Challenge!

General
1

Here's a bit of a fun challenge for anyone wanting to improve their JSONata Fu.

Take the output from the following nmap command output (change the IP address/subnet to match your own):

sudo nmap -sn --oX nmap.xml --privileged -R --system-dns --webxml 192.168.1.0/24

Then in Node-RED, read in the resulting XML file (nmap.xml) and push through the XML node to turn it into JSON.

You will note that the conversion wraps XML attributes in $ properties that, in this case, are entirely unnecessary.

...
                {
                    "status": [
                        {
                            "$": {
                                "state": "up",
                                "reason": "arp-response",
                                "reason_ttl": "0"
                            }
                        }
                    ],
                    "address": [
                        {
                            "$": {
                                "addr": "192.168.1.167",
                                "addrtype": "ipv4"
                            }
                        },
                        {
                            "$": {
                                "addr": "B8:27:EB:DF:49:7E",
                                "addrtype": "mac",
                                "vendor": "Raspberry Pi Foundation"
                            }
                        }
                    ],
                    "hostnames": [
                        {
                            "hostname": [
                                {
                                    "$": {
                                        "name": "pi2.knightnet.co.uk",
                                        "type": "PTR"
                                    }
                                }
                            ]
                        }
                    ],
                    "times": [
                        {
                            "$": {
                                "srtt": "406",
                                "rttvar": "5000",
                                "to": "100000"
                            }
                        }
                    ]
                },
...

So the challenge is to simplify the output, removing the extraneous $ properties:

                {
                    "status": [
                        {
                             "state": "up",
                             "reason": "arp-response",
                             "reason_ttl": "0"
                        }
                    ],

Good luck!

In case you are wondering what the end-game of this might be, I've two reasons. One is that the extraneous $ objects are common artefacts of converting from XML to JSON and so it isn't uncommon to want to get rid of them.

The second is that I am trying to automatically capture and update the devices on my network. I am combining this with some reference data that I maintain in an Excel spreadsheet detailing IP & MAC addresses with descriptions.

Ultimately, I will also combine this with 433MHz devices identified via my RFXtrx433E and devices on my Drayton Wiser smart heating system. Then I will have a complete picture of the devices I am interested in on my Home Automation system.

I will do a write-up of how to combine two arrays of objects using JSONata on my blog shortly which will hopefully be a useful reference for people trying to do similar tasks.

By the way, here is what I've come up with so far - but note that it just side-steps the challenge by building a new array of objects.

Spoiler 
payload.nmaprun.host.(
    {
        /* Note different lookup methods */
        "ip": address.*[addrtype="ipv4"].addr,
        "mac": address.*[addrtype="mac"].addr.$uppercase(),
        "vendor": address.*.vendor,
        "hostname": hostnames.hostname."$".name,
        /* srtt is in microseconds so convert to milliseconds. /1000 again to get to seconds */
        "srtt": $parseInteger(times."$".srtt, '#0')/1000,
        /* Add a JS timestamp for when this was run
         * Eventually will use this to track last updates for devices. */
        "timestamp": $parseInteger($$.payload.nmaprun."$".start, '#0')*1000
    }
)

Note that the other thing I'd like to do to this is to turn the array into an object keyed on mac address.

2

JSONata 1.8, yet to be included with Node-RED is introducing a parent operator, which makes paging through the tree a lot easier/readable. I've done queries like this before through rewriting (once you reach your first 100 line expression with an xml node it gets easy), in a similar way as your solution. Let me see if I can come up with a different one in a bit, I do have some ideas but they need a bit of experimentation

Unfortunately, my fingers aren't cooperating with my mind today, and I can't manage to get the syntax correct. The idea would be to utilise JSONata's capability of writing recursive functions, and have a function that for every object will look for a key "$", and get the underlying value out and inserted on parent level.

3

Thanks for looking Lena.

For now, I gave up (as often the case with JSONata) and went back to a familiar function node. A lot quicker to deal with! But I have to set myself (and others :grinning:) a challenge from time-to-time.

Anyway, the output from today's and yesterday's efforts has been to produce a new devices structure. This combines my manual IP/Mac table with nmap discovery. I've started to build a uibuilder editable table so that I will be able to update things like descriptions and locations for devices.

image
image1189Ă—450 41.6 KB

I blame Paul @zenofmud for this distraction because I looked again at his excellent "ET Display Home" and decided that I wanted a version that didn't take up 300 tabs and that would use uibuilder instead of Dashboard :grinning:

That then reminded me that I'd promised myself that I'd re-engineer my home automation reference data which in turn reminded me that I really needed a UI to administer all this stuff rather than manually editing it in the Editor.

Oh dear, what was that about addiction?!

4

You know, I did look at trying the figure out how to do it with JSONata but was stumped myself (not a difficult task when JSONata is involved) and decided if I was going to do, I'd do it with some function nodes myself.

Then I went for a 4 mile walk instead :innocent:

5

Finally had some time to work on this. I'm not totally happy with the result, but I look forward to adding more in the coming weeks.

6

Note that the xml node can also assign a different value in stead of the $

7

Look forward to seeing the results :grinning:

Doesn't really help though does it? You still get the extra level of object structure as the attributes are turned into ...$.{...}. In the example case, I know that there is no clash between XML tag names and attribute names so the whole lot could be flattened significantly making it much easier to process further.

8

You're right I misread, you want to remove the attribute "enclosure", I agree it can be quite messy at times (looking at my zwave xml, it is hard to read through it)

I think you need to rewrite the "definition" of the json output, something in this direction (my jsonata skills are terrible, but you get the gist):

{
  "status": **.(
    
    $state := **.state;
    $reason := **.reason;

    "status".{
        "state" : $state,
        "reason": $reason
    }
  )
  }
9

Cool. Well done, that's the best yet :grinning:

Somewhat extended:

{
  "status": **.(
    
    $state := **.state;
    $reason := **.reason;
    $addr := **.addr;
    $vendor := **.vendor;
    $srtt := **.srtt;
    $hostname := **.name;

    "status".{
        "state" : $state,
        "reason": $reason,
        "address": $addr[0],
        "mac": $addr[1],
        "vendor": $vendor,
        "srtt": $srtt,
        "hostname": $hostname
    }
  )
  }