apipowerbigit-bashsumologic

How to integrate data from Sumologic API into Power BI - user-not-found error

Goal:

My goal is to take data from a Sumologic dashboard using the Sumologic API and display it within a dashboard in Power BI. I am checking the calls first within Git Bash, before entering the URLs into Power BI Desktop to visualize the data.

I acquired the dash ID from the end of the URL in my Sumologic UI.

I have successfully accessed data through the API for https://api.sumologic.com/api/v1/collectors?limit=10 using the PBI Desktop UI "Basic" Authentication setting, by substituting the access ID for username and the Access key for password.

(As instructed by the Power BI Documentation on APIs see first few steps. It works, even though this states it is for Azure not SumoLogic)

Problem:

But when I curl the command in my Git Bash for GET dashboard,

curl -u "(AccessID):(AccessKey)" -X GET "https://api.sumologic.com/api/v2/dashboards/(dashboardID)"

I get this json response:

{
    "id":"10NLC-3CNIS-5QN8J",
    "errors":[
        {"code":"user:not_found",
         "message":"Object Not Found."}
    ]
 }

I also am unable to add this call into Power BI, likely because of the same error. I was able to add the first URL https://api.sumologic.com/api/v1/collectors?limit=10 without any extra user info given.

This Error seems to confirm that the ID is correct, (as I used to get an "Incorrect ID" error instead of a json response before), but I'm not sure. How can I resolve this user-not-found error?

Solution

  • 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

    #   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.