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
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
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)` ...