{"id":4595,"date":"2025-12-04T11:16:00","date_gmt":"2025-12-04T10:16:00","guid":{"rendered":"https:\/\/www.lieben.nu\/liebensraum\/?p=4595"},"modified":"2025-12-04T11:16:00","modified_gmt":"2025-12-04T10:16:00","slug":"average-device-daily-usage-report-in-powerbi","status":"publish","type":"post","link":"https:\/\/lieben.nu\/liebensraum\/2025\/12\/average-device-daily-usage-report-in-powerbi\/","title":{"rendered":"Average device daily usage report in PowerBI"},"content":{"rendered":"\n
\"\"<\/a><\/figure>\n\n\n\n

A customer wanted to know how many hours on average their devices were being used, without modifying their configuration.<\/p>\n\n\n\n

This to help target replacements, upgrades, #desks per location, #shared devices per location etc.<\/p>\n\n\n\n

At first I figured it’d be a simple task, just getting the right event ID’s from the local eventlog, but no, that required additional auditing to be enabled and how would the data flow to PowerBI?<\/p>\n\n\n\n

The next attempted data source was much better; Defender Advanced Hunting. Especially the DeviceLogonEvents table, which contains cached and interactive logons to all monitored devices. <\/p>\n\n\n\n

Using logon events, we can get an idea of a device’s relative use in the fleet. It won’t be exact as measuring the time between pure lock\/unlock\/signin\/signout events.<\/p>\n\n\n\n

This was fine for the customer’s purpose and checking their data the number of daily logon events (7-9) was actually much higher than the expected 2-3 per device per day (at least on Windows 11, our target OS).<\/p>\n\n\n\n

Getting that data into PowerBI turned out to be even easier \ud83d\ude42<\/p>\n\n\n\n

Option 1: use my PowerBI template<\/a><\/p>\n\n\n\n

Option 2: build your own report using below steps:<\/p>\n\n\n\n

    \n
  1. Open up PowerBI and click Blank Query<\/li>\n<\/ol>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n
      \n
    1. Open the Advanced Editor<\/li>\n<\/ol>\n\n\n\n
      \"\"<\/a><\/figure>\n\n\n\n
        \n
      1. Paste the KQL query and click done<\/li>\n<\/ol>\n\n\n\n
        \"\"<\/a><\/figure>\n\n\n\n

        KQL: <\/strong><\/p>\n\n\n

        \n let\n        AdvancedHuntingQuery = "DeviceLogonEvents\n| where Timestamp between (ago(28d) .. now())\n| where ActionType == ""LogonSuccess""\n| where LogonType == ""Interactive""\n| extend LogonDate = startofday(Timestamp)\n| where LogonDate &lt; startofday(now())  \n| summarize \n    FirstLogon = min(Timestamp), \n    LastLogon = max(Timestamp)\n    by DeviceName, LogonDate\n| extend UsageDuration = LastLogon - FirstLogon\n| project DeviceName, LogonDate, FirstLogon, LastLogon, UsageDuration\n| order by DeviceName asc, LogonDate desc",\n\n        HuntingUrl = "https:\/\/api.security.microsoft.com\/api\/advancedqueries",\n\n        Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery]])),\n\n        TypeMap = #table(\n            { "Type", "PowerBiType" },\n            {\n                { "Double",   Double.Type },\n                { "Int64",    Int64.Type },\n                { "Int32",    Int32.Type },\n                { "Int16",    Int16.Type },\n                { "UInt64",   Number.Type },\n                { "UInt32",   Number.Type },\n                { "UInt16",   Number.Type },\n                { "Byte",     Byte.Type },\n                { "Single",   Single.Type },\n                { "Decimal",  Decimal.Type },\n                { "TimeSpan", Duration.Type },\n                { "DateTime", DateTimeZone.Type },\n                { "String",   Text.Type },\n                { "Boolean",  Logical.Type },\n                { "SByte",    Logical.Type },\n                { "Guid",     Text.Type }\n            }),\n\n        Schema = Table.FromRecords(Response[Schema]),\n        TypedSchema = Table.Join(Table.SelectColumns(Schema, {"Name", "Type"}), {"Type"}, TypeMap , {"Type"}),\n        Results = Response[Results],\n        Rows = Table.FromRecords(Results, Schema[Name]),\n        Table = Table.TransformColumnTypes(Rows, Table.ToList(TypedSchema, (c) => {c{0}, c{2}}))\n\n    in Table\n<\/pre><\/div>\n\n\n
          \n
        1. Edit your credentials and sign in<\/li>\n<\/ol>\n\n\n\n
          \"\"<\/a><\/figure>\n\n\n\n
          \"\"<\/a><\/figure>\n\n\n\n
          \"\"<\/a><\/figure>\n\n\n\n
          \"\"<\/a><\/figure>\n\n\n\n
          Now you’ve got the data connected, and just need to create a table with the fields you want.<\/p>\n\n\n\n
          <\/p>\n","protected":false},"excerpt":{"rendered":"
          A customer wanted to know how many hours on average their devices were being used, without modifying their configuration. This to help target replacements, upgrades, #desks per location, #shared devices per location etc. At first I figured it’d be a simple task, just getting the right event ID’s from the local eventlog, but no, that … Continue reading Average device daily usage report in PowerBI<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,38],"tags":[],"class_list":["post-4595","post","type-post","status-publish","format-standard","hentry","category-defender","category-powerbi"],"_links":{"self":[{"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/posts\/4595","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/comments?post=4595"}],"version-history":[{"count":0,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/posts\/4595\/revisions"}],"wp:attachment":[{"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/media?parent=4595"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/categories?post=4595"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/tags?post=4595"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}