topic Re: How to calculate the difference in count of last two columns where the columns will increase dynamically in Splunk Enterprise

How to calculate the difference in count of last two columns where the columns will increase dynamically

Keerthana_18 — Mon, 25 Oct 2021 15:55:06 GMT

Hi Everyone,

I am new to Splunk. Could someone help me and provide the search for the below query: That would be Great!!

I have a search which gives below results by doing comparison of last week and current week and it shows the count of each status in weekly manner.

Date	Active	Inactive	Deleted	Added
09/10/21	50	20	5	20

I will be sending the above result to lookup file every week data to provide the summary.(| outputlookup append=true summary.csv)

The lookup file looks like below after 3 weeks.

Date	Active	Inactive	Deleted	Added
09/10/2021	50	20	5	20
16/10/2021	55	15	10	30
23/10/2021	60	10	8	15

Date column keeps on growing dynamically each week, always I need to calculate difference between last two column and create new column to tell the difference. The result am expecting is below.

Status	09/10/2021	16/10/2021	23/10/2021	Difference b/w last 2 columns
Active	50	55	60	5
Inactive	20	15	10	-5
Deleted	5	10	8	-2
Added	20	30	15	-15

Re: How to calculate the difference in count of last two columns where the columns will increase dynamically

PickleRick — Mon, 25 Oct 2021 16:39:23 GMT

I don't understand - the second table is teansposed versus the first one. So what exactly do you want?

Re: How to calculate the difference in count of last two columns where the columns will increase dynamically

Keerthana_18 — Tue, 26 Oct 2021 06:21:25 GMT

Hello,

From the lookup file i need to do transpose then I want to find the difference of last column(last 2 weeks) in new column called Difference.

Re: How to calculate the difference in count of last two columns where the columns will increase dynamically

ITWhisperer — Tue, 26 Oct 2021 08:52:11 GMT

The part before the blank lines sets up dummy data and would be replaced by your search/inputlookup

The process is to list the values from the previous and current line, then remove the additional values for all but the last line. Then mvexpand the last line, resetting the first copy to its original state. Now the last line has both values (note the addition of the "-" for the second value), which can then be summed. Finally, transpose to get the layout required.

Note the use of leading underscores in some of the field names to hide them from the foreach.

Re: How to calculate the difference in count of last two columns where the columns will increase dynamically

Keerthana_18 — Tue, 26 Oct 2021 09:33:51 GMT

Thanky @ITWhisperer .

Great! Awesome, it worked as excepted.