How can I join unique fields from 2 different sourcetypes to one column and its values to 2 different columns?

[vijay_datla]

Hi, I am trying to create a table from 2 different sourcetypes. Fields in both source types are same but has different values. I need the uniques fields in 1st column and sourcetype01 field values to column2 and sourcetype02 field values to column3.

Sourcetype: 01

Field01

elementName: SelectSKU
   elementType: transaction
   metrics: { 
     TotalHtmlPageResponseTime: 78163
     TotalHtmlPageTTFB: 6834
     effective90Percentile: 1246

Field02

elementName: SelectProduct
   elementType: transaction
   metrics: { 
     TotalHtmlPageResponseTime: 187545
     TotalHtmlPageTTFB: 187285
     effective90Percentile: 104

Sourcetype: 02

Field01

elementName: SelectSKU
   elementType: transaction
   metrics: { 
     TotalHtmlPageResponseTime: 59545
     TotalHtmlPageTTFB: 1285
     effective90Percentile: 209

Field02

elementName: SelectProduct
   elementType: transaction
   metrics: { 
     TotalHtmlPageResponseTime: 187545
     TotalHtmlPageTTFB: 187285
     effective90Percentile: 756

Field03

elementName: PDP
   elementType: transaction
   metrics: { 
     TotalHtmlPageResponseTime: 4546
     TotalHtmlPageTTFB: 2455
     effective90Percentile: 256

I want to rename the elementName to Transaction and effective90Percentile to 90thPercentile and have a below table:

Transaction                            90th Percentlile (Sourcetype01)                       90th Percentlile (Sourcetype02)

PDP                                                                                                                                             256

SelectSKU                                      1246                                                                              209

SelectProduct                               104                                                                                756

Any advice is appreciated. 

TIA

[bowesmana]

This works by assigning the percentile field to a different named variable according to sourcetype then just doing stats values on those rows

|  makeresults
|  eval _raw="sourcetype,elementName,effective90Percentile
01,SelectSKU,1246
01,SelectProduct,104
02,SelectSKU,209
02,SelectProduct,756
02,PDP,256"
|  multikv forceheader=1
|  eval p90-01=if(sourcetype="01", effective90Percentile, null)
|  eval p90-02=if(sourcetype="02", effective90Percentile, null) 
|  stats values(p90-01) as p90-01 values(p90-02) as p90-02 by elementName 
|  rename p90-01 as "90th Percentile (Sourcetype 01)", p90-02 as "90th Percentile (Sourcetype 02)" elementName as Transaction

but if your data is more complex than this, it probably won't work. For example if you have more than one entry for the same Transaction. Also, this assumes that you have fields called sourcetype, effective90Percentile and elementName as already extracted fields in your data.

[vijay_datla]

Unfortunately, I have around 100 elements from each source type which are identical. but the values of 90percentile  are different in both the sourcetypes for the same elements. I tried the above but did not work.

Any Ideas?

[bowesmana]

Can you clarify whether you have more than one value for the same element in the same sourcetype or are you saying that you have only one value for each element per sourcetype?

Can you give the search you are currently trying

[vijay_datla]

Hi, Every element has only one "effective90Percentile" value in each sourcetype. 

For example in the above pic elementname: "1.AboutUs: History" has one value of effective90Percentile in source type01 and another value in sourcetype02.

Inother words elementname fields are identical in both the source types but has different effective90Percentile in each of sourcetypes. So im looking for a table to have all elamentnames in column01 and its corresponding 90Percentile values from sourcetype01 to column02 and from sourcetype02 to column03.

Sorry, If i have confused you.

[bowesmana]

Hi @vijay_datla 

Can you share the search you are using and the expected values of the sourcetypes in your data. In the query I posted, I assumed your sourcetype values were 01 and 02 respectively - if they are not, then it will not work .

Thanks

[vijay_datla]

Hi @bowesmana ,

Here is the query , and yes i have updated the sourcetypes but no luck:

index=customdata
| eval _raw="sourcetype,elementName,effective90Percentile" |multikv forceheader=1
| eval p90-01=if(sourcetype="Impala_NewWLM", effective90Percentile, null)
| eval p90-02=if(sourcetype="Impala_OldWLM", effective90Percentile, null) | stats values(p90-01) as p90-01 values(p90-02) as p90-02 by elementName
| rename p90-01 as "90th Percentile (Impala_NewWLM )", p90-02 as "90th Percentile (Impala_OldWLM )" elementName as Transaction

Thanks,

Vijay

[bowesmana]

@vijay_datla 

You do not need the 

| eval _raw="sourcetype,elementName,effective90Percentile" |multikv forceheader=1

That was just part of the test sample data setup I provided.

[vijay_datla]

Hi @bowesmana ,

Unfortunately still no luck.

index=customdata
| eval p90-01=if(sourcetype="Impala_NewWLM", effective90Percentile, null)
| eval p90-02=if(sourcetype="Impala_OldWLM", effective90Percentile, null) | stats values(p90-01) as p90-01 values(p90-02) as p90-02 by elementName
| rename p90-01 as "90th Percentile (Impala_NewWLM )", p90-02 as "90th Percentile (Impala_OldWLM )" elementName as Transaction

Its populating the transaction fields in the table but not the 90th Percentile values 😞

[vijay_datla]

Any advices?

[bowesmana]

Do you know what fields are extracted from your data. If there is no effective90Percentile field extracted then you will have to extract it. If you run a search in verbose mode you can see in the events tab the auto extracted fields. If it's not there, then please post an example of the _raw data of your event, so we can give you the correct extraction statement.

[vijay_datla]