correlate data without subsearch or transaction

[stwong]

Hi,

I got data that have some fields missing in some events, e.g.

field1    field2    field3    field4   field5
  A                            val1
            B        val2
  A         B                             val3
  C                            val5
            D        val4
  C         D                             val6  

and want to group by either field1 or field2 to make output like this:

field1    field2    field3    field4   field5
  A          B        val2      val1    val3
  C          D        val4      val5    val6

I tried to avoid join, subsearch, or transaction. Is it possible?
Sorry for newbie question.

Thanks and rgds
/st wong

[elliotproebstel · Accepted solution]

I'd approach this by using eventstats before stats, given the data you presented.

First, cross-apply the values from field1 and field2:

| eventstats values(field1) AS field1 BY field2

This should take your first table and make it look like this:

 field1    field2    field3    field4   field5
   A                            val1
   A         B        val2
   A         B                             val3
   C                            val5
   C         D        val4
   C         D                             val6  

Now every row in the table has a value for field1, making it possible to use stats to populate the table:

| stats values(field2) AS field2 values(field3) AS field3 values(field4) AS field4 values(field5) AS field5 BY field1

If that doesn't work, it means the table likely has some instances where there is no value for field1, like maybe this:

  field1    field2    field3    field4   field5
    A                            val1
    A         B        val2
    A         B                             val3
    C                            val5
    C         D        val4
    C         D                             val6
              E                             val7
              E                  val8

If your data has events like that, I can help you adjust your search. It'll be a little more complex, but it's still doable.

[stwong]

Hi, it works perfectly. Thanks a lot.

[p_gurav]

Did you tryappendcols? Like:

..... | stats values(field3) AS field3 values(field4) AS field4 values(field5) AS field5  by field1 | appendcols [search index="abc_new" sourcetype="csv" | stats values(field3) AS field3 values(field4) AS field4 values(field5) AS field5  by field2] |table field1 field2 field3 field4 field5

[stwong]

Thanks, but can appendcols do something like following?

..| stats values(field3) as field3, values(field4) as field4, values(field5) as field5 by "either field1 or field2" ?

and also hope to avoid subsearches.

Thanks and rgds