Jump to content

PowerBI Dashboard


Recommended Posts

  • 3 weeks later...

I'm trying to create one report in Power BI Desktop that shows the following info for our security team. How can I do this:

 

  • Hostname
  • IP Addresses
  • MAC addresses
  • Type (router, switch, server, et cetera)
  • TCP/UDP services along with ARP and ICMP info
  • OS (+version/build number)
  • List of applications installed per device
  • Basic Hardware information (physical/virtual, attached storage, cpu, NIC state, et cetera)
Link to post
Share on other sites
MichaelB.

Hi Alerts,

 

you can retrieve the informations about the Hostname, IP Addresses, MAC addresses, Type, and TCP/UDP services following these steps.

 

1. Import the listDeviceBaseTypes and listDevices (you must specify the agent id here):

2.thumb.png.3a299503be8d6f55874b37c42ce2ccac.png

 

2. you need to transform the imported list of devices in a table (clicking on the header) and then expand to columns (please, remember to click on "Load more" before expanding the columns)

 

3.thumb.png.1dc06fb84fc1455c47c2a918e5091315.png

4.thumb.png.069174541d657ec195d0447ac72cd654.png

 

5.thumb.png.47db7393291091410359f4b9536df87a.png

 

3. you need to expand to column the fields "user_data", "type", and "names" (in the latter you find the names of each device: host, bonjour, upnp, dhcp, snmp, and inspection)

 

6-1.png.f691434c52eed9fac59a866eeb95ae93.png

6-2.png.f2b540e6d08fb21707be87a94a4b36d7.png

6-3.png.77b08b4061758347f16bd183eff10658.png

 

4. then you need to go in the section "Add Column" and click on "Custom Column" for the actual device type: 

= if [Column1.user_data.type] <> null then [Column1.user_data.type] else [Column1.type.id]

8.thumb.png.36c45f3b0a8a5af8b68f86210fa674a7.png

5. in the section "Home", under "Combine", select "Merge Queries": you need to select the listDeviceBaseTypes highlighting the newly added field and the field "id" specifying the Join Kind = Left Outer

 

10.thumb.png.f9ac93be3a2a6d3302a94ab6511f7037.png

 

6. finally you need to expand the listDeviceBaseTypes

 

11.png.1f54d354ca003211dbf4abf75cefa772.png

 

 

To add the IP Addresses you need to do the "Extract Values..." on the field "ip_addresses" and then to eventually do the "Replace Errors" (I suggest with blank values)

 

12.png.3a71ab760c586668fd5cb882d3cc4459.png

13.png.9856743a446b418d8bad8494d8b1253e.png

 

 

TCP/UDP services can be retrieved by doing the "Expand to New Rows" to the field "ip_ports" and then expanding the result to columns (please, note that this will increase the cardinality of the table)

 

14.png.3490efefdb78f69668626de13a17226e.png

15.png.ec4a978266b8f87ee104076e26dbb7c6.png

 

Finally, the MAC address of each device is in the column "hw_address".

 

For the other points of your request, at this time it is not possible to be retrieved in Power BI.

 

I hope this help.

 

Best,

Michael

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...