The best way to integrate Sumologic into Power BI is to use python scripts that do this curl to get the info needed from Sumologic's SearchJob API.
Step 1: Make Python Script
Replace
- the URL with your sourceName
- From and to times
- query with your query
# IMPORTS
import requests
import json
import base64
import time
import pandas as pd
import pytz
# from async import asyncio
from datetime import datetime, timedelta, date
now = datetime.now()
current_time = now.strftime("%Y-%m-%dT%H:%M:%S")
# VARIABLES
dt = [] # DATA TARGET - List of Data values (Index, Timestamp, Message/Query, Count)
queryList = [] # LIST of Queries
index = 0 # DATA TARGET COUNT - INDEX NumberqueryList = []
n = 1 # SET minutes - n = number of Minutes/Hours 5 min AGO TIME - the "from" value for Query:
messageCount = 0
# Subtract Desired time n from datetime object containing current time
five_min_ago = now - timedelta(minutes=n) # Change time unit here if desired
past_time = five_min_ago.strftime("%Y-%m-%dT%H:%M:%S")
print("CURRENT TIME: ", current_time, " PAST TIME: ", past_time)
def makeCall(queryString): # F U N C T I O N
# CREDENTIALS:
global messageCount
usrPass = "******"
encoded_u = base64.b64encode(usrPass.encode()).decode()
url = "https://api.sumologic.com/api/v1/search/jobs"
fullQuery = '_sourceCategory=prod/../* _sourceName=/apps/apps/logs/../trace.log ' + queryString + " | count" # Form Query with Common Query Parts
print(fullQuery)
headers = {
'Content-Type': 'application/json',
'Authorization': 'Basic '+encoded_u,
'Cookie': '****'
}
payload = json.dumps({
"query": fullQuery,
"from": past_time, # Format: 2021-10-06T11:40:05 -Minute ago
"to": current_time,
"timeZone": "EST",
"byReceiptTime": True
})
# MAKE REQUEST AND GET RESPONSE - (CALL #1): -------------------------------------------------------------------------------------------------------
response = requests.request("POST", url, headers=headers, data=payload)
print("RESPONSE: ", response.text)
# CONTENT EXTRACTION:
jobId = json.loads(response.text).get("id") # Convert Response (text) to json
queryUrl = url + "/" + jobId # Make URL Query
# SPECIFIC SEARCH JOB CALL - (CALL #2): ---------------------------------------------------------------------------------------------------------------
response = requests.request("GET", queryUrl, headers=headers, data=payload)
state = json.loads(response.text).get("state")
print(state)
quitVal = False
while quitVal == False :
time.sleep(5)
resp = requests.request("GET", queryUrl, headers=headers, data=payload)
state = json.loads(resp.text).get("state")
print("...")
if state == "DONE GATHERING RESULTS" :
quitVal = True
print(state)
messageCount = json.loads(resp.text).get("messageCount")
print(messageCount)
# GET DATA CALL - (CALL #3): ---------------------------------------------------------------------------------------------------------------------------
# Form Result URL:
resultUrl = queryUrl + "/messages?offset=0&limit=10000" #records?offset=0&limit=1
results = requests.request("GET", resultUrl, headers=headers, data=payload)
messages = json.loads(results.text).get("messages") # Access Data
return messages
# End of F U N C T I O N
# QUERIES By CATEGORY ----------------------------------------
# Query string you are searching for within the log:
Query1 = "... :Exception"
queryList.append(Query1)
print(queryList) # Print to Check
# ------------------------------------------------------------
# EXECUTE Calls on Each Query - SYNC. = SLOW
for query in queryList:
messages = makeCall(query)
#print(messages)
dt.append((index, past_time,current_time, query, messageCount))
index = index + 1 # Increment the Index of DT for next Query
# CHECK dt:
for each in dt:
print()
print(each)
# ADD data to DataFrame Format for Power BI:
df = pd.DataFrame(dt, columns=('Index', 'FromTime','ToTime', 'Message', 'Messagecount'))
print(df)
Step 2: Add the Python into Power BI
. This just requires you to go to add data. Then select python script. Then copy and past your script into the text entry box. Should automatically calculate when you refresh your pbi dash.
