How to show only certain results in the statistics (by hiding some search results)?

[tonahoyos]

Hello,

I would like to hide the following results in bold and only have the final eval statement show. I am only doing the calculations for the last eval statement.

source="Dataset_Finance.csv" host="sample" index="dataintegration" sourcetype="SampleFinance" ObjectAccount="4*" OR ObjectAccount="5*"
| eval Sales=if(ObjectAccount="411010",DomesticAmount,0), Costs=if(like(ObjectAccount,"5%"),DomesticAmount,0)
| stats sum(Sales) as Sales, sum(Costs) as Costs
| eval CM=Sales+Costs
| eval CMPer=(CM/Sales)*100

Also, I noticed that I can not put a by statement after the eval, should I only include it in the stats section and how will I be able to categorize the CMPer by another value?

[niketn · Accepted solution]

@tonahoyos, I think you need to reevaluate what you are trying to perform with your query.

1) Your base search is looking for all ObjectAccount starting with 4*, however, in your stats you are performing a sum of DomesticAmount only for ObjectAccount 411010 for calculating Sales. Remaining are set to 0. So you should ideally filter for ObjectAccount="411010" in base search rather than "4*".

2) Also if you are calculating percent for Sales and Costs and you are converting Sales for everything other than ObjectAccount 411010 as 0, then you will not be able to calculate percent for other Accounts. Percent calculation is indicating that you need only one Account 411010, unless I am misinterpreting the provided information.

3) As a performance tuning tip you should perform eval after stats command. Also by is applicable on transforming commands like statsnot on eval. The eval command is for expression evaluations like a=b+c etc.

Having said that you can use table or fields command to retain only the fields you require in final table. Please try out the following query

source="Dataset_Finance.csv" host="sample" index="dataintegration" sourcetype="SampleFinance" ObjectAccount="411010" OR ObjectAccount="5*"
| stats sum(DomesticAmount) as Sales, sum(DomesticAmount) as Costs by ObjectAccount
| eval Sales=if(ObjectAccount="411010",Sales,0), Costs=if(match(ObjectAccount,"^5"),Costs,0)
| eval CM=Sales+Costs 
| eval CMPer=(CM/Sales)*100
| table ObjectAccount CMPer

PS: Notice above that :
1) I have filtered only ObjectAccount="411010" in my base search.
2) I have used by ObjectAccount in stats function.
3) Also the eval for Sales and Cost is after eval.
4) Cost uses match() function to use regular expression based pattern matching to find any ObjectAccount starting with 5.

If you want to use your own query, you just need to add the following command to your existing search (since you do not have ObjectAccount in your stats

| table CMPer