# Automation

While the Security Metrics Toolkit is designed for measurements to be manually updated in the Excel Template, the architecture of the toolkit allows for automating measurements as the program matures. 

## Data Format

Metric measurements must be in the same format as the Log sheet. *Metric ID, Value, and Measurement Date* are required fields, while *Notes* is optional. Below is an example of the format:

| Metric ID | Value | Measurement Date | Notes        |
| --------- | ----- | ---------------- | ------------ |
| M-01      | 0.8   | 1/1/2025         | Sample notes |
| M-01      | 0.85  | 2/1/2025         | Sample notes |
| M-02      | 2300  | 1/15/2025        | Sample notes |

## Metric Inventory Sheet

A metric must have both a record in the Metric Inventory as well as the Log. Ensure any Metric ID you are automating log measurements for has a corresponding record in the [Metric Inventory Sheet](/security-metrics-toolkit/powerbi-template/metric-inventory.md) of the Excel Template.

## Common Data Connectors

Data can be ingested into PowerBI from a variety of datasources. For the full list, see the [Microsoft Power Query Connector Documentation.](https://learn.microsoft.com/en-us/power-query/connectors/)  Below are some of the most common:

* Web APIs
  * [Connector: Web](https://learn.microsoft.com/en-us/power-query/connectors/web/web)
* Databases
  * SQL Server
  * Oracle
  * MySQL
  * PostgreSQL
  * Google BigQuery
  * Snowflake
  * Amazon Athena
* Azure
  * Azure Blob Storage
  * Azure Table Storage
  * Azure Cosmos DB
  * Azure Datalake Storage Gen2
  * Azure Databricks
* OData
* ODBC
* Micrososft Fabric
* Flat files
  * CSV
  * JSON
  * XML
  * Parquet

## Ingesting Data

It's recommended to build the above data format in the source system wherever possible, such as a database or data lake. 

To ingest new data into the PowerBI template, click "Get Data" and select the data connector you wish the use. 

{% embed url="<https://www.loom.com/share/91a1b7e6b169409781fbe25d85b57417>" %}

## Extract, Transform, & Load (ETL)

After connecting to your data source, click "transform" to use Power Query to format the data. Ensure the column names are properly named and you have the required columns.&#x20;

1\) If the table did not automatically set your headers, do so by clicking "Use First Row as Headers".

{% embed url="<https://www.loom.com/share/bf7137abd3bd402284efa2615430f33e>" %}

2\) If the table did not automatically set the Measurement Date to type Date, do so by right-clicking on the column > "Change Type" > "Date".&#x20;

{% embed url="<https://www.loom.com/share/2cd2643f2ce34f7788814a85bb09b3cd>" %}

3\) If the table did not automatically set the Value to type Decimal, do so by right-clicking on the column > "Change Type" > "Decimal Number".

{% embed url="<https://www.loom.com/share/db9c73849b244cd2abb7e12538338f70>" %}

4\) Set the name of the query.

{% embed url="<https://www.loom.com/share/12113b5e5e714c04afae45027ee73d28>" %}

We can now see we have 3 data queries and a parameter.

<figure><img src="/files/2IhkUbevuQGzOSsE6c5B" alt=""><figcaption></figcaption></figure>

You can ingest measurement log data from multiple systems in different queries. In order to integrate the new queries into the data model, we need to append the data into the Log table.&#x20;

5\) Click on the "Log" query, and then click "Append Queries" > "Append Queries". Do not append the data as a new query.&#x20;

Select the tables to append to the Log table, depending on how many queries you are pulling in.&#x20;

{% embed url="<https://www.loom.com/share/e5ce686ba6844cdc94b4bb7e53ec7437>" %}

6\) Click on "Close and Apply"

{% embed url="<https://www.loom.com/share/e9797d78908b4cccad8ebe413d458962>" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.securemetrics.io/security-metrics-toolkit/automation.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.