... sound of crickets on a summer night ..... It would really help me if I could get a response to this inquiry, to help me better understand Drill.
I do realize people are busy, and also this was originally sent Aug 25, which is the wrong time of year to get timely response to anything. Hence, this re-up of the message. On Fri, Aug 25, 2023 at 7:39 PM Mike Beckerle <mbecke...@apache.org> wrote: > Below is a small JSON output from Daffodil and below that is the same > Infoset output as XML. > (They're inline in this message, but I also attached them as files) > > This is just a parse of a small PCAP file with a few ICMP packets in it. > It's an example DFDL schema used to illustrate binary file parsing. > > (The schema is here https://github.com/DFDLSchemas/PCAP which uses this > component schema: https://github.com/DFDLSchemas/ethernetIP) > > My theory is that Drill queries against these should be identical to > obtain the same output row contents. > That is, since this data has the same schema, whether it is JSON or XML > shouldn't affect how you query it. > To do that the XML Reader will need the XML schema (or some hand-provided > metadata) so it knows what is an array. (Specifically PCAP.Packet is the > array.) > > E.g., if you wanted to get the IPSrc and IPDest fields in a table from all > ICMP packets in this file, that query should be the same for the JSON and > the XML data. > > First question: Does that make sense? I want to make sure I'm > understanding this right. > > Second question, since I don't really understand Drill SQL yet. > > What is a query that would pluck the IPSrc.value and IPDest.value from > this data and make a row of each pair of those? > > The top level is a map with a single element named PCAP. > The "table" is PCAP.Packet which is an array (of maps). > And within each array item's map the fields of interest are within > LinkLayer.Ethernet.NetworkLayer.IPv4.IPv4Header > (so maybe IPv4Header is the table?) > The two fields within there are IPSrc.value (AS src) and IPDest.value (AS > dest) > > I'm lost on how to tell the query that the table is the array PCAP.Packet, > or the ....IPv4Header within those maybe? > > Maybe this is easy, but I'm just not grokking it yet so I could use some > help here. > > Thanks in advance. > > { > "PCAP": { > "PCAPHeader": { > "MagicNumber": "D4C3B2A1", > "Version": { > "Major": "2", > "Minor": "4" > }, > "Zone": "0", > "SigFigs": "0", > "SnapLen": "65535", > "Network": "1" > }, > "Packet": [ > { > "PacketHeader": { > "Seconds": "1371631556", > "USeconds": "838904", > "InclLen": "74", > "OrigLen": "74" > }, > "LinkLayer": { > "Ethernet": { > "MACDest": "005056E01449", > "MACSrc": "000C29340BDE", > "Ethertype": "2048", > "NetworkLayer": { > "IPv4": { > "IPv4Header": { > "Version": "4", > "IHL": "5", > "DSCP": "0", > "ECN": "0", > "Length": "60", > "Identification": "55107", > "Flags": "0", > "FragmentOffset": "0", > "TTL": "128", > "Protocol": "1", > "Checksum": "11123", > "IPSrc": { > "value": "192.168.158.139" > }, > "IPDest": { > "value": "174.137.42.77" > }, > "ComputedChecksum": "11123" > }, > "Protocol": "1", > "ICMPv4": { > "Type": "8", > "Code": "0", > "Checksum": "10844", > "EchoRequest": { > "Identifier": "512", > "SequenceNumber": "8448", > "Payload": > "6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869" > } > } > } > } > } > } > }, > { > "PacketHeader": { > "Seconds": "1371631557", > "USeconds": "55699", > "InclLen": "74", > "OrigLen": "74" > }, > "LinkLayer": { > "Ethernet": { > "MACDest": "000C29340BDE", > "MACSrc": "005056E01449", > "Ethertype": "2048", > "NetworkLayer": { > "IPv4": { > "IPv4Header": { > "Version": "4", > "IHL": "5", > "DSCP": "0", > "ECN": "0", > "Length": "60", > "Identification": "30433", > "Flags": "0", > "FragmentOffset": "0", > "TTL": "128", > "Protocol": "1", > "Checksum": "35797", > "IPSrc": { > "value": "174.137.42.77" > }, > "IPDest": { > "value": "192.168.158.139" > }, > "ComputedChecksum": "35797" > }, > "Protocol": "1", > "ICMPv4": { > "Type": "0", > "Code": "0", > "Checksum": "12892", > "EchoReply": { > "Identifier": "512", > "SequenceNumber": "8448", > "Payload": > "6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869" > } > } > } > } > } > } > }, > { > "PacketHeader": { > "Seconds": "1371631557", > "USeconds": "840049", > "InclLen": "74", > "OrigLen": "74" > }, > "LinkLayer": { > "Ethernet": { > "MACDest": "005056E01449", > "MACSrc": "000C29340BDE", > "Ethertype": "2048", > "NetworkLayer": { > "IPv4": { > "IPv4Header": { > "Version": "4", > "IHL": "5", > "DSCP": "0", > "ECN": "0", > "Length": "60", > "Identification": "55110", > "Flags": "0", > "FragmentOffset": "0", > "TTL": "128", > "Protocol": "1", > "Checksum": "11120", > "IPSrc": { > "value": "192.168.158.139" > }, > "IPDest": { > "value": "174.137.42.77" > }, > "ComputedChecksum": "11120" > }, > "Protocol": "1", > "ICMPv4": { > "Type": "8", > "Code": "0", > "Checksum": "10588", > "EchoRequest": { > "Identifier": "512", > "SequenceNumber": "8704", > "Payload": > "6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869" > } > } > } > } > } > } > }, > { > "PacketHeader": { > "Seconds": "1371631558", > "USeconds": "44196", > "InclLen": "74", > "OrigLen": "74" > }, > "LinkLayer": { > "Ethernet": { > "MACDest": "000C29340BDE", > "MACSrc": "005056E01449", > "Ethertype": "2048", > "NetworkLayer": { > "IPv4": { > "IPv4Header": { > "Version": "4", > "IHL": "5", > "DSCP": "0", > "ECN": "0", > "Length": "60", > "Identification": "30436", > "Flags": "0", > "FragmentOffset": "0", > "TTL": "128", > "Protocol": "1", > "Checksum": "35794", > "IPSrc": { > "value": "174.137.42.77" > }, > "IPDest": { > "value": "192.168.158.139" > }, > "ComputedChecksum": "35794" > }, > "Protocol": "1", > "ICMPv4": { > "Type": "0", > "Code": "0", > "Checksum": "12636", > "EchoReply": { > "Identifier": "512", > "SequenceNumber": "8704", > "Payload": > "6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869" > } > } > } > } > } > } > }, > { > "PacketHeader": { > "Seconds": "1371631558", > "USeconds": "841168", > "InclLen": "74", > "OrigLen": "74" > }, > "LinkLayer": { > "Ethernet": { > "MACDest": "005056E01449", > "MACSrc": "000C29340BDE", > "Ethertype": "2048", > "NetworkLayer": { > "IPv4": { > "IPv4Header": { > "Version": "4", > "IHL": "5", > "DSCP": "0", > "ECN": "0", > "Length": "60", > "Identification": "55113", > "Flags": "0", > "FragmentOffset": "0", > "TTL": "128", > "Protocol": "1", > "Checksum": "11117", > "IPSrc": { > "value": "192.168.158.139" > }, > "IPDest": { > "value": "174.137.42.77" > }, > "ComputedChecksum": "11117" > }, > "Protocol": "1", > "ICMPv4": { > "Type": "8", > "Code": "0", > "Checksum": "10332", > "EchoRequest": { > "Identifier": "512", > "SequenceNumber": "8960", > "Payload": > "6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869" > } > } > } > } > } > } > }, > { > "PacketHeader": { > "Seconds": "1371631559", > "USeconds": "85428", > "InclLen": "74", > "OrigLen": "74" > }, > "LinkLayer": { > "Ethernet": { > "MACDest": "000C29340BDE", > "MACSrc": "005056E01449", > "Ethertype": "2048", > "NetworkLayer": { > "IPv4": { > "IPv4Header": { > "Version": "4", > "IHL": "5", > "DSCP": "0", > "ECN": "0", > "Length": "60", > "Identification": "30448", > "Flags": "0", > "FragmentOffset": "0", > "TTL": "128", > "Protocol": "1", > "Checksum": "35782", > "IPSrc": { > "value": "174.137.42.77" > }, > "IPDest": { > "value": "192.168.158.139" > }, > "ComputedChecksum": "35782" > }, > "Protocol": "1", > "ICMPv4": { > "Type": "0", > "Code": "0", > "Checksum": "12380", > "EchoReply": { > "Identifier": "512", > "SequenceNumber": "8960", > "Payload": > "6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869" > } > } > } > } > } > } > }, > { > "PacketHeader": { > "Seconds": "1371631559", > "USeconds": "841775", > "InclLen": "74", > "OrigLen": "74" > }, > "LinkLayer": { > "Ethernet": { > "MACDest": "005056E01449", > "MACSrc": "000C29340BDE", > "Ethertype": "2048", > "NetworkLayer": { > "IPv4": { > "IPv4Header": { > "Version": "4", > "IHL": "5", > "DSCP": "0", > "ECN": "0", > "Length": "60", > "Identification": "55118", > "Flags": "0", > "FragmentOffset": "0", > "TTL": "128", > "Protocol": "1", > "Checksum": "11112", > "IPSrc": { > "value": "192.168.158.139" > }, > "IPDest": { > "value": "174.137.42.77" > }, > "ComputedChecksum": "11112" > }, > "Protocol": "1", > "ICMPv4": { > "Type": "8", > "Code": "0", > "Checksum": "10076", > "EchoRequest": { > "Identifier": "512", > "SequenceNumber": "9216", > "Payload": > "6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869" > } > } > } > } > } > } > }, > { > "PacketHeader": { > "Seconds": "1371631560", > "USeconds": "42354", > "InclLen": "74", > "OrigLen": "74" > }, > "LinkLayer": { > "Ethernet": { > "MACDest": "000C29340BDE", > "MACSrc": "005056E01449", > "Ethertype": "2048", > "NetworkLayer": { > "IPv4": { > "IPv4Header": { > "Version": "4", > "IHL": "5", > "DSCP": "0", > "ECN": "0", > "Length": "60", > "Identification": "30453", > "Flags": "0", > "FragmentOffset": "0", > "TTL": "128", > "Protocol": "1", > "Checksum": "35777", > "IPSrc": { > "value": "174.137.42.77" > }, > "IPDest": { > "value": "192.168.158.139" > }, > "ComputedChecksum": "35777" > }, > "Protocol": "1", > "ICMPv4": { > "Type": "0", > "Code": "0", > "Checksum": "12124", > "EchoReply": { > "Identifier": "512", > "SequenceNumber": "9216", > "Payload": > "6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869" > } > } > } > } > } > } > } > ] > } > } > > <?xml version="1.0" encoding="UTF-8"?> > <tns:PCAP xmlns:tns="urn:pcap:2.4"> > <PCAPHeader> > <MagicNumber>D4C3B2A1</MagicNumber> > <Version> > <Major>2</Major> > <Minor>4</Minor> > </Version> > <Zone>0</Zone> > <SigFigs>0</SigFigs> > <SnapLen>65535</SnapLen> > <Network>1</Network> > </PCAPHeader> > <Packet> > <PacketHeader> > <Seconds>1371631556</Seconds> > <USeconds>838904</USeconds> > <InclLen>74</InclLen> > <OrigLen>74</OrigLen> > </PacketHeader> > <LinkLayer> > <Ethernet> > <MACDest>005056E01449</MACDest> > <MACSrc>000C29340BDE</MACSrc> > <Ethertype>2048</Ethertype> > <NetworkLayer> > <IPv4> > <IPv4Header> > <Version>4</Version> > <IHL>5</IHL> > <DSCP>0</DSCP> > <ECN>0</ECN> > <Length>60</Length> > <Identification>55107</Identification> > <Flags>0</Flags> > <FragmentOffset>0</FragmentOffset> > <TTL>128</TTL> > <Protocol>1</Protocol> > <Checksum>11123</Checksum> > <IPSrc> > <value>192.168.158.139</value> > </IPSrc> > <IPDest> > <value>174.137.42.77</value> > </IPDest> > <ComputedChecksum>11123</ComputedChecksum> > </IPv4Header> > <Protocol>1</Protocol> > <ICMPv4> > <Type>8</Type> > <Code>0</Code> > <Checksum>10844</Checksum> > <EchoRequest> > <Identifier>512</Identifier> > <SequenceNumber>8448</SequenceNumber> > <Payload>6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869 > </Payload> > </EchoRequest> > </ICMPv4> > </IPv4> > </NetworkLayer> > </Ethernet> > </LinkLayer> > </Packet> > <Packet> > <PacketHeader> > <Seconds>1371631557</Seconds> > <USeconds>55699</USeconds> > <InclLen>74</InclLen> > <OrigLen>74</OrigLen> > </PacketHeader> > <LinkLayer> > <Ethernet> > <MACDest>000C29340BDE</MACDest> > <MACSrc>005056E01449</MACSrc> > <Ethertype>2048</Ethertype> > <NetworkLayer> > <IPv4> > <IPv4Header> > <Version>4</Version> > <IHL>5</IHL> > <DSCP>0</DSCP> > <ECN>0</ECN> > <Length>60</Length> > <Identification>30433</Identification> > <Flags>0</Flags> > <FragmentOffset>0</FragmentOffset> > <TTL>128</TTL> > <Protocol>1</Protocol> > <Checksum>35797</Checksum> > <IPSrc> > <value>174.137.42.77</value> > </IPSrc> > <IPDest> > <value>192.168.158.139</value> > </IPDest> > <ComputedChecksum>35797</ComputedChecksum> > </IPv4Header> > <Protocol>1</Protocol> > <ICMPv4> > <Type>0</Type> > <Code>0</Code> > <Checksum>12892</Checksum> > <EchoReply> > <Identifier>512</Identifier> > <SequenceNumber>8448</SequenceNumber> > <Payload>6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869 > </Payload> > </EchoReply> > </ICMPv4> > </IPv4> > </NetworkLayer> > </Ethernet> > </LinkLayer> > </Packet> > <Packet> > <PacketHeader> > <Seconds>1371631557</Seconds> > <USeconds>840049</USeconds> > <InclLen>74</InclLen> > <OrigLen>74</OrigLen> > </PacketHeader> > <LinkLayer> > <Ethernet> > <MACDest>005056E01449</MACDest> > <MACSrc>000C29340BDE</MACSrc> > <Ethertype>2048</Ethertype> > <NetworkLayer> > <IPv4> > <IPv4Header> > <Version>4</Version> > <IHL>5</IHL> > <DSCP>0</DSCP> > <ECN>0</ECN> > <Length>60</Length> > <Identification>55110</Identification> > <Flags>0</Flags> > <FragmentOffset>0</FragmentOffset> > <TTL>128</TTL> > <Protocol>1</Protocol> > <Checksum>11120</Checksum> > <IPSrc> > <value>192.168.158.139</value> > </IPSrc> > <IPDest> > <value>174.137.42.77</value> > </IPDest> > <ComputedChecksum>11120</ComputedChecksum> > </IPv4Header> > <Protocol>1</Protocol> > <ICMPv4> > <Type>8</Type> > <Code>0</Code> > <Checksum>10588</Checksum> > <EchoRequest> > <Identifier>512</Identifier> > <SequenceNumber>8704</SequenceNumber> > <Payload>6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869 > </Payload> > </EchoRequest> > </ICMPv4> > </IPv4> > </NetworkLayer> > </Ethernet> > </LinkLayer> > </Packet> > <Packet> > <PacketHeader> > <Seconds>1371631558</Seconds> > <USeconds>44196</USeconds> > <InclLen>74</InclLen> > <OrigLen>74</OrigLen> > </PacketHeader> > <LinkLayer> > <Ethernet> > <MACDest>000C29340BDE</MACDest> > <MACSrc>005056E01449</MACSrc> > <Ethertype>2048</Ethertype> > <NetworkLayer> > <IPv4> > <IPv4Header> > <Version>4</Version> > <IHL>5</IHL> > <DSCP>0</DSCP> > <ECN>0</ECN> > <Length>60</Length> > <Identification>30436</Identification> > <Flags>0</Flags> > <FragmentOffset>0</FragmentOffset> > <TTL>128</TTL> > <Protocol>1</Protocol> > <Checksum>35794</Checksum> > <IPSrc> > <value>174.137.42.77</value> > </IPSrc> > <IPDest> > <value>192.168.158.139</value> > </IPDest> > <ComputedChecksum>35794</ComputedChecksum> > </IPv4Header> > <Protocol>1</Protocol> > <ICMPv4> > <Type>0</Type> > <Code>0</Code> > <Checksum>12636</Checksum> > <EchoReply> > <Identifier>512</Identifier> > <SequenceNumber>8704</SequenceNumber> > <Payload>6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869 > </Payload> > </EchoReply> > </ICMPv4> > </IPv4> > </NetworkLayer> > </Ethernet> > </LinkLayer> > </Packet> > <Packet> > <PacketHeader> > <Seconds>1371631558</Seconds> > <USeconds>841168</USeconds> > <InclLen>74</InclLen> > <OrigLen>74</OrigLen> > </PacketHeader> > <LinkLayer> > <Ethernet> > <MACDest>005056E01449</MACDest> > <MACSrc>000C29340BDE</MACSrc> > <Ethertype>2048</Ethertype> > <NetworkLayer> > <IPv4> > <IPv4Header> > <Version>4</Version> > <IHL>5</IHL> > <DSCP>0</DSCP> > <ECN>0</ECN> > <Length>60</Length> > <Identification>55113</Identification> > <Flags>0</Flags> > <FragmentOffset>0</FragmentOffset> > <TTL>128</TTL> > <Protocol>1</Protocol> > <Checksum>11117</Checksum> > <IPSrc> > <value>192.168.158.139</value> > </IPSrc> > <IPDest> > <value>174.137.42.77</value> > </IPDest> > <ComputedChecksum>11117</ComputedChecksum> > </IPv4Header> > <Protocol>1</Protocol> > <ICMPv4> > <Type>8</Type> > <Code>0</Code> > <Checksum>10332</Checksum> > <EchoRequest> > <Identifier>512</Identifier> > <SequenceNumber>8960</SequenceNumber> > <Payload>6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869 > </Payload> > </EchoRequest> > </ICMPv4> > </IPv4> > </NetworkLayer> > </Ethernet> > </LinkLayer> > </Packet> > <Packet> > <PacketHeader> > <Seconds>1371631559</Seconds> > <USeconds>85428</USeconds> > <InclLen>74</InclLen> > <OrigLen>74</OrigLen> > </PacketHeader> > <LinkLayer> > <Ethernet> > <MACDest>000C29340BDE</MACDest> > <MACSrc>005056E01449</MACSrc> > <Ethertype>2048</Ethertype> > <NetworkLayer> > <IPv4> > <IPv4Header> > <Version>4</Version> > <IHL>5</IHL> > <DSCP>0</DSCP> > <ECN>0</ECN> > <Length>60</Length> > <Identification>30448</Identification> > <Flags>0</Flags> > <FragmentOffset>0</FragmentOffset> > <TTL>128</TTL> > <Protocol>1</Protocol> > <Checksum>35782</Checksum> > <IPSrc> > <value>174.137.42.77</value> > </IPSrc> > <IPDest> > <value>192.168.158.139</value> > </IPDest> > <ComputedChecksum>35782</ComputedChecksum> > </IPv4Header> > <Protocol>1</Protocol> > <ICMPv4> > <Type>0</Type> > <Code>0</Code> > <Checksum>12380</Checksum> > <EchoReply> > <Identifier>512</Identifier> > <SequenceNumber>8960</SequenceNumber> > <Payload>6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869 > </Payload> > </EchoReply> > </ICMPv4> > </IPv4> > </NetworkLayer> > </Ethernet> > </LinkLayer> > </Packet> > <Packet> > <PacketHeader> > <Seconds>1371631559</Seconds> > <USeconds>841775</USeconds> > <InclLen>74</InclLen> > <OrigLen>74</OrigLen> > </PacketHeader> > <LinkLayer> > <Ethernet> > <MACDest>005056E01449</MACDest> > <MACSrc>000C29340BDE</MACSrc> > <Ethertype>2048</Ethertype> > <NetworkLayer> > <IPv4> > <IPv4Header> > <Version>4</Version> > <IHL>5</IHL> > <DSCP>0</DSCP> > <ECN>0</ECN> > <Length>60</Length> > <Identification>55118</Identification> > <Flags>0</Flags> > <FragmentOffset>0</FragmentOffset> > <TTL>128</TTL> > <Protocol>1</Protocol> > <Checksum>11112</Checksum> > <IPSrc> > <value>192.168.158.139</value> > </IPSrc> > <IPDest> > <value>174.137.42.77</value> > </IPDest> > <ComputedChecksum>11112</ComputedChecksum> > </IPv4Header> > <Protocol>1</Protocol> > <ICMPv4> > <Type>8</Type> > <Code>0</Code> > <Checksum>10076</Checksum> > <EchoRequest> > <Identifier>512</Identifier> > <SequenceNumber>9216</SequenceNumber> > <Payload>6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869 > </Payload> > </EchoRequest> > </ICMPv4> > </IPv4> > </NetworkLayer> > </Ethernet> > </LinkLayer> > </Packet> > <Packet> > <PacketHeader> > <Seconds>1371631560</Seconds> > <USeconds>42354</USeconds> > <InclLen>74</InclLen> > <OrigLen>74</OrigLen> > </PacketHeader> > <LinkLayer> > <Ethernet> > <MACDest>000C29340BDE</MACDest> > <MACSrc>005056E01449</MACSrc> > <Ethertype>2048</Ethertype> > <NetworkLayer> > <IPv4> > <IPv4Header> > <Version>4</Version> > <IHL>5</IHL> > <DSCP>0</DSCP> > <ECN>0</ECN> > <Length>60</Length> > <Identification>30453</Identification> > <Flags>0</Flags> > <FragmentOffset>0</FragmentOffset> > <TTL>128</TTL> > <Protocol>1</Protocol> > <Checksum>35777</Checksum> > <IPSrc> > <value>174.137.42.77</value> > </IPSrc> > <IPDest> > <value>192.168.158.139</value> > </IPDest> > <ComputedChecksum>35777</ComputedChecksum> > </IPv4Header> > <Protocol>1</Protocol> > <ICMPv4> > <Type>0</Type> > <Code>0</Code> > <Checksum>12124</Checksum> > <EchoReply> > <Identifier>512</Identifier> > <SequenceNumber>9216</SequenceNumber> > <Payload>6162636465666768696A6B6C6D6E6F7071727374757677616263646566676869 > </Payload> > </EchoReply> > </ICMPv4> > </IPv4> > </NetworkLayer> > </Ethernet> > </LinkLayer> > </Packet> > </tns:PCAP> > > Mike Beckerle > Apache Daffodil PMC | daffodil.apache.org > OGF DFDL Workgroup Co-Chair | www.ogf.org/ogf/doku.php/standards/dfdl/dfdl > Owl Cyber Defense | www.owlcyberdefense.com > > >
