topic Re: After using a JOIN i now have multiple lines, however this has affected my maths as before i have sumed up maths, now i have maths per line in Splunk Search

After using a JOIN i now have multiple lines, however this has affected my maths as before i have sumed up maths, now i have maths per line

robertlynch2020 — Wed, 12 Jul 2017 14:21:45 GMT

I use a JOIN and now i have multiple lines and not unique ones.
It returned one line per unique Context+Command.

Before
GROUPBY Context+Command
One line - This was working fine 🙂

After
GROUPBY Context+Command NPID
Now multiple lines

ORGINAL - One line
However i have to GROUPBY Context+Command NPID now and i am getting back multiple lines of maths. Before i work out how to re-run the maths on the outputted data, i want to know if i can do this with the JOIN?

| tstats summariesonly=true 
avg(MXTIMING.Elapsed) AS average, 
count(MXTIMING.Elapsed) AS count, 
stdev(MXTIMING.Elapsed) AS stdev, 
median(MXTIMING.Elapsed) AS median, 
exactperc95(MXTIMING.Elapsed) AS perc95, 
exactperc99.5(MXTIMING.Elapsed) AS perc99.5, 
min(MXTIMING.Elapsed) AS min, 
max(MXTIMING.Elapsed) AS max,
earliest(_time) as start, 
latest(_time) as stop 

FROM datamodel=MXTIMING 
WHERE host=RABO_SYSTEM_TEST 
AND MXTIMING.source_path = */net/dell430srv/dell430srv/apps/QCST_RABO_v3.1.8_SEC2/logs/traces/ 
AND MXTIMING.UserName2=* 
AND MXTIMING.NPID=*** 
AND GROUPBY MXTIMING.Context+Command 
AND Context+Command = *BTN_Yes#* 
AND Context+Command = *#**

| rename MXTIMING.Context+Command as Context+Command  |eval time_slice_per_min = (stop-start)/60 | eval Throughput_per_minute= count/time_slice_per_min 
| eval average = round(average, 1) | eval average=tostring(average, "commas") | eval stdev = round(stdev, 1) | sort - average | fields - stop - start |

NEW - Multiple lines
NOW I need to do a JOIN from another TABLE on NPID, however i am getting multiple lines out now. I want to get the original .

| tstats summariesonly=true avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min, max(MXTIMING.Elapsed) AS max,earliest(_time) as start, latest(_time) as stop FROM datamodel=MXTIMING_TEST WHERE 
           host=RABO_SYSTEM_TEST_MXTIMING 
AND MXTIMING.source_path = ** 
AND MXTIMING.UserName2=* 
AND MXTIMING.NPID=***  
AND MXTIMING.TYPE8=STANDARD 
AND MXTIMING.Context+Command = *BTN_Yes#*
AND MXTIMING.Context+Command = *#**
GROUPBY MXTIMING.Context+Command MXTIMING.NPID | rename MXTIMING.Context+Command as Context+Command  |rename MXTIMING.NPID as NPID 

| join NPID [| tstats summariesonly=true count(SERVICE.NPID) AS count2 FROM datamodel=SERVICE WHERE ( host=RABO_SYSTEM_TEST_MXTIMING) 
 AND SERVICE.NICKNAME = mx 
GROUPBY SERVICE.NICKNAME SERVICE.NPID  | rename SERVICE.NPID AS NPID ] 

|eval time_slice_per_min = (stop-start)/60 | eval Throughput_per_minute= count/time_slice_per_min | eval average = round(average, 1) | eval average=tostring(average, "commas") | eval stdev = round(stdev, 1) | sort - average

Now i understand why i get Multiple, lines (GROUPBY MXTIMING.Context+Command MXTIMING.NPID), but before i have to write code to re-calculate is there a way of doing it with ONE JOIN.

Re: After using a JOIN i now have multiple lines, however this has affected my maths as before i have sumed up maths, now i have maths per line

woodcock — Thu, 13 Jul 2017 01:50:51 GMT

Do not use join, use | tstats first stuff | tstats append=t second stuff | stats values (*) AS * BY NPID.

Re: After using a JOIN i now have multiple lines, however this has affected my maths as before i have sumed up maths, now i have maths per line

robertlynch2020 — Tue, 29 Sep 2020 14:52:02 GMT

In fact this does not seem to work (I thought it did at the start, sorry)
One small comment, needed to add - | tstats prestats=true append=t to get it to run

The final command looks like below however i have a token for SERVICE.NICKNAME when i change it nothing updates, so the second tstats is not having any effect.

The overall issues is i have 2 tables that i am trying to run maths off. They have a NPID in common.

I am looking for a way to include/exclude the data both ways, so a user enters NICKNAME "MX" NPID 1 and 2 are selected in SERVICE-DATAMODEL, then maths is driven off MXTIMING-DATAMODEL table on NPID's 1 and 2.

Also there is the reverse, where a user can pick TYPE "STANDARD" and NPID's 1 and 3 are used

SERVICE-DATAMODEL

NPID NICKNAME

1 MX

2 MX

3 ENGINE

MXTIMING-DATAMODEL

NPID TYPE Elapsed
1 STANDARD 2
1 STANDARD 1
1 WAREHOUSE 3
1 REAL_TIME 1
2 AAA 3
2 AAA 4
2 AAA 1
2 WAREHOUSE 1
3 REAL_TIME 4
3 STANDARD 1

This i how i tried to applay your solution, but the second tstats is having no affect, perhaps i am missing something.

| tstats summariesonly=true avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min, max(MXTIMING.Elapsed) AS max,earliest(_time) as start, latest(_time) as stop FROM datamodel=MXTIMING_TEST WHERE 
           host=RABO_SYSTEM_TEST_MXTIMING2 
AND MXTIMING.source_path = ** 
AND MXTIMING.UserName2=* 
AND MXTIMING.NPID=***  
AND MXTIMING.TYPE8=STANDARD 
AND MXTIMING.Context+Command = **#*
AND MXTIMING.Context+Command = *#**
AND MXTIMING.Time = *
GROUPBY MXTIMING.Context+Command MXTIMING.NPID MXTIMING.Time | rename MXTIMING.Context+Command as Context+Command  |rename MXTIMING.NPID as NPID 

|  tstats prestats=true append=t summariesonly=true count(SERVICE.NPID) AS count2 FROM datamodel=SERVICE WHERE ( host=RABO_SYSTEM_TEST_MXTIMING2) 
 AND SERVICE.NICKNAME = mx 
GROUPBY SERVICE.NICKNAME SERVICE.NPID  | rename SERVICE.NPID AS NPID | stats avg(average) as average count(count) as count stdev(average) as stdev median(median) as median  exactperc95(perc95) AS perc95, exactperc99.5(perc99.5) AS perc99.5, min(min) AS min, max(max) AS max,earliest(_time) as start, latest(_time) as stop by Context+Command

Re: After using a JOIN i now have multiple lines, however this has affected my maths as before i have sumed up maths, now i have maths per line

robertlynch2020 — Fri, 14 Jul 2017 14:48:54 GMT

Hi @woodcock

In the end i can't get the | tstats first stuff | tstats append=t second stuff | stats values (*) AS * BY NPID to work. I think because i have to use GROUP by MXTIMING.Context+Command as i need to see unique lines of each of them.

I did get the Group by working, but i hit such a strange bug.

After the GROUP by i had to remove all my evals:

|eval time_slice_per_min = (stop-start)/60  | eval Throughput_per_minute= count/time_slice_per_min | eval average = round(average, 1) | eval average=tostring(average, "commas") | eval stdev = round(stdev, 1) | sort - average |

as it was removing lines before the stats could get to them!!! - I have no idea why.

working code

 <query>| tstats summariesonly=$summariesonly_token$ avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min, max(MXTIMING.Elapsed) AS max,earliest(_time) as start, latest(_time) as stop FROM datamodel=MXTIMING_TEST WHERE 
           host=$host_token$ 
AND MXTIMING.source_path = *$source_path_search_token$ 
AND MXTIMING.UserName2=$MXTIMING_UserName_token$ 
AND MXTIMING.NPID=*$MXTIMING_NPID_token$*  
AND MXTIMING.TYPE8=$MXTIMING_TYPE_TOKEN$ 
AND MXTIMING.Context+Command = *$MXTIMING_Context_token$#*
AND MXTIMING.Context+Command = *#$MXTIMING_Command_token$*
AND MXTIMING.Time = *
GROUPBY MXTIMING.Context+Command MXTIMING.NPID MXTIMING.Time | rename MXTIMING.Context+Command as Context+Command  |rename MXTIMING.NPID as NPID 

| join NPID [| tstats summariesonly=$summariesonly_token$ count(SERVICE.NPID) AS count2 FROM datamodel=SERVICE WHERE ( host=$host_token$) 
 AND SERVICE.NICKNAME = $NICKNAME_TOKEN$ 
GROUPBY SERVICE.NICKNAME SERVICE.NPID  | rename SERVICE.NPID AS NPID ] 

 | stats avg(average) as average count(count) as count stdev(average) as stdev median(median) as median  exactperc95(perc95) AS perc95, exactperc99.5(perc99.5) AS perc99.5, min(min) AS min, max(max) AS max,earliest(_time) as start, latest(_time) as stop by Context+Command |</query>

Not working code

<query>| tstats summariesonly=$summariesonly_token$ avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min, max(MXTIMING.Elapsed) AS max,earliest(_time) as start, latest(_time) as stop FROM datamodel=MXTIMING_TEST WHERE 
           host=$host_token$ 
AND MXTIMING.source_path = *$source_path_search_token$ 
AND MXTIMING.UserName2=$MXTIMING_UserName_token$ 
AND MXTIMING.NPID=*$MXTIMING_NPID_token$*  
AND MXTIMING.TYPE8=$MXTIMING_TYPE_TOKEN$ 
AND MXTIMING.Context+Command = *$MXTIMING_Context_token$#*
AND MXTIMING.Context+Command = *#$MXTIMING_Command_token$*
AND MXTIMING.Time = *
GROUPBY MXTIMING.Context+Command MXTIMING.NPID MXTIMING.Time | rename MXTIMING.Context+Command as Context+Command  |rename MXTIMING.NPID as NPID 

| join NPID [| tstats summariesonly=$summariesonly_token$ count(SERVICE.NPID) AS count2 FROM datamodel=SERVICE WHERE ( host=$host_token$) 
 AND SERVICE.NICKNAME = $NICKNAME_TOKEN$ 
GROUPBY SERVICE.NICKNAME SERVICE.NPID  | rename SERVICE.NPID AS NPID ] 

|eval time_slice_per_min = (stop-start)/60  | eval Throughput_per_minute= count/time_slice_per_min | eval average = round(average, 1) | eval average=tostring(average, "commas") | eval stdev = round(stdev, 1) | sort - average | stats avg(average) as average count(count) as count stdev(average) as stdev median(median) as median  exactperc95(perc95) AS perc95, exactperc99.5(perc99.5) AS perc99.5, min(min) AS min, max(max) AS max,earliest(_time) as start, latest(_time) as stop by Context+Command </query>

Re: After using a JOIN i now have multiple lines, however this has affected my maths as before i have sumed up maths, now i have maths per line

woodcock — Sun, 10 Sep 2017 20:01:18 GMT

Like this:

| tstats summariesonly=$summariesonly_token$ avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min, max(MXTIMING.Elapsed) AS max,earliest(_time) as start, latest(_time) as stop FROM datamodel=MXTIMING_TEST WHERE 
            host=$host_token$ 
 AND MXTIMING.source_path = *$source_path_search_token$ 
 AND MXTIMING.UserName2=$MXTIMING_UserName_token$ 
 AND MXTIMING.NPID=*$MXTIMING_NPID_token$*  
 AND MXTIMING.TYPE8=$MXTIMING_TYPE_TOKEN$ 
 AND MXTIMING.Context+Command = *$MXTIMING_Context_token$#*
 AND MXTIMING.Context+Command = *#$MXTIMING_Command_token$*
 AND MXTIMING.Time = *
 GROUPBY MXTIMING.Context+Command MXTIMING.NPID MXTIMING.Time | rename MXTIMING.Context+Command as Context+Command
| rename MXTIMING.NPID as NPID 

| tstats append=t summariesonly=$summariesonly_token$ count(SERVICE.NPID) AS count2 FROM datamodel=SERVICE WHERE ( host=$host_token$) 
  AND SERVICE.NICKNAME = $NICKNAME_TOKEN$ 
 GROUPBY SERVICE.NICKNAME SERVICE.NPID
| rename SERVICE.NPID AS NPID

| stats values(*) AS * BY NPID

| stats avg(average) as average count(count) as count stdev(average) as stdev median(median) as median  exactperc95(perc95) AS perc95, exactperc99.5(perc99.5) AS perc99.5, min(min) AS min, max(max) AS max,earliest(_time) as start, latest(_time) as stop by Context+Command

Re: After using a JOIN i now have multiple lines, however this has affected my maths as before i have sumed up maths, now i have maths per line

robertlynch2020 — Fri, 06 Oct 2017 09:04:21 GMT

Yes, the mistake i was making was running evals before i had pulled all me data back. The final query was this one.

| tstats summariesonly=true avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min, max(MXTIMING.Elapsed) AS max,earliest(_time) as start, latest(_time) as stop FROM datamodel=MXTIMING_TEST WHERE 
           host=TALANX_PostGoLive 
AND MXTIMING.Elapsed >  5 
           AND MXTIMING.source_path = ** 
AND MXTIMING.UserName2=* 
AND MXTIMING.NPID=***  
AND MXTIMING.MXTIMING_TYPE_DM=STANDARD 
AND MXTIMING.Context+Command = **#*
AND MXTIMING.Context+Command = *#**
AND MXTIMING.Time = *
GROUPBY MXTIMING.Context+Command MXTIMING.NPID MXTIMING.Time | rename MXTIMING.Context+Command as Context+Command  |rename MXTIMING.NPID as NPID 
| join NPID [| tstats summariesonly=true count(SERVICE.NPID) AS count2 FROM datamodel=SERVICE WHERE ( host=TALANX_PostGoLive) 
 AND SERVICE.NICKNAME = mx 
GROUPBY SERVICE.NICKNAME SERVICE.NPID  | rename SERVICE.NPID AS NPID ]  | join Context+Command  type=left [inputlookup TEST_MXTIMING.csv | rename Context_Command AS Context+Command  ] |  search | where average > Threshold OR isnull('Threshold') | fillnull Tags | eval Tags=if(Tags=0,"NO_TAG",Tags) | search Tags = *
 | stats avg(average) as average count(count) as count stdev(average) as stdev median(median) as median  exactperc95(perc95) AS perc95, exactperc99.5(perc99.5) AS perc99.5, min(min) AS min, max(max) AS max,earliest(_time) as start, latest(_time) as stop by Context+Command Tags | sort - average