Help with Stats / Tstats and exclude devices based on OS version

[neerajs_81]

Hello All,  
We currently use the following search to list all the Windows hosts in our environment. 

| tstats dc(host) where index=windows by host

Now, i have a requirement to filter out  all Windows 10 systems  as in if the OS_Version field = Windows 10. Since the OS_Version field is not applicable to tstats , the only option i see is to use stats command as follows:

index=windows os_version="windows 10" | stats dc(host)  by host

This search takes lot of time, runs  very slowly if i need query for Last 7 d time range.  I understand tstats is much faster as compared to stats and this slowness  with stats is bound to be there.

Any thoughts, suggestions how to optimize this , make the search faster for  getting a list of distinct hosts , their count based on os_version ?  WHat would you all do in such a use case ?

[isoutamo]

Hi

have you looked this conf presentation https://conf.splunk.com/files/2020/slides/PLA1089C.pdf and where PREFIX is presented? Maybe it can help you?

r. Ismo

[neerajs_81]

Thank you @isoutamo , that does seem to be exactly what i am looking for. But the slide content is a bit confusing. How do i exactly use prefix for non-indexed fields like os_version in my case ?

Is it   

| tstats dc(host) where index=windows  by PREFIX(os_version=)

OR
| tstats dc(host) where Prefix(os_version="windows  10")  by host 

Both are  not working.

[isoutamo · Accepted solution]

Can you try this

| tstats dc(host) as nHost
    where index=windows AND TERM(os_version="windows 10")

or

| tstats dc(hosts) as nHost
  where index=windows
     by PREFIX(os_version=)

 Both of those should work. You can try those with _internal logs like

| tstats count(host) as host where index=_internal AND source=*metrics.log by PREFIX(group=)

OR

| tstats count(host) as host where index=_internal AND source=*metrics.log AND TERM(group=conf)

[neerajs_81]

Thanks a lot