Introduction to XQL: Building Your First Widget

Introduction

In the next part of our series, we are looking to provide quick information immediately to our SOC Analysts by using widgets. In our scenario, we are interested in graphing the failed logins using GlobalProtect on our on-premises NGFWs and Prisma Access for known users of our environment. For our use case, the goal of this widget is to quickly provide the following information:

• Identify users experiencing the most failed GlobalProtect logins, whom the Help Desk may need to contact for proactive assistance.
• Detecting brute-force attacks, credential stuffing, or misconfigurations due to password compromise.
• Correlates with the internal Active Directory to focus on the Domain Users group only.

What are widgets?

Widgets are a way to visually express the results of our XQL queries against datasets. We can visually express the data in the following visuals:

• Area Chart – Line chart with a shaded area beneath; used to show trends and volume over time.
• Bubble Chart – Scatter plot with bubbles representing an extra data dimension in sizes; useful for multi-variable comparisons.
• Column Chart – Vertical bars for comparing categories; used for discrete data analysis.
• Funnel Chart – Displays stages in a process, often showing decreasing values; used for conversion/drop-off analysis.
• Gauge Chart – Semi-circular or circular meter displaying a single value within a range; good for performance tracking.
• Line Chart – Connects data points with a line to show trends over time; useful for continuous data monitoring.
• Map Chart – Plots geographical data to visualize regional patterns and distributions.
• Pie Chart – Circular chart divided into slices representing proportions; used for percentage-based comparisons.
• Scatter Plot – Individual data points are plotted to identify relationships or clusters between two variables.
• Single Value Chart – Displays one key metric prominently for quick reference.
• Word Cloud – Text-based visualization where word size reflects frequency; useful for analyzing common terms in datasets.

We can use widgets in dashboard and reports. Widgets can help with real-time monitoring and enhanced threat hunting by providing visibility into security events and key insights at a glance. 

Forming our hypothesis

First, we'll return to our XQL Query Flow Chart to follow the flow and form our hypothesis. Returning to the XQL IDE, we'll start by querying the panw_ngfw_globalprotect_raw dataset filtering for login failures. Using the dataset schema, we can confirm we're only interested in the source_user row, and using the comp stage, we can group similar rows together. We'll start with the following XQL Query that will sort failed GlobalProtect logins in descending order. 

dataset = panw_ngfw_globalprotect_raw
| filter status = "Failure"
| fields source_user
| comp count(_id) as Count by source_user
| sort desc Count 

The results of our query are shown in the table below the XQL IDE. 

Reviewing the first results, we can already see that the failed login attempts are not from our user base and do not follow our naming standard. Because the GlobalProtect Portal is exposed to the internet, the login form will be constantly under attack of login attempts from bots using leaked credentials. Using these results would result in noise for our SOC analyst to filter through. With XQL, we can use the concept of dataset joins to filter this noise out by integrating the output of our known users.

A new concept, dataset joins

Joins are a way to merge two datasets together in our query. We have the option of inner, left, and right dataset joins. We use joins to combine rows from two or more datasets based on a related column. 

• Inner – Returns only matching records from both datasets.
• Left – Returns all records from the left dataset and matching records from the right dataset.
• Right – Opposite of a left join; returns all records from the right dataset and matching ones from the left.

When we are performing dataset joins, there is a potential for conflicts in field names or values. The conflict_strategy parameter is used to handle these conflicts and determine which value to keep when fields from both datasets have the same name.

• Left – Retains values from the left dataset when there are conflicts in a join operation.
• Right – Retains values from the right dataset when conflicts occur
• Both – Keeps values from both datasets, potentially creating duplicate or concatenated values

Before using a dataset join, it is important that the XQL Query is valid on its own. It's recommended to open another tab in your browser and use the XQL IDE to validate the results.

Since we're using the Cloud Identity Agent, we know that our usernames are stored in the pan_dss_raw dataset. (Before the Cloud Identity Agent, the service was known as the Directory Sync Service.) We can filter our usernames based on the Active Directory group. We'll focus on users apart of the Domain Users group as it's a default security group that includes all user accounts within a domain.

dataset = pan_dss_raw
| filter security_groups contains "Domain Users"
| fields sam_account_name

We can verify the results in the table below the XQL IDE.

Joining our query

We will use an inner join since we're only interested in the source usernames that match in both datasets. As our usernames are referred to as sam_account_name in the pan_dss_raw and source_user in the panw_ngfw_globalprotect_raw, we'll use the as function to match users from the sam_account_name fields to the source_user fields using cie_users as the intermediary name. 

dataset = panw_ngfw_globalprotect_raw
| filter status = "Failure"
| join type = inner (
  dataset = pan_dss_raw
  | filter security_groups contains "Domain Users"
  | fields sam_account_name
) as cie_users cie_users.sam_account_name = source_user
| fields source_user
| comp count(_id) as Count by source_user
| sort desc Count 

Our results can be viewed in the table below the XQL IDE.

In our results, we can see the interesting problem that the username Administrator is returned in mixed cases. We can use the alter stage in both datasets to standardize the results in lowercase and refine our results.

dataset = panw_ngfw_globalprotect_raw
| filter status = "Failure"
| alter source_user = lowercase(source_user)
| join type = inner (
  dataset = pan_dss_raw
  | filter security_groups contains "Domain Users"
  | alter sam_account_name = lowercase(sam_account_name)
  | fields sam_account_name
) as cie_users cie_users.sam_account_name = source_user
| fields source_user
| comp count(_id) as Count by source_user
| sort desc Count

Our results are verified in the table below the XQL IDE.

Building the graph

With our refined query results, the XQL IDE makes building the graph easy using the WebUI. By clicking on the Graph tab in the results, the Chart Editor is like a "what you see is what you get" form is presented. 

The different options allow you to adjust the graph type as needed. For ease of use, we'll start with a pie chart using the source_user results as the X-axis and the Count as the Y-axis.

Once we're satisfied with the results, we can click the Add to query button to quickly add the graph snippet to our existing query in the XQL IDE as a view stage. 

As a final step, we'll update the XQL Query to add comments and format the query to be easier to read in case another SOC Analyst needs to update the widget or query in the future. 

dataset = panw_ngfw_globalprotect_raw
| filter status = "Failure"
| alter source_user = lowercase(source_user) //Formating source_user into all lowercase.
| join type = inner (
  dataset = pan_dss_raw //CIE Dataset
  | filter security_groups contains "Domain Users" //Limiting to Domain Users AD Group
  | alter sam_account_name = lowercase(sam_account_name) //Formating sam_account_name into all lowercase.
  | fields sam_account_name
) as cie_users cie_users.sam_account_name = source_user //Combine sam_account_name and source_user results as same field
| fields source_user
| comp count(_id) as Count by source_user 
| sort desc Count
| limit 15 //Limiting to top 15 users. Can increase with default_limit = `false` in the view stage.
| view graph type = pie 
       header = "Failed GlobalProtect Logins" 
       show_callouts = `true` 
       xaxis = source_user 
       yaxis = Count 
       legend_percentage = `true` 

Finally, we click the Save As button and select Widget to Library. After adding a name and description, the widget will be available for future Dashboard and Reports.

Conclusion

Widgets turn XQL query results into interactive charts, graphs, and heatmaps for easier data visualization. They support real-time monitoring, user-friendly interaction, and customizable dashboards, helping to identify trends and anomalies. In this use case, we've simplified complex queries from GlobalProtect and Cloud Identity Engine datasets to show insights on failed logins against known users, aiding SOC analysts in faster analysis. 

Stay tuned for the next part of our series where we'll use an XQL Query to create a dashboard for reporting.