Query to get the number of the operating system.

Showing results for 
Show  only  | Search instead for 
Did you mean: 

Query to get the number of the operating system.

L3 Networker


I have used the below query to get the number of the operating system.

dataset = endpoints
| filter endpoint_status = CONNECTED
| alter operating_system = to_json_string(operating_system)
| alter operating_system1 = regextract(operating_system , "[^\.]*(Centos|RHEL|Amazon|Windows|Ubuntu)")
| alter operating_system2 = arraystring(operating_system1 , ":")
| fields operating_system , endpoint_name, operating_system2
| comp count(endpoint_name) as counter by operating_system2
| sort desc counter


How do I go forward from here and display the sum of RHEL, CENTOS as Linux in the table . 



L3 Networker

Hi @Shashanksinha


If your goal is to count the number of agents by OS and simplify its grouping, I would suggest using the “agent_os_type” filter under the xdr_data dataset. This filter groups agent OS types that are currently connected (example output: AGENT_OS_WINDOWS). You can then take it a step further using “dedup” to establish a single entry per IP address associated with the connected operating systems. This query could look something like:


dataset = xdr_data

| filter agent_os_type != NULL and agent_ip_addresses != NULL

| fields agent_os_type as type, agent_ip_addresses as ip

| dedup ip by asc ip

| comp count(type) as count by type 


Hope that helps! I would also be curious to know if this solved your issue.

Thank you for the response . 


Since we have kuberneted we need an output where it gives us windows, Linux and kuberenetes

Would that be possible with this ? As the data sets for both are different



No problem!


I tweaked the prior query to also be compatible with the endpoints dataset:


dataset = endpoints

| filter endpoint_status = CONNECTED

| filter operating_system != null and last_seen != null

| fields operating_system as type, last_seen as ls

| dedup ls by desc ls

| comp count(type) as count by type


The IP address field doesn’t seem to be compatible with the query when the dataset is set to endpoints. But, I’ve found similar functionality with the agent “last_seen” field. This is likely due to the data type that is used depending on the dataset. How does this query work out with your implementation?


Thanks for the response . 

Currently this query gives me operating system individually .But I want to be able to sumup 3 operating systems and name them as Linux and do the same for windows .

Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!