topic query help...!!!!!! in Splunk Search

query help...!!!!!!

martinapple381 — Fri, 01 Jul 2016 02:27:09 GMT

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

Re: query help...!!!!!!

woodcock — Fri, 01 Jul 2016 15:40:29 GMT

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

Re: query help...!!!!!!

DalJeanis — Thu, 09 Feb 2017 20:09:39 GMT

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

Re: query help...!!!!!!

DalJeanis — Thu, 09 Feb 2017 20:14:02 GMT

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