Calculating values from dynamically named columns

[astockmeister_s]

I have a search that returns values for dates and I want to calculate the changes between the dates.  What I want would look something like this.

index	1-Aug	8-Aug	Aug 8 change	15-Aug	Aug 15 Change	22-Aug	Aug 22 change	29-Aug	Aug 29 change
index1	5.76	5.528	96%	5.645	102%	7.666	136%	6.783	88%
index2	0.017	0.023	135%	0.036	157%	0.033	92%	14.985	45409%
index3	2.333	2.257	97%	2.301	102%	2.571	112%	0.971	38%
index4	2.235	1.649	74%	2.01	122%	2.339	116%	2.336	100%
index5	19.114	14.179	74%	14.174	100%	18.46	130%	19.948	108%

I have a search that returns the values without the change calculations

| loadjob savedsearch="me@email.com:splunk_instance_monitoring:30 Days Ingest By Index"
| eval day_of_week=strftime(_time,"%a"), date=(strftime(_time,"%Y-%m-%d"))
| search day_of_week=Tue
| fields - _time day_of_week
| transpose header_field=date
| rename column AS index
| sort index
| addcoltotals label=Totals labelfield=index

If the headers were something like "week 1" "week 2" I can get what I want, but with date headers that change very time, I've tried using foreach to iterate through and caclulate the changes from one column to the next but haven't been able to come up with the right solution.  Can anyone help?

[ITWhisperer]

You could try something along these lines

| makeresults format=csv data="index,1-Aug,8-Aug,15-Aug,22-Aug,29-Aug
index1,5.76,5.528,5.645,7.666,6.783
index2,0.017,0.023,0.036,0.033,14.985
index3,2.333,2.257,2.301,2.571,0.971
index4,2.235,1.649,2.01,2.339,2.336
index5,19.114,14.179,14.174,18.46,19.948"
``` the lines above simulate your data (without the calculations) ```
| untable index date size
| eval date=strptime(date."-2024","%d-%b-%Y")
| fieldformat date=strftime(date,"%F")
| sort 0 index date
| streamstats last(size) as previous window=1 global=f current=f by index
| eval relative_size = 100 * size / previous
| fields - previous
| appendpipe
    [| eval date=strftime(date, "%F")." change"
    | xyseries index date relative_size]
| appendpipe
    [| eval date=strftime(date, "%F")
    | xyseries index date size]
| fields - date size relative_size
| stats values(*) as * by index

[astockmeister_s]

When I add your processing to the end of mine I get a table that only has one column -- index.  None of the data is there.

[astockmeister_s]

For completeness, here's how I spliced them together, although I tried just adding your commands after my search, entirely, and after my search but without the addcoltotals and neither worked.

| loadjob savedsearch="30 Days Ingest By Index"
| eval day_of_week=strftime(_time,"%a"), date=(strftime(_time,"%Y-%m-%d"))
| search day_of_week=Tue
| fields - _time day_of_week
| transpose header_field=date
| rename column AS index
| untable index date size
| eval date=strptime(date."-2024","%d-%b-%Y")
| fieldformat date=strftime(date,"%F")
| sort 0 index date
| streamstats last(size) as previous window=1 global=f current=f by index
| eval relative_size = 100 * size / previous
| fields - previous
| appendpipe
[| eval date=strftime(date, "%F")." change"
| xyseries index date relative_size]
| appendpipe
[| eval date=strftime(date, "%F")
| xyseries index date size]
| fields - date size relative_size
| stats values(*) as * by index

[astockmeister_s]

Thanks for your help, I really appreciate it.

Here's the output from the report job:

OTHER arc dev test prod

2024-07-16

5.760

0.017

2.333

2.235

19.114

2024-07-17

5.999

0.018

2.595

2.260

18.355

2024-07-18

6.019

0.018

2.559

1.962

16.879

2024-07-19

5.650

0.018

2.177

1.566

14.573

2024-07-20

4.849

0.013

2.389

1.609

12.348

2024-07-21

4.619

0.013

2.190

1.618

12.296

2024-07-22

5.716

0.019

2.425

1.626

14.286 I was able to play around with what you sent and this gives me rows with the size and the change in size, which is the data I want, but I can't seem to get it back to the table format I need.  if I add "| stats values(*) as * by index" then I end up with a format that is multivalue and I haven't been able to get that untangled either.  I am OK at this stuff, but am definitely not a pro level user. | loadjob "" | eval date=(strftime(_time,"%Y-%m-%d")) | fields - _time | transpose header_field=date | rename column AS index | sort index | untable index date size | sort 0 index date | streamstats last(size) as previous window=1 global=f current=f by index | eval relative_size = 100 * size / previous | fields - previous | appendpipe [| eval date=strftime(date, "%F")." change" | xyseries index date relative_size] | appendpipe [| eval date=strftime(date, "%F") | xyseries index date size]

[astockmeister_s]

That didn't format well.  I'll post the data in a separate reply from my comments.

I was able to play around with what you sent and this gives me rows with the size and the change in size, which is the data I want, but I can't seem to get it back to the table format I need. 

if I add "| stats values(*) as * by index" then I end up with a format that is multivalue and I haven't been able to get that untangled either.  I am OK at this stuff, but am definitely not a pro level user.

| loadjob ""
| eval date=(strftime(_time,"%Y-%m-%d"))
| fields - _time
| transpose header_field=date
| rename column AS index
| sort index
| untable index date size
| sort 0 index date
| streamstats last(size) as previous window=1 global=f current=f by index
| eval relative_size = 100 * size / previous
| fields - previous
| appendpipe
[| eval date=strftime(date, "%F")." change"
| xyseries index date relative_size]
| appendpipe
[| eval date=strftime(date, "%F")
| xyseries index date size]

[ITWhisperer · Accepted solution]

| makeresults format=csv data="date,OTHER,arc,dev,test,prod
7/16/2024,5.76,0.017,2.333,2.235,19.114
7/17/2024,5.999,0.018,2.595,2.26,18.355
7/18/2024,6.019,0.018,2.559,1.962,16.879
7/19/2024,5.650,018,2.177,1.566,14.573
7/20/2024,4.849,0.013,2.389,1.609,12.348
7/21/2024,4.619,0.013,2.19,1.618,12.296
7/22/2024,5.716,0.019,2.425,1.626,14.286
7/23/2024,5.716,0.019,2.425,1.626,14.286"
| eval _time=strptime(date,"%m/%d/%Y")
| fields - date
``` the lines above simulate the data from your loadjob (with 22nd duplicated to 23rd to give 2 Tuesdays) ```
``` there is no need for the transpose as the untable will work with the _time and index fields in the other order ```
| untable _time index size
| eval date=strftime(_time,"%F")
| eval day=strftime(_time, "%a")
| where day="Tue"
| fields - day _time
| sort 0 index date
| streamstats last(size) as previous window=1 global=f current=f by index
| eval relative_size = 100 * size / previous
| fields - previous
| appendpipe
    [| eval date=date." change"
    | xyseries index date relative_size]
| appendpipe
    [| xyseries index date size]
| fields - date size relative_size
| stats values(*) as * by index

[astockmeister_s]

I had to remove these 2 lines from the very top because they emptied the _time column:

| eval _time=strptime(date,"%m/%d/%Y")
| fields - date

But after that it works like a charm.  Thanks so much

[ITWhisperer]

It is a bit difficult to diagnose what might be wrong without sight of your data. Please could you share some sample representative events (anonymised as necessary) from your loadjob

[astockmeister_s]

	OTHER	arc	dev	test	prod
7/16/2024	5.76	0.017	2.333	2.235	19.114
7/17/2024	5.999	0.018	2.595	2.26	18.355
7/18/2024	6.019	0.018	2.559	1.962	16.879
7/19/2024	5.65	0.018	2.177	1.566	14.573
7/20/2024	4.849	0.013	2.389	1.609	12.348
7/21/2024	4.619	0.013	2.19	1.618	12.296
7/22/2024	5.716	0.019	2.425	1.626	14.286