Splunk Search

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

neerajs_81
Builder

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 ?

Labels (2)
0 Karma
1 Solution

isoutamo
SplunkTrust
SplunkTrust

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)

View solution in original post

isoutamo
SplunkTrust
SplunkTrust

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
Builder

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.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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
Builder

Thanks a lot

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...