Task 1

Points: 9

Description

Aaliyah is showing you how Intelligence Analysts work. She pulls up a piece of intelligence she thought was interesting. It shows that APTs are interested in acquiring hardware tokens used for accessing DIB networks. Those are generally controlled items, how could the APT get a hold of one of those?

DoD sometimes sends copies of procurement records for controlled items to the NSA for analysis. Aaliyah pulls up the records but realizes it’s in a file format she’s not familiar with. Can you help her look for anything suspicious?

If DIB companies are being actively targeted by an adversary the NSA needs to know about it so they can help mitigate the threat.

Help Aaliyah determine the outlying activity in the dataset given

Downloads:
DoD procurement records (shipping.db)
Prompt:
Provide the order id associated with the order most likely to be fraudulent.

Solution

First thing to check is what kind of db file this is.

cobra@arch:~/codebreaker/task1$ file shipping.db

shipping.db: Zip data (MIME type "application/vnd.oasis.O"?)

Since its zip data, we can start by unzipping it.

cobra@arch:~/codebreaker/task1$ mkdir shipping

cobra@arch:~/codebreaker/task1$ unzip -d shipping shipping.db

Looking at the files in there, we can see that is an OASIS Open Document which is what the MIME type was referring to.

cobra@arch:~/codebreaker/task1/shipping$ cat manifest.rdf

<?xml version="1.0" encoding="utf-8"?>
<rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
  <rdf:Description rdf:about="styles.xml">
    <rdf:type rdf:resource="http://docs.oasis-open.org/ns/office/1.2/meta/odf#StylesFile"/>
  </rdf:Description>
  <rdf:Description rdf:about="">
    <ns0:hasPart xmlns:ns0="http://docs.oasis-open.org/ns/office/1.2/meta/pkg#" rdf:resource="styles.xml"/>
  </rdf:Description>
  <rdf:Description rdf:about="content.xml">
    <rdf:type rdf:resource="http://docs.oasis-open.org/ns/office/1.2/meta/odf#ContentFile"/>
  </rdf:Description>
  <rdf:Description rdf:about="">
    <ns0:hasPart xmlns:ns0="http://docs.oasis-open.org/ns/office/1.2/meta/pkg#" rdf:resource="content.xml"/>
  </rdf:Description>
  <rdf:Description rdf:about="">
    <rdf:type rdf:resource="http://docs.oasis-open.org/ns/office/1.2/meta/pkg#Document"/>
  </rdf:Description>
</rdf:RDF>

We can open this format in LibreOffice. It does prompt to repair the file at first, but succesfully loads after doing this.

The file is basically a spreadsheet of all the orders.

The most noticable thing here is that all the orders use the same company information with only the date and order number changing. Since we’re looking for outlying activity, we can search for orders from the same company that have different information.

Since LibreOffice opened the spreadsheet with Writer, we can’t apply any formulas, so to do that we would need to transfer the data to LibreOffice Calc, Google Sheets, Excel, etc.

However, if we explore the unzipped data further, we can see that all the spreadsheet data is stored in content.xml. So, we can just write a Python script to parse it.

Python has built-in XML processing modules that we can use.

All we need to do is iterate through every row of the table and aggregate them all into one list, not including the order IDs and dates since we only care about variations in company information. Once we have a list of orders, we can convert the list to set to remove any duplicates, resulting in a list of all companies associated with every order.

We can also output all orders to a CSV file for easier reading.

import xml.etree.ElementTree as ET

tree = ET.parse('shipping/content.xml')
root = tree.getroot()

namespaces = {
    'office': 'urn:oasis:names:tc:opendocument:xmlns:office:1.0',
    'table': 'urn:oasis:names:tc:opendocument:xmlns:table:1.0',
    'text': 'urn:oasis:names:tc:opendocument:xmlns:text:1.0'
}

tables = root.findall('.//table:table', namespaces)

orderers = []

with open('./all_orders.csv', 'w') as output_file:
    for table in tables:
        table_name = table.get('{urn:oasis:names:tc:opendocument:xmlns:table:1.0}name')
        
        for row in table.findall('table:table-row', namespaces):
            row_values = []
            
            for cell in row.findall('table:table-cell', namespaces):
                p_elements = cell.findall('text:p', namespaces)
                cell_value = ''.join([p.text for p in p_elements if p.text])
                row_values.append(cell_value)
            
            output_file.write(','.join(row_values) + '\n')
            orderers.append(','.join(row_values[:-2]))

orderers = set(orderers)

for orderer in orderers:
    print(orderer)

cobra@arch:~/codebreaker/task1$ python solve.py

Titan Aerospace Systems,892 Collins Island, Yoderfort, CT 11532,Nicholas Clark,###-###-8824,[email protected],Sharon Brown,###-###-0320,[email protected]
Williams Jackson International,85141 Wilson Skyway, West Jacquelineburgh, TX 16674,Hailey Mills,###-###-0400,[email protected],Jason Oneill,###-###-1291,[email protected]
Vanguard Technologies,0565 Fowler Cove Suite 052, Port James, OR 72033,Matthew Gray,###-###-2401,[email protected],Jane Hampton,###-###-8270,[email protected]
Springfield Defense Laboratories,75395 Jones Fields, East Randyside, MN 12403,Karen Parker,###-###-3347,[email protected],Renee Wells,###-###-8988,[email protected]
Phoenix Tactical Innovations,7929 William Causeway, Port Kimberly, DE 80669,Jeffrey Barnes,###-###-5820,[email protected],Jennifer Hansen,###-###-3393,[email protected]
Cerberus Defense Solutions,595 David Springs, West Charlesville, SC 36646,Eric Jones,###-###-4059,[email protected],Linda Smith,###-###-0319,[email protected]
Guardian Armaments,1780 Brian Knoll Suite 957, Port Gregory, NE 13381,Joy Richardson,###-###-7610,[email protected],Jasper Wright,###-###-9691,[email protected]
Aerospace Dynamics,Unit 1932 Box 0996, DPO AA 92849,Dawn Jackson,###-###-5604,[email protected],Mark Drake,###-###-7582,[email protected]
Atlas Strategic Systems,650 Johnathan Brooks, Paigeshire, NH 27360,Howard Hernandez,###-###-7747,[email protected],Lauren Alvarez,###-###-3901,[email protected]
Guardian Armaments,PSC 9018, Box 4767, APO AP 63208,Joy Richardson,###-###-7610,[email protected],Jasper Wright,###-###-9691,[email protected]
Terraform Industries,5745 Nancy Springs, South Randy, HI 92772,David Summers,###-###-8114,[email protected],Michael May,###-###-3182,[email protected]
Sentinel Security Group,17537 John Meadows, Robertsport, AR 46947,Carrie Johnson,###-###-5484,[email protected],Whitney Moreno,###-###-9858,[email protected]
Aegis Defense Solutions,86228 Thornton Expressway, New Carolynborough, IA 28111,Molly Hunt,###-###-9572,[email protected],Veronica Hammond,###-###-2697,[email protected]
Ironclad Defense Technologies,734 Snyder Ranch, Butlerborough, IA 65637,Mr. Carlos Norton,###-###-4688,[email protected],Wendy Murphy,###-###-3451,[email protected]

Looking at the list of companies, one company is listed twice, Guardian Armaments.

Here’s the two entries.

Guardian Armaments,1780 Brian Knoll Suite 957, Port Gregory, NE 13381,Joy Richardson,###-###-7610,[email protected],Jasper Wright,###-###-9691,[email protected]

Guardian Armaments,PSC 9018, Box 4767, APO AP 63208,Joy Richardson,###-###-7610,[email protected],Jasper Wright,###-###-9691,[email protected]

The only difference between these is the addresses. If we search through the spreadsheet for each of these addresses, we can see that the first address is used for all orders except a single order where the second entry’s address is used. So, that order must be fradualent.

So, we can submit the order ID GUA0533770.

Result

Great Work! That order does look fishy…

Description#

Solution#

Result#

Description

Solution

Result