I have a requirement to find the average duration taken by the SERVICE
in a day.
Expected o/p1 for 1st service query:
DATE SERVICE AVERAGE(SEC)
2014-01-22 WORST 6.5 (calculation: 09+04/2(no of worst services)
Expected o/p2 for another service query:
DATE SERVICE AVERAGE(SEC)
2014-01-22 BEST 7.3 (calculation: 07+08+07/3(no of best service)
Here are some sample raw evens:
20140122T100510 SERVICE=WORST MESSAGE=REQ COUNTRY=USA ACCNO=1234
20140122T100513 SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD COUNT=1
20140122T100515 SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD COUNT=1
20140122T100516 SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD COUNT=0
20140122T100519 SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD COUNT=1 (20140122T100510-20140122T100519)=09sec
20140122T080520 SERVICE=WORST MESSAGE=REQ COUNTRY=USA ACCNO=1000
20140122T080524 SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1000 TYPE=GOLD COUNT=0 (20140122T080520- 20140122T080520)=04sec
20140122T100514 SERVICE=BEST MESSAGE=REQ COUNTRY=USA ACCNO=1432
20140122T100515 SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1432 TYPE=SILVER COUNT=1
20140122T100519 SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1432 TYPE=SILVER COUNT=0
20140122T100521 SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1432 TYPE=SILVER COUNT=1 (07sec)
20140122T100543 SERVICE=BEST MESSAGE=REQ COUNTRY=USA ACCNO=1256
20140122T100551 SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1256 TYPE=SILVER COUNT=1 (08sec)
20140122T100545 SERVICE=BEST MESSAGE=REQ COUNTRY=USA ACCNO=1900
20140122T100552 SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1900 TYPE=SILVER COUNT=1 (07sec)
The query I tried is:
Index= mmartin st=m01 host=defr4456 SERVICE=WORST |transaction SERVICE startswith=REQ endswith RES |timechart span=1d avg(duration) as AVERAGE(SEC)
(not getting proper results for this query)
any ideas??
Thanks..!!
-MARTIN
try this -
Index= mmartin st=m01 host=defr4456 SERVICE=*
|transaction SERVICE ACCNO startswith=REQ maxspan=1m
1) RES is on multiple records before the end of the transaction, so you can't use it for the endswith parameter.
2) All the records must have the same ACCNO (or they aren't relevant to each other and aren't part of a single transaction)
3) Since you are looking at 5-to-10 second response times, I set the maximum duration (maxspan) for a transaction to be 1m. If you expect transactions longer than that, increase it to 2m or 3m or whatever.
Now you have a single transaction record for each chunk of events, contianing their field values and the duration. _time contains the start of the transaction. You can apply the timechart function now.
| timechart span=1d avg(duration) as avgDuration by service
By the way, this creates your test data -
|makeresults | eval mydata = mvappend("20140122T100510!!!!SERVICE=WORST MESSAGE=REQ COUNTRY=USA ACCNO=1234",
"20140122T100513!!!!SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD COUNT=1",
"20140122T100515!!!!SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD COUNT=1",
"20140122T100516!!!!SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD COUNT=0",
"20140122T100519!!!!SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD COUNT=1",
"20140122T080520!!!!SERVICE=WORST MESSAGE=REQ COUNTRY=USA ACCNO=1000",
"20140122T080524!!!!SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1000 TYPE=GOLD COUNT=0",
"20140122T100514!!!!SERVICE=BEST MESSAGE=REQ COUNTRY=USA ACCNO=1432",
"20140122T100515!!!!SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1432 TYPE=SILVER COUNT=1",
"20140122T100519!!!!SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1432 TYPE=SILVER COUNT=0",
"20140122T100521!!!!SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1432 TYPE=SILVER COUNT=1",
"20140122T100543!!!!SERVICE=BEST MESSAGE=REQ COUNTRY=USA ACCNO=1256",
"20140122T100551!!!!SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1256 TYPE=SILVER COUNT=1",
"20140122T100545!!!!SERVICE=BEST MESSAGE=REQ COUNTRY=USA ACCNO=1900",
"20140122T100552!!!!SERVICE=BEST MESSAGE=RES COUNTRY=USA ACCNO=1900 TYPE=SILVER COUNT=1")
| mvexpand mydata
| eval mydata=split(mydata,"!!!!")
| eval _time = strptime(mvindex(mydata,0),"%Y%m%dT%H%M%S")
| eval _raw = mvindex(mydata,1)
| table _time _raw
| sort -_time
| rex field=_raw "SERVICE=(?<SERVICE>[^\s]*)\s*MESSAGE=(?<MESSAGE>[^\s]*)\s*COUNTRY=(?<COUNTRY>[^\s]*)\s*ACCNO=(?<ACCNO>[^\s]*)\s*TYPE=(?<TYPE>[^\s]*)\s*COUNT=(?<COUNT>[^\s]*)"
| table _time _raw SERVICE MESSAGE COUNTRY ACCNO TYPE COUNT
and this finishes up, matching the case of the above
| transaction SERVICE ACCNO startswith=REQ maxspan=1m
| timechart span=1d avg(duration) as avgDuration by SERVICE
Try this:
index= mmartin st=m01 host=defr4456
| stats max(_time) AS _time range(_time) AS duration BY SERVICE ACCNO
| timechart span=1d avg(duration) AS dailyAvgDuration BY SERVICE
| untable _time service dailyAvgDuration
| eval dailyAvgDuration=tostring(dailyAvgDuration, "duration")
| xyseries _time service dailyAvgDuration