How to correlate two different sourcetypes.

[allan_newton]

I have two sourcetypes

src_type_data and src_type_scale.

src_type_data contains two fields

---------------------------
User_Type |  amount spent |
---------------------------
   0      |     30        |
   0      |     12        |
   0      |     32        |
   0      |     18        |
   1      |     102       |
   1      |     30        |
   1      |     39        |
   1      |     235       |
   1      |     17        |
---------------------------

src_type_scale contains only one field "Scale"

------------
   Scale    |
------------
     0.01   |
     0.02   |
     .      |
     .      |    
     .      |
     4.99   |    
     5.00   |
-------------    

There are two types of users differentiated by code 0,1. Now I have to compute mean of amount_spent for 0 and 1 separately and i have to apply normal distribution over the scale. Then display a table like
Formula to compute Normaldist will be eval norm_dist_0=mean(amount_spent for 0)-Scale.

---------------------------------
Scale | normdist_0 | normdist_1 |
---------------------------------
0.01  |  0.24      | 0.011      |
0.02  |  0.28      | 0.019      |
.     |   .        |  .         |
.     |   .        |  .         |
5.00  |  0.001     |  0.009     |
---------------------------------

[chris · Accepted solution]

The date you have is in csv format or has been reduced to a tabular format using the search language right?

To play around I converted what is listed below src_type_data to user.csv and the list below src_type_scale to scale.csv

The csv files were created in $SPLUNK_HOME/etc/system/lookups

The following search does display a table with the fields you want, but I don't quite understand how the scale and data are related, the result doesn't have a lot to do with normal distribution...

| inputlookup user.csv | stats mean(spent) as  spent by type | map search="| inputlookup scale.csv | eval type=$type$ | eval dist=tonumber($spent$)-Scale "| chart avg(dist) by Scale,type