Splunk Search

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

neerajs_81
Contributor

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
Contributor

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
Contributor

Thanks a lot

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>