时间戳

首页 > 解决方案 > Splunk中如何计算field和gropuby field的比值

splunk - Splunk中如何计算field和gropuby field的比值

问题描述

我有这张桌子。

Fruits  Result
--------------
Apple   sold
Apple   sold
Apple   instock
Apple   expired
Banana  sold
Banana  sold
Banana  sold
Orange  instock
Orange  instock

我必须在 Splunk 中生成如下所示的报告。我想按水果类型计算并计算结果的比率。

Fruits  count  instock_ratio expired_ratio sold_ratio
----------------------------------------------------
Apple   4       0.25         0.25          0.5
Banana  3       0            0             1.0
Orange  2       1.0          0             0

在 SQL 中，我可以得到这个结果。

WITH src AS(
    SELECT
       Fruits,
       count(CASE WHEN result="sold" THEN Fruits ELSE null END) AS sold_count,
       count(CASE WHEN result="instock" THEN Fruits ELSE null END) AS instock_count,
       count(CASE WHEN result="expired" THEN Fruits ELSE null END) AS expired_count,
       count(Fruits) AS total_counts
    FROM table
    GROUP BY Fruits
)
SELECT
   Fruits,
   total_counts,
   sold_count/total_counts,
   instock_count/total_counts,
   expired_count/total_counts
FROM src

任何人都可以帮助我使用 splunk 命令吗？

标签： splunk

解决方案

将以下内容添加到您的搜索中

| stats count, count(eval(Result="sold")) AS sold_count, count(eval(Result="expired")) AS expired_count, count(eval(Result="instock")) AS instock_count by Fruits
| eval sold_ratio=sold_count/count, expired_ratio=expired_count/count, instock_ratio=instock_count/count | fields - *_count

我们只计算每个水果的总计数和每个结果的计数。要计算出比率，只需将每个计数除以总数即可。

这是一个显示它有效的示例。它还使用该foreach命令使事情变得更清洁。

| makeresults count=100 | eval r1=random()%3 | eval Fruits=case(r1=1, "Apple", r1=2, "Banana", true(), "Orange") | eval r2=random()%3 | eval Result=case(r2=1,"instock", r2=2, "sold", true(), "expired") 
| stats count, count(eval(Result="sold")) AS sold_count, count(eval(Result="expired")) AS expired_count, count(eval(Result="instock")) AS instock_count by Fruits
| foreach *_count [ eval <<MATCHSTR>>_ratio=<<FIELD>>/count ] | fields - *_count

推荐阅读