How to break out a column and append it to the bottom of another column?

[ClubMed]

From the Subject Title, what I mean is it will increase the row count and decrease the column count - that is my intention.

After a series of mathematical computations, I ended up with the following table:

Unixtime_A	Total_A	Unixtime_B	Total_B
imaginary_unix_1	1	imaginary_unix_3	4
imaginary_unix_2	2	imaginary_unix_1	5
imaginary_unix_3	3	imaginary_unix_4	6

Notes:

• Unixtime_A may not equal Unixtime_B, but they are formatted the same that is snapped to the month with @mon (unixtime)
• Total_A and Total_B were the result of various conditional counts, so they need to be seperate fields

The desired table is:

Unixtime_AB	Total_A	Total_B
imaginary_unix_1	1
imaginary_unix_2	2
imaginary_unix_3	3
imaginary_unix_3		4
imaginary_unix_1		5
imaginary_unix_4		6

Which I can then use | fillnull and use a simple stats to sum both totals by Unixtime_AB. Like so:

| stats sum(Total_A), sum(Total_B) by Unixtime_AB

 
I'm not 100% sure if transpose, untable, or xyseries could do this - or if I was misusing them somehow.

[yuanliu · Accepted solution]

I don't quite get why you want a sparse corner for Total_* but it is hackable

| appendpipe
    [ eval Total_A = null()
    ]
| eval Total_B = if(isnull(Total_A), Total_B, null())
| eval Unixtime_AB = if(isnull(Total_B), Unixtime_A, Unixtime_B)
| fields Total_* Unixtime_AB

(Note this hack works for a small number of Unixtime_* but not particularly scalable.)

Just in case you want a dense matrix, I'm offering an obvious result set:

Total_AB	Unixtime_AB
1	imaginary_unix_3
2	imaginary_unix_1
3	imaginary_unix_4
4	imaginary_unix_3
5	imaginary_unix_1
6	imaginary_unix_4

To get this, do

| appendpipe
    [ eval Total_A = null()
    ]
| eval Total_AB = if(isnull(Total_A), Total_B, Total_A)
| eval Unixtime_AB = if(isnull(Total_B), Unixtime_A, Unixtime_B)
| fields - *_A *_B

Here is an emulation you can play with and compare with real data.

| makeresults format=csv data="Unixtime_A,	Total_A,	Unixtime_B,	Total_B
imaginary_unix_1,	1,	imaginary_unix_3,	4
imaginary_unix_2,	2,	imaginary_unix_1,	5
imaginary_unix_3,	3,	imaginary_unix_4,	6"
``` data emulation above ```

Hope this helps.

[ClubMed]

Thank you! The first appendpipe achieved the desired objective! The size constraint should not be a problem because I had all the unixtime snapped to the month with @mon so there's only 300 rows in this table.

The way to explain this odd situation is that each day, we get the data dump of the population but their field values may change by the day.

The issue is that Splunk has a 90 day data retention policy for our events. So basing events purely on _time only goes back 90 days.

BUT, in our events, there are additional unixtime fields (two to be exact) that go back much further than 90 days and we needed to use these to provide a historical month by month view (hence snapping unixtime with @mon).

Total_A was the total sum of the population over time based on Unixtime_A, and Total_B is a conditional sum of the population where only a field met a condition, and Unixtime_B contained the time this condition was first met.

That's why I wanted Total_A and Total_B to be seperate, but Unixtime_A and Unixtime_B could be appended together.

To put some context to it, Total_A is total vulnerabilities population regardless of whether it was fixed or active based on Unixtime_A being when it was first detected. Total_B is total fixed vulnerabilities population based on Unixtime_B being when it was fixed.