import requests
import pandas
import time
SEC_TOKEN = 'XYZ'
header = {
'SEC':SEC_TOKEN,
'Content-Type':'application/json',
'accept':'application/json'
}
def do_request(method, url, params={}):
r = requests.request(method=method, url=url, params=params, headers=header, verify=False)
return r.json()
def check_status(search_status, search_id):
if search_status=="COMPLETED":
print("Search Completed")
method = "GET"
url = 'https://192.168.56.144/api/ariel/searches/%s/results' % search_id
return do_request(method, url)
else:
print("Waiting for 3 seconds...")
time.sleep(3)
method = "GET"
url = 'https://192.168.56.144/api/ariel/searches/%s' % search_id
resp_json = do_request(method, url)
return check_status(resp_json['status'], search_id)
# Saved Search
url = 'https://192.168.56.144/api/ariel/saved_searches'
params = {'filter':'name="Top Log Sources"'}
type(params)
dict
method = "GET"
res_json = do_request(method, url, params)
res_json
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/urllib3/connectionpool.py:1013: InsecureRequestWarning: Unverified HTTPS request is being made to host '192.168.56.144'. Adding certificate verification is strongly advised. See: https://urllib3.readthedocs.io/en/1.26.x/advanced-usage.html#ssl-warnings warnings.warn(
[{'owner': 'admin', 'is_dashboard': True, 'description': '', 'creation_date': 1245191315681, 'uid': 'SYSTEM-13', 'database': 'EVENTS', 'is_default': False, 'is_quick_search': True, 'name': 'Top Log Sources', 'modified_date': 1622547778276, 'id': 2721, 'is_aggregate': True, 'aql': 'SELECT logsourcename(logSourceId) AS \'Log Source\', UniqueCount("sourceIP") AS \'Source IP (Unique Count)\', UniqueCount("destinationIP") AS \'Destination IP (Unique Count)\', UniqueCount("destinationPort") AS \'Destination Port (Unique Count)\', UniqueCount(qid) AS \'Event Name (Unique Count)\', UniqueCount(category) AS \'Low Level Category (Unique Count)\', UniqueCount("protocolId") AS \'Protocol (Unique Count)\', UniqueCount("userName") AS \'Username (Unique Count)\', MAX("magnitude") AS \'Magnitude (Maximum)\', SUM("eventCount") AS \'Event Count (Sum)\', COUNT(*) AS \'Count\' from events GROUP BY logSourceId order by "Event Count (Sum)" desc last 6 hours', 'is_shared': True}]
type(res_json)
list
len(res_json)
1
SAVED_SEARCH_ID = res_json[0]['id']
SAVED_SEARCH_ID
2721
method = "POST"
url = 'https://192.168.56.144/api/ariel/searches'
params = {'saved_search_id':SAVED_SEARCH_ID}
params
{'saved_search_id': 2721}
res_json = do_request(method, url, params)
res_json
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/urllib3/connectionpool.py:1013: InsecureRequestWarning: Unverified HTTPS request is being made to host '192.168.56.144'. Adding certificate verification is strongly advised. See: https://urllib3.readthedocs.io/en/1.26.x/advanced-usage.html#ssl-warnings warnings.warn(
{'cursor_id': '789355dd-2bb9-454a-9d05-26ba4d373d48', 'status': 'WAIT', 'compressed_data_file_count': 0, 'compressed_data_total_size': 0, 'data_file_count': 0, 'data_total_size': 0, 'index_file_count': 0, 'index_total_size': 0, 'processed_record_count': 0, 'desired_retention_time_msec': 86400000, 'progress': 0, 'progress_details': [], 'query_execution_time': 0, 'query_string': 'SELECT logsourcename(logSourceId) AS \'Log Source\', UniqueCount("sourceIP") AS \'Source IP (Unique Count)\', UniqueCount("destinationIP") AS \'Destination IP (Unique Count)\', UniqueCount("destinationPort") AS \'Destination Port (Unique Count)\', UniqueCount(qid) AS \'Event Name (Unique Count)\', UniqueCount(category) AS \'Low Level Category (Unique Count)\', UniqueCount("protocolId") AS \'Protocol (Unique Count)\', UniqueCount("userName") AS \'Username (Unique Count)\', MAX("magnitude") AS \'Magnitude (Maximum)\', SUM("eventCount") AS \'Event Count (Sum)\', COUNT(*) AS \'Count\' from events GROUP BY logSourceId order by "Event Count (Sum)" desc last 6 hours', 'record_count': 0, 'size_on_disk': 0, 'save_results': False, 'completed': False, 'subsearch_ids': [], 'snapshot': None, 'search_id': '789355dd-2bb9-454a-9d05-26ba4d373d48'}
SEARCH_ID = res_json['search_id']
SEARCH_ID
'789355dd-2bb9-454a-9d05-26ba4d373d48'
resp = check_status("WAIT", SEARCH_ID)
resp
Waiting for 3 seconds... Search Completed
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/urllib3/connectionpool.py:1013: InsecureRequestWarning: Unverified HTTPS request is being made to host '192.168.56.144'. Adding certificate verification is strongly advised. See: https://urllib3.readthedocs.io/en/1.26.x/advanced-usage.html#ssl-warnings warnings.warn( /Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/urllib3/connectionpool.py:1013: InsecureRequestWarning: Unverified HTTPS request is being made to host '192.168.56.144'. Adding certificate verification is strongly advised. See: https://urllib3.readthedocs.io/en/1.26.x/advanced-usage.html#ssl-warnings warnings.warn(
{'events': [{'Log Source': 'Health Metrics-2 :: localhost', 'Source IP (Unique Count)': 1.0, 'Destination IP (Unique Count)': 1.0, 'Destination Port (Unique Count)': 1.0, 'Event Name (Unique Count)': 1.0, 'Low Level Category (Unique Count)': 1.0, 'Protocol (Unique Count)': 1.0, 'Username (Unique Count)': 0.0, 'Magnitude (Maximum)': 5.0, 'Event Count (Sum)': 113760.0, 'Count': 113760.0}, {'Log Source': 'System Notification-2 :: qradar', 'Source IP (Unique Count)': 2.0, 'Destination IP (Unique Count)': 1.0, 'Destination Port (Unique Count)': 1.0, 'Event Name (Unique Count)': 4.0, 'Low Level Category (Unique Count)': 3.0, 'Protocol (Unique Count)': 1.0, 'Username (Unique Count)': 0.0, 'Magnitude (Maximum)': 7.0, 'Event Count (Sum)': 23362.0, 'Count': 23362.0}, {'Log Source': 'SIM Audit-2 :: qradar', 'Source IP (Unique Count)': 3.0, 'Destination IP (Unique Count)': 1.0, 'Destination Port (Unique Count)': 1.0, 'Event Name (Unique Count)': 7.0, 'Low Level Category (Unique Count)': 2.0, 'Protocol (Unique Count)': 1.0, 'Username (Unique Count)': 4.0, 'Magnitude (Maximum)': 8.0, 'Event Count (Sum)': 146.0, 'Count': 146.0}, {'Log Source': 'Anomaly Detection Engine-2 :: qradar', 'Source IP (Unique Count)': 1.0, 'Destination IP (Unique Count)': 1.0, 'Destination Port (Unique Count)': 1.0, 'Event Name (Unique Count)': 1.0, 'Low Level Category (Unique Count)': 1.0, 'Protocol (Unique Count)': 1.0, 'Username (Unique Count)': 0.0, 'Magnitude (Maximum)': 3.0, 'Event Count (Sum)': 18.0, 'Count': 18.0}]}
type(resp)
dict
type(resp['events'])
list
len(resp['events'])
4
df = pandas.json_normalize(resp['events'])
type(df)
pandas.core.frame.DataFrame
df
Log Source | Source IP (Unique Count) | Destination IP (Unique Count) | Destination Port (Unique Count) | Event Name (Unique Count) | Low Level Category (Unique Count) | Protocol (Unique Count) | Username (Unique Count) | Magnitude (Maximum) | Event Count (Sum) | Count | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | Health Metrics-2 :: localhost | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.0 | 5.0 | 113760.0 | 113760.0 |
1 | System Notification-2 :: qradar | 2.0 | 1.0 | 1.0 | 4.0 | 3.0 | 1.0 | 0.0 | 7.0 | 23362.0 | 23362.0 |
2 | SIM Audit-2 :: qradar | 3.0 | 1.0 | 1.0 | 7.0 | 2.0 | 1.0 | 4.0 | 8.0 | 146.0 | 146.0 |
3 | Anomaly Detection Engine-2 :: qradar | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.0 | 3.0 | 18.0 | 18.0 |
df.shape
(4, 11)