<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Apache Spot's Ipython Advanced Mode
DNS
This 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
Step1:  Request Data
In order to request data we are using Graphql (a query language for APIs, more info at
Step3:  Now that we have a function, we can run a query like this
Step4:  Pandas Dataframes
The following cell loads the results into a pandas dataframe
For more information on how to use pandas, you can learn more here
Step5:  Additional operations
Additional operations can be performed on the dataframe like sorting the data, filtering it and grouping it
Filtering the data
Step6:  Ordering the data
Step7:  Grouping the data
Step9:  Reset Scored Connections
Uncomment and execute the following cell to reset all scored connections for this day
Step10:  Sandbox
At this point you can perform your own analysis using the previously provided functions as a guide.
Happy threat hunting!
<ASSISTANT_TASK:>
Python 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]
Explanation: Apache Spot's Ipython Advanced Mode
DNS
This 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
End of explanation
def makeGraphqlRequest(query, variables):
    return GraphQLClient.request(query, variables)
Explanation: 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
End of explanation
suspicious_query = query($date:SpotDateType) {
                            dns {
                              suspicious(date:$date)
                                  { clientIp
                                    clientIpSev
                                    dnsQuery
                                    dnsQueryClass
                                    dnsQueryClassLabel
                                    dnsQueryRcode
                                    dnsQueryRcodeLabel
                                    dnsQueryRep
                                    dnsQuerySev
                                    dnsQueryType
                                    dnsQueryTypeLabel
                                    frameLength
                                    frameTime
                                    networkContext
                                    score
                                    tld
                                    unixTimestamp                                 
                                  }
                            }
                    }
##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']['dns']['suspicious']
Explanation: 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
End of explanation
df = pd.read_json(json.dumps(results))
##Printing only the selected column list from the dataframe
##Unless specified otherwise, 
print df[['clientIp', 'unixTimestamp','tld', 'dnsQuery','dnsQueryRcode','dnsQueryRcodeLabel']]
Explanation: Pandas Dataframes
The following cell loads the results into a pandas dataframe
For more information on how to use pandas, you can learn more here: https://pandas.pydata.org/pandas-docs/stable/10min.html
End of explanation
##Filter results where the destination port = 3389
##The resulting data will be stored in df2 
df2 = df[df['tld'].isin(['sjc04-login.dotomi.com'])]
print df2[['clientIp', 'unixTimestamp','tld', 'dnsQuery','dnsQueryRcode','dnsQueryRcodeLabel']]
Explanation: Additional operations
Additional operations can be performed on the dataframe like sorting the data, filtering it and grouping it
Filtering the data
End of explanation
srtd = df.sort_values(by="tld")
print srtd[['clientIp', 'unixTimestamp','tld', 'dnsQuery','dnsQueryRcode','dnsQueryRcodeLabel']]
Explanation: Ordering the data
End of explanation
## This command will group the results by pairs of source-destination IP
## summarizing all other columns 
grpd = df.groupby(['clientIp','tld']).count()
## This will print the resulting dataframe displaying the input and output bytes columnns
print grpd[["dnsQuery"]]
Explanation: Grouping the data
End of explanation
# reset_scores = mutation($date:SpotDateType!) {
#                   dns{
#                       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']['dns']['resetScoredConnections']['success']
Explanation: Reset Scored Connections
Uncomment and execute the following cell to reset all scored connections for this day
End of explanation
#Your code here
Explanation: Sandbox
At this point you can perform your own analysis using the previously provided functions as a guide.
Happy threat hunting!
End of explanation
<END_TASK>