Atera and Power BI

cjeffers

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.

Sarah_from_Atera

Hi @cjeffers , Sarah from Atera here.

I love to see our community members sharing valuable insights to help others use the platform in the best way.

Keep up the good work!

adip

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

Thank you @cjeffers 🤩

[Deleted User]

@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

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

cjeffers

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

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

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

Connecting Atera to Power BI.pdf

[Deleted User]

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

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

@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

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

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

brent.owens

https://community.atera.com/discussion/comment/1962#Comment_1962

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

tom.weston

Hi guys.

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

Orinoco360

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