stats count by not working

[davidcraven02]

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

[philipmattocks]

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]

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?

[adonio]

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