Starting with Log Analytics: Part 3 - Creating our first workbook

In this post, we will continue to our Log Analytics series and create our workbook using datas we have imported through Intune and PowerShell.

Other articles

- Part 1: Creating our first Log Analytics workspace

- Part 2: Importing your own datas into the workspace

- Part 3: Creating our first workbook (you are here) 

- Part 4: Add Intune datas into Log Analytics workspace

- Part 5: RunningKQL queries in Log Analytics through PowerShell

Imported datas

In the pevious post, we have:

-1. Created a PowerShell script to get some information about devices

2. Created a proactive remediation package to run script on devices

3. Uploaded information to Log Analytics through Intune 

Informations we have uploaded are the following:

- Device name

- User name

- Device model

- BIOS version

- Disk size

- Free disk space

- Free disk spec percntage

- Bitlocker status

All those information have been imported in the Custom Log: TestReport_CL

Our workbook

Now we have imported our information into Log Analytics, it's time to gather them into a report.

In this dashboard, we want below things:

- A Pie chart with number of devices with or without Bitlocker

- A Pie chart with number of devices per model

- A table with all information

Furthermore, in this table we want:

- Add a warning icon if Bitlocker is disabled

- Change cell background to green if free disk space percent is > 20%

- Change cell background to orange if free disk space percent is < 20%

Get sources

Click on the below picture to get the workbook JSON file

Manually creating the workbook

In this part, we will create our workbook step by step.

You can also use the JSON from sources.

We will proceed as below:

1. Go to your Log Analytics workspace

2. Go to Workbooks

3. Click on New

4. Click on Save

5. Type a name

Creating queries

Queries will help us to create table, pie chart...

In our workbook example we will below things using information we get:

- A Pie chart displaying device with Bitlocker enabled or not

- A Pie chart displaying number of devices per model

- A table displaying all information from the table QuickDeviceReport_CL

Pie chart: Bitlocker enabled or not

Creating the query

1. Click on Add query

2. Add the below query:

3. see overview below:

3. In the Visualization combobox choose Pie chart:

4. Click on Run query

5. In Size, select Tiny

6. Go to Advanced settings

7. Go to Chart title

8. Type a title like: Bitlocker status

9. Go to Style

10. Check Show border around content

11. Click on Done editing

12. See below the result:

Understanding the query

In this query we're checking the custom logs previously created using the table TestReport_CL.

We will count device depending of the LocalAdminStatus_s as below:

Changing Pie color

In this example we want to set color as below:

- Devices with Bitlocker enabled: green

- Devices with Bitlocker disabled: red

1. Edit the query

2. Go to Chart settings

3. Go to Series Settings

4. We will configured it as below:

5. Click on Save and Close

6. See below result

Pie chart: devices per model

Creating the query

1. Click on Add query

2. Add the below query:

3. See below overview:

3. In the Visualization combobox choose Pie chart:

4. In Size, select Tiny

5. Click on Run query

6. Go to Advanced settings

7. Go to Chart title

8. Type a title like: Devices per model

9. Go to Style

10. Check Show border around content

11. Click on Done editing

12. See below the result:

Understanding the query

In this query we're checking the custom logs previously created using the table QuickDeviceReport_CL.

We will count device depending of the LocalAdminStatus_s as below:

Creating a group

Now we have our 2 PIe charts, we want to gather them into a group to display them side by side.

1. In Bitlocker status, click on Edit

2. Go to Style

3. Check Make this item a custom width

4. Click on Move > Move into group

5. Click on OK

6. Click on Done editing

7. In the part Devices per model, click on Edit

8. Go to dans Style

9. Check Make this item a custom width

10. Click on Move > Move into group

11. See below result:

Table with all information

Creating the query

1. Click on Add query

2. Add the below query:

3. See below result:

3. Go to Advanced settings

4. Go to Chart title

5. Type a title like: Devices information

6. Go to Style

7. Check Show border around content

8. Click on Done editing

9. See below the result:

Understanding the query

In this query we're checking the custom logs previously created using the table TestReport_CL.

Adding icon an color

In this query we want to 

- Add a warning icon if Bitlocker is disabled

- Add an green color to cell from column Free disk percent if it's greater than 20%

- Add an orange color to cell from column Free disk percent if it's lesser than 20%

Bitlocker status

1. Edit the query

2. Click on Column Settings

3. Click on Bitlocker status

4. In Column renderer select Thresholds

5. Check Custom formating

6. Go to Thresholds Settings 

7. Change icons for default to Warning

8. Add a new one as below:

- Operator: ==

- Value: Enabled

- Icons: Success

9. Click on Save and Close

10. See below the result:

11. Click again on Column Settings

12. Select Disk size

13. Check Custom formating

14. In Units, select Megabytes

15. Select Free space

16. Check Custom formating

17. In Units, select Megabytes

18. Click on Save and Close

19. See below the result:

Free disk percent

1. Select Free space

2. Check Custom formating

3. In Units, select Percentage

4. In Column renderer select Thresholds

5. Go to Thresholds Settings 

6. Select Colors

7. Change default color to None

8. Add a new one as below:

- Operator: <

- Value: 20

- Color: Red(Bright)

9. Add a new one as below:

- Operator: >

- Value: 20

- Color: Green(Bright)

10. See below the result:

11. Click on Done Editing

12. Click on Save and Close

13. See below the result: