Dashboards & Visualizations

Merge two rows based on common field value

charan986
Engager

I've a table like below and I want to merge two rows based on the COMMONID

1.    JBID    JOBTYPE                     START_TIME             END_TIME          COMMONID
2.                                2020-03-10T06:30:00   2020-03-10T08:30:00   abc
3.   6398   Medium                                                                abc
4.   5649   Medium                                                                def
5.                                2020-03-10T08:30:00   2020-03-10T10:30:00   def
6.   5649   Medium                                                                ghi
7.                                2020-03-20T08:30:00   2020-03-20T10:30:00   ghi
8.                                2020-03-11T08:30:00   2020-03-11T10:30:00   jkl
9.   6383   Medium                                                               jkl
10.  7070   Medium                                                                mno
11.                                  2020-03-10T08:30:00    2020-03-10T10:30:00   mno
12.  11690  Medium                                                               pqr
13.                                  2020-03-12T06:30:00    2020-03-12T08:30:00   pqr
14.                                  2020-03-19T06:30:00    2020-03-19T08:30:00   stu
15.  6398   Medium                                                                stu
16.  6398   Medium                                                                vwx
17.                                  2020-03-10T06:30:00    2020-03-10T08:30:00   vwx

The resulting table should look like below

1.  JBID    JOBTYPE                                START_TIME             END_TIME          COMMONID
2.  6398    Medium                            2020-03-10T06:30:00   2020-03-10T08:30:00   abc
3.  5649    Medium                          2020-03-10T08:30:00 2020-03-10T10:30:00   def
4.  5649    Medium                          2020-03-20T08:30:00 2020-03-20T10:30:00   ghi
5.  6383    Medium                          2020-03-11T08:30:00 2020-03-11T10:30:00   jkl
6.  7070    Medium                          2020-03-10T08:30:00 2020-03-10T10:30:00   mno
7. 11690    Medium                          2020-03-12T06:30:00 2020-03-12T08:30:00   pqr
8.  6398    Medium                          2020-03-19T06:30:00 2020-03-19T08:30:00   stu
9.  6398    Medium                          2020-03-10T06:30:00 2020-03-10T08:30:00   vwx

How do I achieve this?

Labels (1)
Tags (1)
0 Karma
1 Solution

manjunathmeti
Champion

hi @charan986

You can do this using stats command:

<base search>| stats first(*) as * by COMMONID

View solution in original post

woodcock
Esteemed Legend

I typically do this:

... | stats list(*) AS * BY COMMONID

Just in case there are some duplicated lines.

0 Karma

to4kawa
SplunkTrust
SplunkTrust
....
| selfjoin COMMONID

manjunathmeti
Champion

hi @charan986

You can do this using stats command:

<base search>| stats first(*) as * by COMMONID

View solution in original post

charan986
Engager

Thanks, it worked 🙂

0 Karma