Atera and Power BI

Options
cjeffers
cjeffers Member Posts: 28 ✭✭✭

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.

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 Posts: 61 admin
    Options

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

    Thank you @cjeffers 🤩

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

    @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: 10
    Options

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

  • cjeffers
    cjeffers Member Posts: 28 ✭✭✭
    Options

    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: 10
    Options

    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 ✭✭✭
    Options

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

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

    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: 10
    Options

    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 ✭✭✭
    Options

    @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
    Options

    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 ✭✭✭
    Options

    @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
    Options

    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
    Options

    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 ✭✭
    Options

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