Query to get the number of the operating system.

[Shashanksinha]

Hello, 

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 . 

[mfakhouri]

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.

[Shashanksinha]

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

[mfakhouri]

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?

[Shashanksinha]

Hello 

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 .