<jupyter_start><jupyter_text>Dataset ExplorationLet's run a few queries against our database to attempt to understand what information we have.  First, we need to set up a few contextual variables.<jupyter_code>import pandas as pd
from py2neo import Graph

# Connect to our Graph database, ensure connectivity, and store connection in variable.
graph = Graph("bolt://localhost", auth=("neo4j", "neo4j"));<jupyter_output><empty_output><jupyter_text>Now, we can run a simple query to give us our first look into the dataset.  The below query will give us all of our different node types and how many of those types we have.<jupyter_code>result = {"label": [], "count": []}
for label in graph.run("CALL db.labels()").to_series():
    query = f"MATCH (:`{label}`) RETURN count(*) as count"
    count = graph.run(query).to_data_frame().iloc[0]['count']
    result["label"].append(label)
    result["count"].append(count)
nodes_df = pd.DataFrame(data=result)
nodes_df.sort_values("count")<jupyter_output><empty_output><jupyter_text>The table above shows we have two scans that resulted in 9 'Hits' and 61 'Hops'.  At first glance, we may be quick to assume 9 'Hits' means we ended up at nine different sites.  What about a redirect scenario?  How can we detect if a 'Hit' resulted in a redirect or not?  Below is a list containing all of the 'Hits' we encountered:<jupyter_code>query = """
MATCH (h:Hit)
RETURN h.url as URL, h.ip AS IP
"""

graph.run(query).to_data_frame()<jupyter_output><empty_output><jupyter_text>Knowing we may have some 'Hits' that resulted in redirects, it would be nice to know which ones were the result of a redirect, but did not redirect themselves.<jupyter_code>query = """
MATCH p=(h:Hit)-[r:RESULTED_IN*]->(hh:Hit)
WITH DISTINCT last(nodes(p)) as nodes
RETURN nodes.url as URL, nodes.ip AS IP
"""

graph.run(query).to_data_frame()<jupyter_output><empty_output><jupyter_text>While knowing what websites were the end of a series of 'Hops', we could also find out which 'Hits' result in  redirects.<jupyter_code>query = """
MATCH (:Scan)-[r:RESULTED_IN*]->(h:Hit)-[rr:RESULTED_IN*]->(hh:Hit)
WITH DISTINCT h
RETURN h.url as URL, h.ip AS IP
"""

graph.run(query).to_data_frame()<jupyter_output><empty_output><jupyter_text>But what about the other three 'Hits'?  Why weren't they returned in the last two queries?  Simple: the other 'Hits' are websites that did not redirect or result from a redirect themselves.<jupyter_code>query = """
MATCH p=(s:Scan)-[:RESULTED_IN*1..]->(h:Hit)
WHERE NOT (h)-[]->() AND NOT (:Hit)-[*]->(h)
WITH DISTINCT h
RETURN h.url as URL, h.ip AS IP
"""

graph.run(query).to_data_frame()<jupyter_output><empty_output><jupyter_text>What is we simply wanted to know the number of tests that were run for a single scan?<jupyter_code>query = """
MATCH (s:Scan { id: '1' })-[r:RESULTED_IN]->()
RETURN size(collect(r)) as Test_Count
"""

graph.run(query).to_data_frame()<jupyter_output><empty_output><jupyter_text>Let's try to look at more intereting examples.  What if we wanted to look into the longest path a test took from start to its final 'Hit'?  We could easily write a simple query to find this information out.<jupyter_code>query = """
match p=(n:Scan)-[:RESULTED_IN*1..]->(m:Hit)
WITH relationships(p) as n, length(p) as Length
RETURN n[0].test_id as Test_ID, Length
order by Length desc
limit 3
"""

graph.run(query).to_data_frame()<jupyter_output><empty_output><jupyter_text>Another interesting question we could ask is: What 'Hop' and 'Hit' carry the most relationships?  The following query will let us know what ip addresses and websites are the most connected.<jupyter_code>query = """
MATCH (n)
WHERE n:Hit OR n:Hop
WITH n, (SIZE((n)-[]->()) + SIZE(()-[]->(n))) AS Relationship_Count
RETURN n.ip, n.url, Relationship_Count
ORDER By Relationship_Count DESC
LIMIT 5
"""

graph.run(query).to_data_frame()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12654320987654322, 'fraction_numerical': 0.00360082304526749, 'mean_word_length': 5.636518771331058, 'pattern_counts': {'":': 2, '<': 37, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '539235', 'license': 'MIT', 'n_tokens_mistral': 1301, 'n_tokens_neox': 1264, 'n_words': 445, 'path': '2 - Experimenting and Gathering Information.ipynb', 'repo_name': 'CyberbabbleBlog/jupyter-election-scans-graph-analysis', 'size': '3888'}