(index=geniachip AND (geniaComplete.flag OR "DVT ready" OR "transfer complete for all banks" OR "lz4.complete*" OR "On-station compression complete.") OR (index=fbu_sizes)
| eventstats values(fbuLZ4Size) AS fbuLZ4Size by run_name
| eval run_id_date_size = run_id."##".date."@@".fbuLZ4Size
| eval message=if(message LIKE "geniaComplete.flag%", "geniaComplete.flag", message)
| eval message=if(message LIKE "lz4.complete%", "lz4.complete", message)
| eval start_time=case(message="geniaComplete.flag", timestamp,
message="lz4.complete", timestamp,
message="transfer complete for all banks.", unix_time,
message="DVT ready", unix_time,
message="On-station compression complete.", unix_time)
| chart values(start_time) over run_id_date_size by message
| eval fbuLZ4Size=mvindex(split(run_id_date_size,"@@"),1)
| eval fbuLZ4SizeGB = fbuLZ4Size/1000000
| search geniaComplete.flag = *
| eval "Xfer Time" = ('transfer complete for all banks.' - 'On-station compression complete.')/60
| eval "ACAP Time" = ('DVT ready' - 'transfer complete for all banks.')/60
| eval "ubf_compress" = ('lz4.complete' - 'geniaComplete.flag')/60
| eval "Total Time" = ('DVT ready' - 'geniaComplete.flag')/60
| eval count=if('ubf_compress' > 30, 1, 0)
| eval count1=if('Xfer Time' > 60, 1, 0)
| eval count2=if('ACAP Time' > 60, 1, 0)
| eval count3=if('Total Time' > 150, 1, 0)
| stats dc(run_id_date_size) AS "Total Runs", sum(count) AS "ubfcompress", sum(count1) AS "Xfer Time", sum(count2) AS "ACAP Time", sum(count3) AS "Total", median("Xfer Time") AS med_xfer_time, median("ACAP Time") AS med_ACAP_time, median("ubf_compress") AS med_ubf_compress, median("Total Time") AS med_tot_time
| eval "UBFcompress lag" = ('ubfcompress'/'Total Runs') * 100
| eval "Transfer Time" = ('Xfer Time'/'Total Runs') * 100
| eval "ACAP Processing" = ('ACAP Time'/'Total Runs') * 100
| eval "Total TAT" = ('Total'/'Total Runs') * 100
| fields - "Total Runs" "ubfcompress" "ACAP Time" "Xfer Time" "Total" "Xfer Rate"
| transpose
| eval Threshold = case(column=="UBFcompress lag", "30 Minutes",
column=="Total TAT", "150 Minutes",
column=="Transfer Time", "60 Minutes",
column=="ACAP Processing", "60 Minutes")
| eval sort_field = case(column=="UBFcompress lag", 2,
column=="Total TAT", 1,
column=="Transfer Time", 3,
column=="ACAP Processing", 5)
| sort sort_field
| fields - sort_field
| rename column AS "Turnaround Time Process"
| eval "row 1" = round('row 1', 1)
| rename "row 1" AS "Percent Runs over Threshold"
| table "Turnaround Time Process" Threshold "Percent Runs over Threshold" Median
This search pulls timestamps for checkpoints in our pipeline. I utilize these checkpoints to determine the length of time the process takes. I then need to compare the time for each individual "run" to a threshold in order to get the percentage of runs that took longer than that threshold on that specific process. I was able to do all that, however, I got a separate request to also display the median for each process which complicated things with the use of the transpose command.
Currently the end result from the above query looks like this
Turnaround Time Process Threshold Percent Runs over Threshold Median
Total TAT 150 Minutes 22.8
UBFcompress lag 30 Minutes 39.0
Transfer Time 60 Minutes 3.8
ACAP Processing 60 Minutes 8.2
med_xfer_time 4.3
med_ACAP_time 34.8
med_ubf_compress 12.0
med_tot_time 106.3
I'd like to save each of the med_* values to a median field matched to the respective process. So the final table should look like
Turnaround Time Process Threshold Percent Runs over Threshold Median
Total TAT 150 Minutes 22.8 106.3
UBFcompress lag 30 Minutes 39.0 12.0
Transfer Time 60 Minutes 3.8 4.3
ACAP Processing 60 Minutes 8.2 34.8
I'm having trouble using the stats command (to get the median values) in conjunction with the transpose command as I can't save the field values of the med_* to a new field (Median).
Any help/tips would be much appreciated!
... View more