topic correlate data without subsearch or transaction in Splunk Search

correlate data without subsearch or transaction

stwong — Tue, 27 Mar 2018 10:07:46 GMT

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

Re: correlate data without subsearch or transaction

p_gurav — Tue, 27 Mar 2018 10:39:20 GMT

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

Re: correlate data without subsearch or transaction

stwong — Tue, 27 Mar 2018 12:21:43 GMT

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

Re: correlate data without subsearch or transaction

elliotproebstel — Tue, 27 Mar 2018 13:33:01 GMT

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.

Re: correlate data without subsearch or transaction

stwong — Wed, 28 Mar 2018 09:51:13 GMT

Hi, it works perfectly. Thanks a lot.