Splunk Search

stats count by not working

davidcraven02
Communicator

I want to use stats count (machine) by location but it is not working in my search.

Below is my current query displaying all machines and their Location. I want to use a stats count to count how many machines do/do not have 'Varonis' listed as their Location

 index=ad source=otl_addnsscan name=*nas* type=CNAME NOT ( name=*.com OR name=*app*) 
    | rex field=data "^(?<machine>[^.]+).*$" 
    | eval machine = lower(machine) 
    | search NOT machine=*app* 
    | table machine] 
| search NOT 
    [ search index=summary report=otl_engineering_jira_serverrequests Component/s=*Momentum* 
    | eval machine=lower('Server Name') 
    | table machine] 
| join type=left machine 
    [ search index = varonis source = otl_varonis_monitoring sourcetype="csv"
      | rex field=_raw "^(\x22(?<machine>\w+))"
        | rex field=Share "((?<drive>\w+)\$)" 
        | rex field=_raw "^(\x22(?<nas>\w+))"
        | eval nas_cot=(machine)
        | eval con=nas_cot. "," .drive 
        | eval con_UL = upper(con) 
        ] 
| stats values(nas_cot) as nas_cot values(Location) AS Location by machine 
| fillnull value="Not in Varonis" Location 
| dedup machine 
| fields - nas_cot
| sort machine asc
0 Karma

philipmattocks
Path Finder

Can you try removing the space after the count so that it's:

stats count(machine) by location

Let me know if that helps.

nickhills
Ultra Champion

Try removing | stats values(nas_cot) as nas_cot values(Location) AS Location by machine
and the dedupcommand and then add |stats dc(machine) by locationat the end
or dc(location) by machine - I am not 100% clear which one you want?

If my comment helps, please give it a thumbs up!
0 Karma

adonio
Ultra Champion

maybe try dc(machine)
stands for distinct count of machines. count used to count events that belongs to a certain field
hope it helps.

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...