Selecting/adding fields based on other fields

[0YAoNnmRmKDg]

Many thanks in advance for any help here..

I know what i need to do in principle but cant nail the Splunk search.... I have tried to use transaction and first last but cant it to work.

I have a csv file exported monthy and ingested into splunk, need to calculate a monthy $ value for a given combination

sudo search....

for the FIRST time you see foo=1 and bar=10, select field x
for the LAST time you see foo=1 and bar=10, select field y and x
for the LAST time you see foo=1 and bar=11, select field y and x
for the LAST time you see foo=1 and bar=n..., select field y and x
For the LAST time you see foo=1 select field y

add x+y+x+y+x+y = monthy $

for the FIRST time you see foo=2 and bar=20, select field x
for the LAST time you see foo=2 and bar=20, select field y and x
for the LAST time you see foo=2 and bar=21, select field y and x
for the LAST time you see foo=2 and bar=n..., select field y and x
For the LAST time you see foo=2 select field y

add x+y+x+y+x+y = monthy $

and so on.... any ideas

• there are times and dates in each row
• foo and bar will always be uniq

with the example below, the desired output in a table would be...

1 = $4099.66
2 = $5672.07

---

   ID   Start           Stop            y       foo     x       bar
    1   31/03/15 22:01  1/04/15 19:31   278.88  2197.94 764.22  10
    1   1/04/15 19:31   2/04/15 17:59   340.91  2019.52 702.19  10
    1   2/04/15 17:59   7/04/15 00:00   342.32  2015.46 700.78  10
    1   7/04/15 00:00   7/04/15 23:45   638.98  1162.27 404.12  10
    1   7/04/15 23:45   8/04/15 15:45   792.56  720.56  250.54  10
    1   8/04/15 15:45   9/04/15 16:20   943.96  285.14  99.14   10
    1   9/04/15 16:20   10/04/15 20:36  93.98   2729.71 949.12  11
    1   10/04/15 20:36  11/04/15 14:59  214.13  2384.16 828.97  11
    1   11/04/15 14:59  12/04/15 15:13  218.21  2372.42 824.89  11
    1   12/04/15 15:13  13/04/15 14:48  350.74  1991.26 692.36  11
    1   13/04/15 14:48  14/04/15 18:42  578.17  1337.16 464.93  11
    1   14/04/15 18:42  15/04/15 14:17  687.16  1023.69 355.94  11
    1   15/04/15 14:17  16/04/15 18:37  767.65  792.2   275.45  11
    1   16/04/15 18:37  17/04/15 19:05  955.14  252.98  87.96   11
    1   17/04/15 19:05  18/04/15 16:05  50.98   2853.38 992.12  12
    1   18/04/15 16:05  19/04/15 15:39  51.14   2852.93 991.96  12
    1   19/04/15 15:39  20/04/15 18:27  215.4   2380.51 827.7   12
    1   20/04/15 18:27  21/04/15 20:15  350.21  1992.78 692.89  12
    1   21/04/15 20:15  22/04/15 18:34  488.3   1595.64 554.8   12
    1   22/04/15 18:34  23/04/15 17:09  630 1188.1  413.1   12
    1   23/04/15 17:09  25/04/15 16:00  630.12  1187.75 412.98  12
    1   25/04/15 16:00  26/04/15 09:44  631.33  1184.28 411.77  12
    1   26/04/15 09:44  27/04/15 19:27  631.69  1183.23 411.41  12
    1   27/04/15 19:27  28/04/15 23:45  930.24  324.6   112.86  12
    1   28/04/15 23:45  29/04/15 15:33  33.3        2904.22 1009.8  13
    1   29/04/15 15:33  30/04/15 14:39  206.14  2407.14 836.96  13
    2   31/03/15 23:50  1/04/15 23:46   81.63   2765.22 961.47  20
    2   1/04/15 23:46   2/04/15 23:21   329.13  2053.41 713.97  20
    2   2/04/15 23:21   3/04/15 22:57   388.6   1882.38 654.5   20
    2   3/04/15 22:57   4/04/15 13:34   524.51  1491.5  518.59  20
    2   4/04/15 13:34   7/04/15 22:21   701.85  981.44  341.25  20
    2   7/04/15 22:21   8/04/15 23:38   937.17  304.66  105.93  20
    2   8/04/15 23:38   9/04/15 23:51   129.28  2628.19 913.82  21
    2   9/04/15 23:51   10/04/15 23:23  370.06  1935.69 673.04  21
    2   10/04/15 23:23  11/04/15 16:43  565.21  1374.42 477.89  21
    2   11/04/15 16:43  13/04/15 23:24  777.8   763.02  265.3   21
    2   13/04/15 23:24  14/04/15 23:45  1020.29 65.6    22.81   21
    2   14/04/15 23:45  15/04/15 23:45  224.09  2355.52 819.01  22
    2   15/04/15 23:45  16/04/15 23:58  497.82  1568.25 545.28  22
    2   16/04/15 23:58  17/04/15 23:45  750.78  840.73  292.32  22
    2   17/04/15 23:45  18/04/15 22:49  815.2   655.44  227.9   22
    2   18/04/15 22:49  19/04/15 11:23  951.57  263.25  91.53   22
    2   19/04/15 11:23  20/04/15 23:47  105.86  2695.53 937.24  23
    2   20/04/15 23:47  21/04/15 23:59  352.14  1987.24 690.96  23
    2   21/04/15 23:59  22/04/15 23:45  578.07  1337.44 465.03  23
    2   22/04/15 23:45  23/04/15 23:39  838.94  587.17  204.16  23
    2   23/04/15 23:39  24/04/15 23:58  28.91   2916.86 1014.19 24
    2   24/04/15 23:58  25/04/15 03:37  87.79   2747.52 955.31  24
    2   25/04/15 03:37  28/04/15 23:57  271.71  2218.54 771.39  24
    2   28/04/15 23:57  29/04/15 23:55  538.2   1452.1  504.9   24

[woodcock]

Generally (not the complete solution but you can iterate it out) like this:

...  | eventstats latest(y) AS lastyBYfoo BY foo | stats first(lastyBYfoo) AS lastyBYfoo earliest(x) AS firstxBYfoobar earliest(y) AS firstyBYfoobar latest(x) AS lastxBYfoobar latest(y) AS lastyBYfoobar by foo bar | eval monthly1=if(foo=1 AND bar=10,firstxBYfoobar + lastyBYfoobar + lastxBYfoobar ,0) + if(foo=1 AND bar=11,lastxBYfoobar+lastyBYfoobar,0) + ... + if(foo=1,lastyBYfoo,0)

I would save everything after | monthly1 as a macro and call it like this:

... | eventstats earliest(x) AS firstxBYfoobar earliest(y) AS firstyBYfoobar latest(x) AS lastxBYfoobar latest(y) AS lastyBYfoobar by foo bar | eventstats latest(y) AS lastyBYfoo | dedup foo bar | `monthly(1)` | `monthly(2)` ...