4

I select orderids for a model in a subsearch and than select the most common materials for each orderid, so I get a list of every Material and the time it was a part of an order. I want to display the most common materials in percentage of all orders. So I need this amount how often every material was found and then divide that by total amount of orders.

sourcetype=file1 [subsearch... ->returns Orders] |

here I need to select the total amount of orders like:

stats dc(Orders) as totalamount by Orders|
stats dc(Orders) as anz by Material|
eval percentage= anz/totalamount|
sort by percentage desc

How can I perform the total amount of search?

L_J
  • 2,351
  • 10
  • 23
  • 28
Nils
  • 41
  • 2
  • 6

2 Answers2

1

I assume from your base search you will get the Orders and Material anyway, You need to use eventstats for taking the total count . Below code should work 

index=foo sourcetype=file1 [subsearch... ->returns Orders]
| stats count(Orders) as order_material_count by Material 
| eventstats sum(order_material_count ) as totalCount
| eval percentage=(order_material_count*100)/totalamount  
| fields Mateira, order_material_count , percentage
| sort - percentage
Learner
  • 1,544
  • 8
  • 29
  • 55
0

Try the streamstats command.

index=foo sourcetype=file1 [subsearch... ->returns Orders]
| streamstats count(Orders) as totalamount 
| stats count(Orders) as anz by Material 
| eval percentage=(anz*100)/totalamount 
| sort - percentage
RichG
  • 9,063
  • 2
  • 18
  • 29
  • I think it wont work. I get no results to the searchpart |streamstats dc(Orders) as totalamount . – Nils Aug 03 '18 at 10:52
  • my whole search is: sourcetype, subsearch , streamstats, stats, eval, table, sort – Nils Aug 03 '18 at 10:55
  • I edited my answer to include the subsearch for Orders. – RichG Aug 03 '18 at 11:29
  • I dont get any results for totalamount as well, so the precentage is empty too. That is my whole search sourcetype="*file1" [search sourcetype="file2" MODEL=0815 |dedup ORDID| fields ORDID | format] | streamstats count(ORDID) as totalamount | stats count(ORDID) as anz by MATNR | eval percentage=(anz*100)/totalamount | table MATNR totalamount | sort - percentage – Nils Aug 03 '18 at 11:49
  • Run the subsearch by itself to verify to get the expected results. Then run the query up to the first pipe and check those results. One of those statements is not returning ORDID fields. – RichG Aug 03 '18 at 11:58
  • I did that, the search works if I dont write Streamstats, but instead do the calculation with a fixed value |eval percantage=(anz*100) / 170. I tryed the streamstats without stats and without the calsulation, it put a digit in every line, like 1, 2, 3, 4. But for my purposes I need just the total amount. just another stats dc(ORDID) but that wont work – Nils Aug 03 '18 at 12:10
  • Try `eventstats` in place of `streamstats`. – RichG Aug 03 '18 at 12:28
  • doenst work ass well, i tryed every statement i found on google. maybe it wont work, but thank you – Nils Aug 03 '18 at 12:40