How Many Times a Node Has Joined to the Network

To gather this information, we will query Node and table events. They both relate each other through NodeId field. Next query shows how to find all the “ACCEPTED” events relate to a node. Node is selected using its EUI64 and not the short address, because short address can be reused by the coordinator and the same short address may be assigned to different devices; however, EUI64 is unique.

CopySELECT Frame.* FROM Frame,Event,Node WHERE Node.idNode = Event.NodeId AND Event.frameid =idframe AND Node.EUI64 =X'4154504C33360001' AND Event.event = 3

Also, we could query the different short address assigned to a node in this log. To do so, we need to gather information from the challenge:

CopySELECT Distinct(Data) FROM Event,Node WHERE Node.idNode = Event.NodeId AND Node.EUI64 =X'4154504C33360001' AND Event.event = 2

We can be interested in knowing when those join events happened:

CopySELECT Frame.* FROM Frame,Event,Node WHERE Node.idNode = Event.NodeId AND Event.FrameId = Frame.idFrame AND Node.EUI64 =X'4154504C33360001' AND Event.event = 2

Or simply:

SELECT idFrame,TimeStamp FROM Frame,Event,Node WHERE Node.idNode = Event.NodeId AND Event.FrameId = Frame.idFrame AND Node.EUI64 =X'4154504C33360001' AND Event.event = 2