Hi
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.
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
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
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
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>
Do not use join
, use | tstats first stuff | tstats append=t second stuff | stats values (*) AS * BY NPID
.
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