Combine 2 searches with sub search into One Time Chart

[prasad984m]

I have 2 queries which is having sub search for input look up in each.

Query 1

This query outputs the timechart for for CPU1. It will count each processes listed in the CPU1 field of the test.csv. 

index=custom | eval SEP=split(_raw,"|"), eval CPU1=trim(mvindex(SEP,1))
| bin _time span=1m
| stats count(CPU1) as CPU1_COUNT by _time CPU1

| search 
[ |  input lookup test.csv  | fields CPU1 | fillnull value = 0 |  format ]

Query 2

This query outputs the timechart for for CPU2. It will count each processes listed in the CPU2 field of the test.csv. 

index=custom | eval SEP=split(_raw,"|"), eval CPU2=trim(mvindex(SEP,1))
| bin _time span=1m
| stats count(CPU2) as CPU2_COUNT by _time CPU2

| search 
[ |  input lookup test.csv  | fields CPU2 | fillnull value = 0 |  format ]

test.csv (sample)

CPU1	CPU2	CPU3
process_a	process_b	process_c
process_d	process_e	process_f
process_g	process_i	process_h

What I want is to display the CPU1 and CPU2 time chart in one chart . 

Any advice on that will be a great help.

Thanks

[P_vandereerden]

The CSV is not structured as a lookup table. The structure should be that, given a value for CPU1 (e.g. "process_a"), what are the (first matching) values for CPU2 ("process_b") and CPU3 ("process_c").

What you seem to be looking for is given a value for some CPU (e.g. "process_a"), to what CPU category does it belong ("CPU1").

Are you able to restructure the test.csv to be more like:

Process	CPU Class
process_a	CPU1
process_b	CPU2
process_c	CPU3
process_d	CPU1
process_e	CPU2
process_f	CPU3
process_g	CPU1
process_h	CPU2
process_i	CPU3

IF you can't restructure that file, something like this would work:

| makeresults 
| eval CPU=mvappend("process_a","process_a","process_b","process_a","process_c","process_a","process_b","process_d","process_a","process_e","process_a","process_b","process_c","process_a","process_a","process_b","process_d","process_a","process_c","process_a","process_b","process_e","process_a")
| mvexpand CPU
``` The above is to generate sample data and can be ignored in your SPL ```
``` uncomment the line below and notice the change from CPU1 to CPU ```
```index=custom | eval SEP=split(_raw,"|"), eval CPU=trim(mvindex(SEP,1))```
``` These two lines create aliases to map in the CPU group for each class in turn ```
| eval myCPU1=CPU
| eval myCPU2=CPU
``` These next lines assume that a process will only appear once in the test.csv file. ``` 
``` If that is the case, then CPU2 and CPU3 will be non-null when CPU1 matches, ```
``` otherwise that process does not belong to CPU1 (and ditto for the CPU2 case.) ```
| lookup community CPU1 as myCPU1 | eval myCPU1=if(NOT isnull(CPU2),CPU,NULL) 
| lookup community CPU2 as myCPU2 | eval myCPU2=if(NOT isnull(CPU1),CPU,NULL)
``` Now create your stats on the two CPU classes. ```
| bin _time span=1m
| stats count(myCPU1) as CPU1_COUNT count(myCPU2) as CPU2_COUNT by _time