Can you help me with my query using the append command with an inputlookup?

[djain]

Hey!

I am trying to create a query to evaluate an error rate. So the lookup table just has the total_users and time column. And, here is the query that I have written.

index=abc sourcetype=abc_errors
| stats dc(Users) AS Users_with_errors BY errorCode 
| append [| inputlookup AccountCount.csv]
| eval ErrorRate= round(((Users_with_errors/Total_Users)*100),2)
| timechart avg(ErrorRate) by  errorCode span=1d

And, some how, the append is not working or I am using it wrong, as I am not getting the Total_user value.

Can someone please give this a try?

[Vijeta · Accepted solution]

The append works fine with input lookup. The append will add the rows after the result from your first query(from index). The calculation you are doing will not work as when the column Total_users will have value your rest of the values from index will not be there. You can try the join instead-

index=abc sourcetype=abc_errors
 | stats dc(Users) AS Users_with_errors BY errorCode | eval i=10
 | join i [| inputlookup AccountCount.csv|eval i=10]
 | eval ErrorRate= round(((Users_with_errors/Total_Users)*100),2)
 | timechart avg(ErrorRate) by  errorCode span=1d

[djain]

This idea of a join worked although not with Timechart. Instead of eval=i I changed time to Day in both then joined on day and used the chart command. Thank you so much for your response.

[kmaron]

I think what's happening is the append is adding the entries with the Total_Users as separate events from the search events. In order to do your eval you need them grouped into the same event.

Can you try this?

 index=abc sourcetype=abc_errors
 | stats dc(Users) AS Users_with_errors BY errorCode 
 | append [| inputlookup AccountCount.csv]
 | stats values(Users_with_errors) as "Users_with_errors" values(Total_Users) as TotalUsers by _time
 | eval ErrorRate= round(((Users_with_errors/Total_Users)*100),2)
 | timechart avg(ErrorRate) by  errorCode span=1d

[djain]

This did not work unfortunately. But thank you for your response