topic Re: How can I merge 2 tabled rows and add field values from columns as new fields? in Splunk Search

How can I merge 2 tabled rows and add field values from columns as new fields?

nidhsha2 — Mon, 27 Feb 2017 15:46:36 GMT

I am looking to combine columns/values from row 2 to row 1 as additional columns. I am not sure which commands should be used to achieve this and would appreciate any help.

Example:

Current format

Desired format

Re: How can I merge 2 tabled rows and add field values from columns as new fields?

somesoni2 — Mon, 27 Feb 2017 17:13:11 GMT

Give this a try

your current search giving fields Date A B C
| eventstats first(Date) as Date
| untable Date metrics data
| streamstats count by metrics | eval metrics=metrics."_".count
| xyseries Date metrics data

Re: How can I merge 2 tabled rows and add field values from columns as new fields?

DalJeanis — Mon, 27 Feb 2017 21:08:30 GMT

somesoni2's answer is perfect if either (1) you will only ever have 2 days in your search, or (3) however many days ARE returned, you want to display them all horizontally.

Where you have more than two days, this strategy will compare each day's results to the following day's.

This part just makes test data-

| makeresults 
| eval mydata="7/1/2016,1,2,3 7/2/2016,4,5,6 7/3/2016,7,8,9" 
| makemv mydata
| mvexpand mydata 
| makemv delim="," mydata
| eval Date = strptime(mvindex(mydata,0),"%m/%d/%Y")
| eval A=mvindex(mydata,1)
| eval B=mvindex(mydata,2)
| eval C=mvindex(mydata,3)
| table Date A B C

This part spreads each day's data into two sets, one to be the current day and one to compare with the prior day.

| eval switcher="left right"
| makemv switcher 
| mvexpand switcher
| eval Date=if(switcher="left",Date,Date-86400)
| eval A_2=if(switcher="left",Null(),A)
| eval A=if(switcher="left",A,Null())
| eval B_2=if(switcher="left",Null(),B)
| eval B=if(switcher="left",B,Null())
| eval C_2=if(switcher="left",Null(),C)
| eval C=if(switcher="left",C,Null())

This part collects it up and displays it.

| stats values(*) as * by Date 
| where like(A,"%") AND like(A_2,"%")
| eval DateDisplay=strftime(Date,"%Y-%m-%d")
| table DateDisplay A B C A_2 B_2 C_2

Re: How can I merge 2 tabled rows and add field values from columns as new fields?

DalJeanis — Mon, 27 Feb 2017 21:24:42 GMT

Here's test code for the curious.

| makeresults 
| eval mydata="7/1/2016,1,2,3 7/2/2016,4,5,6" 
| makemv mydata
| mvexpand mydata 
| makemv delim="," mydata
| eval Date = strptime(mvindex(mydata,0),"%m/%d/%Y")
| eval A=mvindex(mydata,1)
| eval B=mvindex(mydata,2)
| eval C=mvindex(mydata,3)
| table Date A B C


| eventstats first(Date) as Date
| untable Date metrics data
| streamstats count by metrics 
| eval metrics=metrics."_".count
| xyseries Date metrics data
| eval DateDisplay=strftime(Date,"%Y-%m-%d")

Explanation of the code:

Everything before the space just creates test data.

Eventstats keeps only the earliest date , throwing away the unneeded date from the second record.

Untable creates records that (in this case) all have the same Date, each record with the field name in "metrics" and the field value in "data".

Streamstats determines a suffix for each field name so that the combined field name will be unique. This usage assumes the data for the earliest date will appear first in the underlying data.

Eval appends the suffix onto the field name in metrics in order to create the unique field name.

Being a belt and suspenders and duct-tape sort of a person, I dislike verbs and functions --first() and streamstats in this case-- that depend on an event order that I can't see by inspection of the code. I'd probably amend the code to this :

| sort 0 Date
| untable Date metrics data
| streamstats count by metrics 
| eval metrics=metrics."_".count
| eventstats min(Date) as Date
| xyseries Date metrics data
| eval DateDisplay=strftime(Date,"%Y-%m-%d")

Re: How can I merge 2 tabled rows and add field values from columns as new fields?

nidhsha2 — Wed, 01 Mar 2017 15:27:26 GMT

Thanks! This is exactly what I am looking for. The only issue I am hitting now is the last line....transforming to xyseries. I do not necessarily care about the date now but if I execute your last line I get "no results found".

Re: How can I merge 2 tabled rows and add field values from columns as new fields?

somesoni2 — Wed, 01 Mar 2017 15:38:13 GMT

So the xyseries command is failing? Do you get results with field name Date, metrics and data (case sensitive)? You can also replace xyseries command with following

| chart values(data) over Date by metrics