Atera and Power BI

cjeffers
cjeffers Member Posts: 28 ✭✭✭
in Read before posting

Hey guys,

I have just began playing with the Atera API in Microsoft Power BI. I wanted to create a thread for us to all share ideas, instructions, and projects that use the Atera API specifically in Power BI. My current project is a ticketing wallboard. If you have any ideas/suggestions for the wallboard or ideas for future BI projects feel free to jump in. Also if you are having any issues with Power BI connecting to the Atera API I'd be happy to help out.

Screenshot 2024-01-18 110508.png

Above is what I have started with. This is a side project for work and nothing I am in a rush to get knocked out. I'm just enjoying the learning process and figuring out how it all works and can be used.

Comments

  • adip
    adip Administrator, Internal Posts: 61 admin

    I also wanted to chip in and say this is an awesome idea!

    Thank you @cjeffers 🤩

  • [Deleted User]
    [Deleted User] Posts: 0 ✭

    @cjeffers Thank you very much for your post, I have dropped you a DM as I have had a ticket open with Atera for many months on how to achieve importing the paginated data from the API in to power BI and they could not find anyone who could help with this, so I am really hoping that you can help.

  • AlexYoungNSM
    AlexYoungNSM Member Posts: 21 ✭✭

    Yeah i'm struggling with the pages to be honest, i get 20 records and thats all

  • cjeffers
    cjeffers Member Posts: 28 ✭✭✭
    https://community.atera.com/discussion/comment/1903#Comment_1903

    I am working on a document that will detail how to get all pages. I will DM once complete. Should have done within the next hour.

  • AlexYoungNSM
    AlexYoungNSM Member Posts: 21 ✭✭

    ah fantastic, as i'm going a bit crazy lol probably a dive in the deep end for me as first time using PowerBI :)

  • cjeffers
    cjeffers Member Posts: 28 ✭✭✭

    Attaching the PDF instructional here for anyone else who may need it :)

    Connecting Atera to Power BI.pdf

  • [Deleted User]
    [Deleted User] Posts: 0 ✭

    Big thanks to @cjeffers for that helpful document.

    if you want to query your agents, follow the same instructions except swap tickets? for agents? in the initial connection url and then when you have duplicated the query, replace the text in the advanced editor with this:-

    let

    Source = Json.Document(Web.Contents("https://app.atera.com/api/v3/agents"& "?page=2" & "&itemsInPage=50", [Headers=[Accept="application/json", #"x-api-key"="YourAPIKey"]])),

    totalPages = Source[totalPages],
    Custom1 = List.Numbers(1,totalPages,1),

    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://app.atera.com/api/v3/agents?page="&[Column1] & "&itemsInPage=50", [Headers=[Accept="application/json", #"x-api-key"="YourAPIKey"]]))),
    
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"page", "itemsInPage", "totalPages", "prevLink", "nextLink", "items", "totalItemCount"}, {"Custom.page", "Custom.itemsInPage", "Custom.totalPages", "Custom.prevLink", "Custom.nextLink", "Custom.items", "Custom.totalItemCount"}),
    
#"Expanded Custom.items" = Table.ExpandListColumn(#"Expanded Custom", "Custom.items"),
    
#"Expanded Custom.items1" = Table.ExpandRecordColumn(#"Expanded Custom.items", "Custom.items", {"MachineID", "AgentID", "DeviceGuid", "FolderID", "CustomerID", "CustomerName", "AgentName", "SystemName", "MachineName", "DomainName", "CurrentLoggedUsers", "ComputerDescription", "Monitored", "LastPatchManagementReceived", "AgentVersion", "Favorite", "ThresholdID", "MonitoredAgentID", "Created", "Modified", "Online", "ReportedFromIP", "AppViewUrl", "Motherboard", "Processor", "Memory", "Display", "Sound", "ProcessorCoresCount", "SystemDrive", "ProcessorClock", "Vendor", "VendorSerialNumber", "VendorBrandModel", "ProductName", "MacAddresses", "IpAddresses", "HardwareDisks", "OS", "OSType", "WindowsSerialNumber", "Office", "OfficeSP", "OfficeOEM", "OfficeSerialNumber", "OSNum", "LastRebootTime", "OSVersion", "OSBuild", "OfficeFullVersion", "LastLoginUser"}, {"Custom.items.MachineID", "Custom.items.AgentID", "Custom.items.DeviceGuid", "Custom.items.FolderID", "Custom.items.CustomerID", "Custom.items.CustomerName", "Custom.items.AgentName", "Custom.items.SystemName", "Custom.items.MachineName", "Custom.items.DomainName", "Custom.items.CurrentLoggedUsers", "Custom.items.ComputerDescription", "Custom.items.Monitored", "Custom.items.LastPatchManagementReceived", "Custom.items.AgentVersion", "Custom.items.Favorite", "Custom.items.ThresholdID", "Custom.items.MonitoredAgentID", "Custom.items.Created", "Custom.items.Modified", "Custom.items.Online", "Custom.items.ReportedFromIP", "Custom.items.AppViewUrl", "Custom.items.Motherboard", "Custom.items.Processor", "Custom.items.Memory", "Custom.items.Display", "Custom.items.Sound", "Custom.items.ProcessorCoresCount", "Custom.items.SystemDrive", "Custom.items.ProcessorClock", "Custom.items.Vendor", "Custom.items.VendorSerialNumber", "Custom.items.VendorBrandModel", "Custom.items.ProductName", "Custom.items.MacAddresses", "Custom.items.IpAddresses", "Custom.items.HardwareDisks", "Custom.items.OS", "Custom.items.OSType", "Custom.items.WindowsSerialNumber", "Custom.items.Office", "Custom.items.OfficeSP", "Custom.items.OfficeOEM", "Custom.items.OfficeSerialNumber", "Custom.items.OSNum", "Custom.items.LastRebootTime", "Custom.items.OSVersion", "Custom.items.OSBuild", "Custom.items.OfficeFullVersion", "Custom.items.LastLoginUser"})

    in
    #"Expanded Custom.items1"

  • AlexYoungNSM
    AlexYoungNSM Member Posts: 21 ✭✭

    The one thing i wish with the API, unless someone knows something would to be retrieving the software on agents, not sure why we can't do that, i just get told to put it in the suggestions.

  • cjeffers
    cjeffers Member Posts: 28 ✭✭✭

    @AlexYoungNSM This is one of the features that I have asked for as well with the same response. Hopefully we will be able to pull that data at some point through the API.

  • brent.owens
    brent.owens Member Posts: 2 ✭

    Thanks for the guide. I'm working on setting it up now but I'm getting a time out error when applying the changes. Any advice?

  • cjeffers
    cjeffers Member Posts: 28 ✭✭✭

    @brent.owens I have not seen that error before. Would you mind sharing a screenshot of the error?

  • brent.owens
    brent.owens Member Posts: 2 ✭
    https://community.atera.com/discussion/comment/1962#Comment_1962
    image.png

    It worked when I only tried to load 20 lines, but when I used your guide, it timed out

  • tom.weston
    tom.weston Member Posts: 1 ✭

    Hi guys.

    This is brilliant thanks, Can I use this to generate a report of tickets resolved/closed by technicians in one day?

  • Orinoco360
    Orinoco360 Member Posts: 6 ✭✭

    We are starting to use Power BI, this is a great start. Thank you @cjeffers

Topics