Splunk Search

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

djain
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

Vijeta
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

Vijeta
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

djain
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

kmaron
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

djain
Path Finder

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!