help with using table and stats to produce query output

[ebailey]

I need to take the output of a query and create a table for two fields and then sum the output of one field. The two fields are already extracted and work fine outside of this issue.

For example

eventtype=test-prd Failed_Reason="201" hoursago=4 | stats count by Failed_User | table Failed_User,count

works perfectly

I get a list of Failed_User with a count of how many times the field occurred in the query.

The problem is when I try to add the second field to the table

eventtype=test-prd Failed_Reason="201" hoursago=4 | stats count by Failed_User | table Failed_User,count,IP_ADDR

I get the same table as above but with no data under the column for IP_ADDR

How do I carry the data from the extracted field to fill in the column under IP_ADDR?

Every event with Failed_User also contains IP_ADDR

Thanks

Ed

[sawgata12345]

eventtype=test-prd Failed_Reason="201" hoursago=4
| stats count, IP_ADDR by Failed_User
| table Failed_User,count,IP_ADD

even without values(IP_ADDR) works as above

Similar kind of output from access.log
source="access.log" host="cisco-virtual-machine" index="testaccess1" sourcetype="access_combined_wcookie" status=404|stats count by status,clientip | table count,status,clientip

[dwaddle · Accepted solution]

The stats command produces a statistical summarization of data. The reason your IP_ADDR field doesn't appear in your table command is because stats summarized your primary search into a smaller result set containing only a count for each value of Failed_User. What you might do is use the values() stats function to build a list of IP_ADDR for each value of Failed_User

eventtype=test-prd Failed_Reason="201" hoursago=4 
| stats count, values(IP_ADDR) as IP_ADDR by Failed_User 
| table Failed_User,count,IP_ADDR

Note - I am not sure the table command provides anything useful in this example.

[dwaddle]

Awesome. Could you please click the checkbox to the left to mark the answer as correct? Thanks.

[mendesjo]

great explanation, I get it.. but I tried that values() suggestion with no luck.

[ebailey]

perfect thanks for explaining how stats works. Your explanation is the best I have read.