<jupyter_start><jupyter_text>Apache Spot's Ipython Advanced Mode NetflowsThis guide provides examples about how to request data, show data with some cool libraries like pandas and more. **Import Libraries**The next cell will import the necessary libraries to execute the functions. Do not remove<jupyter_code>import datetime
import pandas as pd
import numpy as np
import linecache, bisect
import os

spath = os.getcwd()
path = spath.split("/")
date = path[len(path)-1]<jupyter_output><empty_output><jupyter_text>**Request Data**In order to request data we are using Graphql (a query language for APIs, more info at: http://graphql.org/).We provide the function to make a data request, all you need is a query and variables<jupyter_code>def makeGraphqlRequest(query, variables):
    return GraphQLClient.request(query, variables)<jupyter_output><empty_output><jupyter_text>Now that we have a function, we can run a query like this:*Note: There's no need to manually set the date for the query, by default the code will read the date from the current path<jupyter_code>suspicious_query = """query($date:SpotDateType) {
                            flow {
                              suspicious(date:$date)
                                  {
                                      srcIp
                                      dstIp
                                      srcPort
                                      dstPort
                                      score
                                      srcIp_domain
                                      dstIp_rep
                                      protocol
                                      outBytes
                                      inPkts
                                      srcIp_rep
                                      inBytes
                                      srcIp_isInternal  
                                      rank 
                                      dstIp_geoloc
                                      tstart
                                      outPkts  
                                      dstIp_isInternal
                                      dstIp_domain
                                  }
                            }
                    }"""

##If you want to use a different date for your query, switch the 
##commented/uncommented following lines

variables={
    'date': datetime.datetime.strptime(date, '%Y%m%d').strftime('%Y-%m-%d')
#     'date': "2016-10-08"
    }
 
suspicious_request = makeGraphqlRequest(suspicious_query,variables)

##The variable suspicious_request will contain the resulting data from the query.
results = suspicious_request['data']['flow']['suspicious']<jupyter_output><empty_output><jupyter_text>Pandas DataframesThe following cell loads the results into a pandas dataframeFor more information on how to use pandas, you can learn more here: https://pandas.pydata.org/pandas-docs/stable/10min.html<jupyter_code>df = pd.read_json(json.dumps(results))
##Printing only the selected column list from the dataframe
##By default it will only print the first 15 results
print df[['srcIp','dstIp','srcPort','dstPort','score']]<jupyter_output><empty_output><jupyter_text>Additional operations Additional operations can be performed on the dataframe like sorting the data, filtering it and grouping it**Filtering the data**<jupyter_code>##Filter results where the destination port = 3389
##The resulting data will be stored in df2 

df2 = df[df['dstPort'].isin(['3389'])]
print df2[['srcIp','dstIp','srcPort','dstPort','score']]<jupyter_output><empty_output><jupyter_text>**Ordering the data**<jupyter_code>srtd = df.sort_values(by="rank")
print srtd[['rank','srcIp','dstIp','srcPort','dstPort','score']]<jupyter_output><empty_output><jupyter_text>**Grouping the data**<jupyter_code>## This command will group the results by pairs of source-destination IP
## summarizing all other columns 
grpd = df.groupby(['srcIp','dstIp']).sum()
## This will print the resulting dataframe displaying the input and output bytes columnns
print grpd[["inBytes","inPkts"]]<jupyter_output><empty_output><jupyter_text>**Reset Scored Connections**Uncomment and execute the following cell to reset all scored connections for this day<jupyter_code># reset_scores = """mutation($date:SpotDateType!) {
#                   flow{
#                       resetScoredConnections(date:$date){
#                       success
#                       }
#                   }
#               }"""


# variables={
#     'date': datetime.datetime.strptime(date, '%Y%m%d').strftime('%Y-%m-%d')
#     }
 
# request = makeGraphqlRequest(reset_scores,variables)

# print request['data']['flow']['resetScoredConnections ']['success']<jupyter_output><empty_output><jupyter_text>SandboxAt this point you can perform your own analysis using the previously provided functions as a guide.Happy threat hunting!<jupyter_code>#Your code here<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0902834008097166, 'fraction_numerical': 0.004858299595141701, 'mean_word_length': 2.289613848202397, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 37, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 19, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '537379', 'license': 'Apache-2.0', 'n_tokens_mistral': 1336, 'n_tokens_neox': 1261, 'n_words': 364, 'path': 'spot-oa/oa/flow/ipynb_templates/Advanced_Mode_master.ipynb', 'repo_name': 'jfn6030217/incubator-spot', 'size': '4940'}