Why are the stats values not working with append?

[davidcraven02]

I need the field concate_CSV to list all concatenations for each machine but it is not working. (Actual v Desired output below)

• The field con_Splunk contains the concatenation of 'machine' and 'drive' from the table displayed (e.g nas01b,B)

• The field concate_CSV contains all the concatenation of 'machine' and 'drive' for each machine from the csv (e.g nas01b,E nas01b,F)

When I use "| join type=left machine " instead of append on the second search with "stats values(concate_CSV) as concate_CSV values(Location) AS Location by machine " it works but it excludes machines that don't have any Path listed on the source=WMI:Shares

     index=summary report=otl_engineering_jira_serverrequests Status=Closed "Server Name"=* NOT Project="Server Build"       NOT "Decommission Date"=* Project= "Win Admin" NOT "Component/s"= "*Momentum*" 
    | eval machine = lower('Server Name') 
    | table machine 
    | append 
        [ search index=windows host=*nas* source=WMI:Shares 
        | eval machine=lower(host) 
        | eval drive = Path 
        | rex field=drive "(?P<Drive>\w+)\:" 
        | eval con_Splunk=machine. "," .Drive 
        | eval con_splunkUL = upper(con_Splunk) ] 
    | append 
        [ search index = varonis source = otl_varonis_monitoring sourcetype="csv" 
        | rex field=Share "((?<drive>\w+)\$)" 
        | rex field=machine "^(?<machine>\w+)\." 
        | eval machine = lower(machine) 
        | eval con=machine. "," .drive 
        | eval concate_CSV = upper(con) 
        | eventstats max(Location) as Location by machine ] 
    | search 
        [ search index=ad source=otl_addnsscan name=*nas* type=CNAME NOT ( name=*.options-it.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] 
    | fillnull value="Not in Varonis" Location 
    | dedup machine, drive 
    | table machine , drive, Location ,con_Splunk, concate_CSV, 
    | eval machine = lower(machine) 
    | sort machine asc

[cmerriman]

what are the results when you try with append? have you gotten any errors with either join or append when looking at the job inspector? when you used append, did you finish it up with something like |stats values(Location) as Location values(con_Splunk) as con_Splunk values(concate_CSV) as concate_CSV by machine drive to merge it all together?

[davidcraven02]

I do use append in this query and the results are in the screenshot as 'Actual Results'. Do you mean when using "| join type=left machine " ?

If so when using this join it works correctly only but it only displays 1,633 results (instead of the correct amount of 1,935) and it excludes those machines that don't have any Path listed from the source=WMI:Shares.