How to merge search results into a meaningful data?

[2chs]

Hi There,

Need to combine these two searches meaningfully, can someone help please.

1st Query:

index=xyz ....

| chart count(serviceName) as total count(eval(isPolicySuccessful="true")) as successTotal by serviceName

which gives something like below;

serviceName      total     successTotal
srvc1                   26429       26344
srvc2                       80               80
srvc3                        12              12

2nd Query:

index=xyz ....

| bin _time span=1s
| stats count AS TPS by _time serviceName | eventstats max(TPS) as peakTPS by _time serviceName | eval peakTime=if(peakTPS==TPS,_time,null())
| chart max(TPS) AS "PeakTPS" eval(round(avg(TPS),2)) AS "AVG TPS" min(TPS) AS "MinTPS" first(peakTime) as peakTime by serviceName | fieldformat peakTime=strftime(peakTime,"%x %X")

which gives something like below:

serviceName     PeakTPS       AVG TPS      MinTPS        peakTime
srvc33                11                         1.64                 1             10/15/20 16:34:40
srvc1                     1                          1.00                 1             10/15/20 16:44:42
srvc5                    2                           1.63                 1             10/15/20 20:35:22

Now the problem is how to merge these two results into a meaningful one?

something like below:

serviceName      total     successTotal   PeakTPS       AVG TPS      MinTPS        peakTime
srvc1                   26429       26344                  1                          1.00                 1             10/15/20 16:44:42

Please help!

[gcusello]

Hi @2chs,

I cannot test the search but see the approach, something like this:

index=xyz ....
| eventstats count(serviceName) as total by serviceName
| eventstats count(eval(isPolicySuccessful="true")) as successTotal by serviceName
| eventstats max(TPS) as peakTPS by _time serviceName 
| eval peakTime=if(peakTPS==TPS,_time,null())
| chart values(total) AS total values(successTotal) AS successTotal max(TPS) AS "PeakTPS" eval(round(avg(TPS),2)) AS "AVG TPS" min(TPS) AS "MinTPS" first(peakTime) as peakTime by serviceName 
| fieldformat peakTime=strftime(peakTime,"%x %X")

Ciao.

Giuseppe