Can you help fix the output of appencols and stats list in this search?

[mahbs]

Hi,

I'm trying to list data per field. I've used stats list which works partially, it lists all the data for both fields into a single field.

This is what I want:

USER      ERROR    
 -------------------------
 user1     error1
                error2
                error3
 -------------------------
 user2     error4
                error5

However, this is what I'm getting:

USER      ERROR    
 -------------------------
 user     error1    
                error 2   
                error 3
                error 4
                error 5

 -------------------------
 user2     

This is my query:

 index=xxx sourcetype=xxx host=n | fieldsummary | fields field,count | regex field="^([A-Z_]+)$" | rename count as Count_V8 | join field [search index=drv sourcetype=xxx host=N|fieldsummary | fields field,count | regex field="^([A-Z_]+)$" | rename count as Count_V14] | eval match=if(Count_V8==Count_V14, "match", "No Match") |  eventstats max(Count_V14) AS maximum_V14| eval internal_diff=if(maximum_V14==Count_V14, "== Max Value", "<Max value") | eval All_Fields=if(internal_diff=="<Max value", field, "safe") | where All_Fields !="safe" | appendcols [search index=drv sourcetype=nl_loca_chain | where isnull(All_Fields) | rename _raw as Raw | stats list(Raw)]

The appendcols subsearch bit is the most relevant bit.

I've tried sticking "by field" after stats list(Raw) but it doesn't work.

Please Help!

[somesoni2 · Accepted solution]

I think you're better off with map command which will, for each All_Fields values, run that subsearch. Something like this

index=xxx sourcetype=xxx host=n | fieldsummary | fields field,count | regex field="^([A-Z_]+)$" | rename count as Count_V8 | join field [search index=drv sourcetype=xxx host=N|fieldsummary | fields field,count | regex field="^([A-Z_]+)$" | rename count as Count_V14] | eval match=if(Count_V8==Count_V14, "match", "No Match") |  eventstats max(Count_V14) AS maximum_V14| eval internal_diff=if(maximum_V14==Count_V14, "== Max Value", "<Max value") | eval All_Fields=if(internal_diff=="<Max value", field, "safe") | where All_Fields !="safe" 
| map search="search index=drv sourcetype=nl_loca_chain | where isnull('$All_Fields$') | rename _raw as Raw | stats list(Raw)  as Raw | eval All_Fields=\"$All_Fields$\""

[mahbs]

You Sir, are amazing! Thank you sooooooo muchhhh!!!!!! It worked

[somesoni2]

Glad to be of help. Don't forget to mark your question closed by accepting the answer.

[DalJeanis]

I don't see an appendcols subsearch in your code. Please post the entire code and mark it with the code button.

[mahbs]

Hi Dal, I've made the adjustments, please have a look

[somesoni2]

Append cols will just appends columns row by row and since your appendcols subsearch have only one row, everything appears in first row. Do you have any common field between those two result sets?

[mahbs]

Unfortunately I don't. The subsearch returns events which I want to append to the relevant field. Each field produces events, and I simply want to append those events to those fields. It's nearly there, I'm just missing one piece of the puzzle

[somesoni2]

So how do you decide how many entries from list(Raw) will be added to first row, second row etc...?

[mahbs]

Well, I've got a variable called All_Fields, which contains data that filters for raw data relevant to that field. I wanted to use "By Field" to assign the raw data to a field, but that's not working

[somesoni2]

What all fields are available before the appendcols?
And how many rows will be there (max)?

[mahbs]

There'll be two rows. There are two fields that's being passed to the subsearch through the variable All_Fields. In