Splunk Search

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

Path Finder

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?

0 Karma
1 Solution

Influencer

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

View solution in original post

0 Karma

Influencer

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

View solution in original post

0 Karma

Path Finder

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.

0 Karma

Motivator

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
0 Karma

Path Finder

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

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!