To create terms dynamically in a TermSet and set them in a Managed Metadata column using Power Automate

Introduction: 

In this blog post, we will learn how to dynamically create Terms in a TermSet of a Termstore and set them in a managed metadata column using Microsoft Power Automate.

Requirement:

To obtain information about Skills and Past Projects from the Office 365 user profile and add it to a SharePoint list, we will enable Out-of-the-Box (OOTB) filtering and sorting operations on these fields.

We can use Single line of text OR Multiline of text site column and store information with delimiters, but limitation was, can’t perform OOTB filtering and sorting operation.

To overcome this issue, we are going to use a Managed Metadata site column to store the terms values dynamically. This approach will allow us to perform Out-of-the-Box (OOTB) filtering and sorting operations on the information, providing a more effective solution compared to using Single line of text or Multiline of text site columns with delimiters.

Approach:

Create “Managed Metadata field” with “Allow multiple values” checkbox enabled and “Customize your term set” option selected as shown in below screenshot.

Below are the steps in Power Automate to dynamically create Terms in a TermSet of a Termstore and then create an item in a SharePoint list with those terms in a Managed Metadata field.

Step 1: Add an action “Initialize Variable”, Name it as “Initialize variable Skills” as shown in below screenshot. This will be used to store JSON for use in SharePoint list Item creation.

 

Step 2: Add “Get user profile (V2)” action with user principal name or Email ID in “User (UPN)” as shown below.

 

Step 3: Append value to “Skills” variable as shown in below screenshot.

 Step 4: Add below set of actions to process each element in “Skills” array (which we retrieved in Step-2).

 

Step 5: Then we need to add an action called “Send an HTTP request to SharePoint” to retrieve terms from TermSet.

Uri - _api/v2.1/termStore/groups/97ac8ae5-1608-4047-8951-585b3a2640c5/sets/e0af947c-6cef-4766-8c79-9c454cc5a323/children.

In Uri, “97ac8ae5-1608-4047-8951-585b3a2640c5” is Termstore ID and “e0af947c-6cef-4766-8c79-9c454cc5a323” is TermSet ID in which we need to store or create terms.

 

Step 6: To compare the current item in the loop with the term name, we need to filter the values retrieved in Step-5 to check if they are "equal to" the term name.

string (item ()? ['labels'][0] ['name']) expression is used in comparison as shown in below screenshot.

 

Step 7: Add the following set of actions to form JSON or create JSON data for the "Skills" variable. The condition, as shown in the below screenshot, checks whether the term is present in the "Skills" TermSet or not, using the expression length(body('Filter_skills_array')). If the term is not present, we are adding a POST call to create a new term and then appending the values to the "Skills" variable. On the other hand, if the term is already present, we simply append the values to the "Skills" variable.

 

Step 8: If the condition is "Yes," indicating that the term is present in the "Skills" TermSet of the Termstore, then we simply append the values to the "Skills" variable.

Note: Value will be appended in JSON format as - 

{

“value”: “Sharepoint|3198df72-311b-4757-9246-052296af2”

}

Where, the expression in below screenshot consists of “{current term name in loop}|body('Filter_skills_array')[0]['id']”

 

Step 9: If the condition is "No," meaning that the term is not present in the "Skills" TermSet of the Termstore, in such a case, we need to create the term using a POST call with the action called "Send an HTTP request to SharePoint," as shown in the below screenshot.

Uri - _api/v2.1/termStore/groups/97ac8ae5-1608-4047-8951-585b3a2640c5/sets/e0af947c-6cef-4766-8c79-9c454cc5a323/children.

In Uri, “97ac8ae5-1608-4047-8951-585b3a2640c5” is Termstore ID and “e0af947c-6cef-4766-8c79-9c454cc5a323” is TermSet ID in which we need to store or create terms.

Step 10: Add “Compose” action to fetch and store term ID from Step-9, as shown below.

 

Step 11: After that, append the JSON value, as shown in the below screenshot, with the term name and ID from the "Step-10" compose action.

Step 12: Add the below action after completing the Condition and loop actions to append the closing of JSON in the "Skills" variable.

 

Step 13: Add the below action to parse the JSON, enabling us to create and store the data in a SharePoint list item.

Schema: {

    "type": "array",

    "items": {

        "type": "object",

        "properties": {

            "Value": {

                "type": "string"

            }

        },

        "required": [

            "Value"

        ]

    }

}

 

Step 14: Use Step-13 parsed JSON in “Create Item” action as shown in below screenshot.

 

Finally, we can see that terms have been added to the "Skills" TermSet using Power Automate flow.

 

Also, terms have been added to the "Skills" Managed Metadata field in the SharePoint list as well.

 

Additionally, we can view the "Skills" managed metadata field from the list settings, where we have dynamically appended terms using Power Automate

If you have any questions you can reach out our SharePoint Consulting team here.

Building an Effective Monitoring Stack for Docker Containers: Exploring Grafana, Prometheus, and cAdvisor

INTRODUCTION: 

In this blog post, we will delve into the world of container monitoring and explore the essential components of building a robust monitoring stack for Docker containers. Our focus will be on the Grafana Monitoring Stack, a powerful combination of tools and techniques that enable us to gain deep insights into our containerized applications' performance, health, and security. By the end of this article, you'll have the knowledge and tools necessary to ensure the stability and optimal performance of your Dockerized environments.

To accomplish this, we will concentrate on three key tools: Grafana, Prometheus, and cAdvisor. When combined, these tools offer a complete and scalable solution for monitoring Docker containers.

Grafana, an open-source visualization and analytics platform, plays a pivotal role in our monitoring stack. It empowers us to create customizable dashboards, allowing us to monitor and analyze data from various sources effortlessly.

Prometheus, another open-source tool, excels at collecting and storing time-series data. With its powerful monitoring and alerting capabilities, Prometheus enables us to effectively track and respond to changes in our containerized environments.

Completing our monitoring stack is cAdvisor (Container Advisor), an open-source tool developed by Google that provides detailed insights into individual containers' resource usage and performance characteristics. By leveraging cAdvisor, we can closely monitor container-level metrics and diagnose any potential bottlenecks or issues.

Through the combination of Grafana, Prometheus, and cAdvisor, we can establish a comprehensive monitoring solution that empowers us to make informed decisions and ensure the smooth operation of our containerized applications.

PROBLEM STATEMENT:

As we delve into the implementation of this monitoring stack, it is crucial to understand the challenges that necessitate its adoption. Let's examine some of the key difficulties I encountered when monitoring Docker containers: 

1. Lack of Visibility: Docker containers introduce an additional layer of abstraction, making it challenging to gain comprehensive visibility into the health and performance of individual containers. This lack of visibility hinders effective monitoring and can lead to difficulties in identifying and resolving issues promptly. 
2. Resource Bottlenecks: Without proper monitoring, it becomes challenging to identify resource bottlenecks within the Docker environment. This can result in degraded application performance, response time delays, and even container crashes. Effective monitoring is essential to detect and address these bottlenecks proactively.
3. Reactive Troubleshooting: Relying solely on reactive troubleshooting can be time-consuming and disruptive. Reactively addressing issues can lead to performance degradation and security breaches in the Docker environment. A proactive monitoring approach is crucial for early detection and timely resolution of problems.
4. Security and Compliance: Monitoring Docker containers goes beyond performance optimization; it is also vital for ensuring the security and compliance of your applications. Effective monitoring helps detect vulnerabilities, unauthorized access attempts, and adherence to compliance standards, safeguarding your Docker environment. 
   
   

SOLUTION:

By implementing a monitoring stack with Grafana, Prometheus, and cAdvisor, we can overcome the challenges mentioned earlier and gain comprehensive insights into our Docker containers. 

Installing Grafana:  

1. Prepare the RHEL (Linux) Operating System with the latest packages. Ensure Docker and Docker Compose are pre-installed for this setup. 

2. Create a directory for Grafana configuration: mkdir grafana

3. Navigate to the directory: cd grafana

4. Create a file named docker-compose.yml and open it for editing: nano docker-compose.yml

5. Paste the following contents into the file:

version: '3'
services:
  grafana:
    image: grafana/grafana-enterprise
    container_name: grafana
    restart: unless-stopped
    ports:
      - '3000:3000'
    volumes:
      - 'grafana_storage:/var/lib/grafana'
volumes:
  grafana_storage: {}

6. Save the file and Start the Grafana container: docker-compose up -d

7. Access Grafana in your web browser using the server's IP address or domain name and port 3000 (e.g., http://your-server-ip:3000).

8. Create a new dashboard and set up the desired metrics and alerts for your Docker containers. This requires the installation of Prometheus and cAdvisor. 

Installing Prometheus:  

1. Create a directory for Prometheus configuration: mkdir prometheus

2. Navigate to the directory: cd prometheus

3. Create a file named docker-compose.yml and open it for editing: nano docker-compose.yml. Paste the following contents into that file:

version: '3'
services:
  prometheus:
    image: prom/prometheus
    ports:
      - 9090:9090
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
      - ./data:/prometheus

4. Create another file named prometheus.yml and open it for editing: nano prometheus.yml. Paste the following contents into that file:

global:
  scrape_interval: 15s
scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

5. Once we have saved both the above YAML files, we will create a Prometheus service file for RHEL (Red Hat Enterprise Linux) operating system. The below contents should be pasted in the service file and saved in /etc/systemd/system/prometheus.service location. 

[Unit]
Description=Prometheus Server
Documentation=https://prometheus.io/docs/introduction/overview/
After=network.target

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/prometheus --config.file=/etc/prometheus/prometheus.yml --storage.tsdb.path=/var/lib/prometheus/data
ExecReload=/bin/kill -HUP $MAINPID
Restart=always

[Install]
WantedBy=multi-user.target

6. Once we have these files in place, we will start the Prometheus container: docker-compose up -d, and we will execute the below commands for the changes to take effect. 

sudo systemctl daemon-reload          # Reload systemd after modifying the service file

sudo systemctl start prometheus       # Start Prometheus service

sudo systemctl enable prometheus      # Enable Prometheus to start on boot

sudo systemctl status prometheus      # Check the status of Prometheus service

7. The status command will give the active indicator in green. 

8. Verify that Prometheus is running by accessing the Prometheus UI. Open a web browser and visit http://<your_server_ip>:9090. We should see the Prometheus UI, indicating that Prometheus is up and running.

Installing cAdvisor: 

1. Use the following docker command to install and configure cAdvisor:

docker run \

--volume=/:/rootfs:ro \

--volume=/var/run:/var/run:rw \

--volume=/sys:/sys:ro \

--volume=/var/lib/docker/:/var/lib/docker:ro \

--volume=/dev/disk/:/dev/disk:ro \

--device=/dev/kmsg \

--publish=8080:8080 \

--detach=true \

--name=cadvisor \

google/cadvisor:latest

Running the above command will launch cAdvisor as a Docker container in a detached mode with port as 8080. 

2. Verify that cAdvisor is running by accessing the cAdvisor UI. Open a web browser and visit http://<your_server_ip>:8080. We should see the cAdvisor UI, indicating that cAdvisor is up and running.

Integrating Grafana, Prometheus and cAdvisor to make them work as an effective Monitoring Stack: 

1. In Grafana, add Prometheus as a data source using the URL where Prometheus is running, because of which the monitoring of Docker containers can start effectively. 

2. For adding this, Navigate to Home > Connections > Data sources > Prometheus. Once added, we will have it in the list of data sources. 

3. Then, we need to import pre-built Grafana dashboards for Docker and cAdvisor metrics from the Grafana community and create a custom dashboard based on our monitoring needs, which for me included the memory usage, CPU usage, Network I/O and container counts. Below are the default data sources that need to be imported. 

4. So, once we have the data sources sorted and cAdvisor running alongside Prometheus as configured in the above steps, we will have a custom dashboard with all the live information with a refresh interval of 5 seconds to 60 minutes, which can be customized based on requirements. 

5. Below are the dashboard details for different live information received from Docker containers, where each line color represents a different container.

Memory usage per container: 

CPU Usage per container: 

Network I/O per container: 

6. Now we have the monitoring stack setup complete, we can also configure alerts via Alert Manager with this Grafana Web UI. Alert Manager is an open-source component that handles and manages alerts generated by Prometheus. It provides advanced features for deduplicating, grouping, and routing alerts to different receivers, such as email, PagerDuty, or other custom integrations. 

CONCLUSION:

In conclusion, implementing a monitoring stack consisting of Grafana, Prometheus, and cAdvisor for Docker containers is crucial for ensuring the stability and performance of our applications. This powerful combination allows us to gain comprehensive visibility into resource utilization, track performance metrics, and set up proactive alerts for potential issues. Hope this helps!

If you have any questions you can reach out our SharePoint Consulting team here.

Excel File Creation: Copying Header Structure from Existing Files

Introduction:

In this blog, we will learn how to create an Excel file based on the structure of an existing Excel file, without content, using Power Automate Flow. We will create a flow that generates a new Excel file inheriting the header structure from the parent file. Regardless of the number of columns or the specific headers in the parent Excel file, our automated flow will replicate the same column names and structure in the new Excel file.

Problem:

We encountered a scenario where we needed to create a separate Excel file with only the header structure from an existing file containing data. Unfortunately, Power Automate's Excel connector does not provide an out-of-the-box action to duplicate or copy a file structure.

Solution:

To address this challenge, we have developed a Power Automate flow that operates on an Excel file and generates another Excel file with only the header structure. These steps are applicable to any type of Excel file, regardless of the header structure. Hence, we can pass a dynamic header structure to the output file. Let's dive into the steps to achieve this:

Step 1:

First, we add the "For a selected file" trigger to initiate our flow. This trigger allows us to activate the flow when a specific file or item is selected within a SharePoint list or library.                               

Step 2:

In this step, we use the "Get table" action to fetch the table name from the existing parent Excel file. By using an expression to retrieve the file name dynamically, we ensure that the "Get table" action fetches the table name from the correct parent Excel file.                        

Step 3:

Next, we include the "List rows present in a table" action to fetch all the data from the Excel table. This action retrieves all the rows present within the specified table in the Excel file. We can dynamically set the "Table" value by retrieving the 'id' value from the first table obtained in the response of the "Get tables" action.

 first(outputs('Get_tables')?['body/value'])['id']  

Step 4:

We will add the "Compose" action and include the following expression:

 first(outputs('List_rows_present_in_a_table')?['body/value'])['id']  

This expression retrieves the first row of data from the output of the "List rows present in a table" action. By capturing the data from the first row using this expression within the "Compose" action, we store it for further processing.

 

Step 5:

Once we have obtained the output from the "Compose" action, we notice that it includes some unwanted properties such as "@odata.etag" and "ItemInternalId" above the actual data rows. To extract only the column names from the JSON object, we need to remove these unwanted properties.                                

To achieve this, we use the "Select" function, which allows us to manipulate the JSON object and filter out the unwanted properties to easily extract the desired column names.

The "From" value should be set to the following expression:

 json(replace(string(outputs('Compose_5')), '@odata.', 'odata'))  

Insert the following expression as the input value:

 removeProperty(removeProperty(item(), 'ItemInternalId'), 'odataetag')  

This expression removes the "ItemInternalId" and "odataetag" properties from each item in the JSON array.                                 

This is how we have obtained the desired outcomes using this select action:                              

Step 6:

Clicking on the "Next step" button, we choose the "Create CSV file" action. In the "From" field of the "Create CSV file" action, we add the output of the previous "Select" action. This ensures that we capture the modified JSON object with the desired column names and associated values.                            

Here, this is the expected output that we have obtained from the above action:                            

Step 7:

In this step, we utilize the "Compose" action to process the CSV table created in the previous step. Add the following expression in the "Inputs" field of the "Compose" action:

 first(split(body('Create_CSV_table'), decodeUriComponent('%0D%0A')))  

This expression splits the body of the "Create_CSV_table" action by the line break ("%0D%0A") and retrieves the first element. This allows us to isolate the header row of the CSV file, which consists of the column names. This allows us to isolate the header row of the CSV file, which consists of the column names.

Here, we have obtained the desired output, which includes the column names that will be utilized in our "Create table" action.

Step 8:

We add the "Create file" action to generate a blank Excel file. In the "File content" field of the "Create file" action, we enter a space to create a blank file.

Step 9:

To create a table in the blank Excel file and include the column names from the "Compose" action in Step 7, we follow these steps:

In the "Column names" field of the "Create table" action, we enter the output of the "Compose" action from Step 7, which is "Compose 6".

By including the column names from the "Compose" action, we ensure that the table created in the Excel file will have the correct column headers.

Finally, we save and run the flow manually. You can see the flow generating a new Excel file with the exact same table structure as the parent file.

Conclusion:

This blog presented a straightforward method to dynamically create a new Excel file with the same header structure as the parent table, without any data. By leveraging Power Automate Flow and SharePoint, we achieved this outcome seamlessly.

If you have any questions you can reach out our SharePoint Consulting team here.