topic Re: Add a column with count stats in addition to an existing column with avarage stats in Splunk Search

Add a column with count stats in addition to an existing column with avarage stats

piefragnisp — Mon, 16 Mar 2020 12:06:31 GMT

I have a json file with some information regarding soa requests. Basically info such as callee, caller, start and end timestamp of a request (write me if you want more details).

By the way I have a splunk-query to group all the events by the callee and calculate the average duration of the difference between end and start timestamp.

Something like this:

| tstats values where index=my_index by callee, timestampStart, timestampEnd  | eval duration= round((strptime(timestampEnd  , "%Y-%m-%dT%H:%M:%S.%6N%Z") - strptime(timestampStart, "%Y-%m-%dT%H:%M:%S.%6N%Z")),2)| stats avg(duration) as duration by callee| eval duration=round(duration,2) |table callee, duration

In addition to the avarage duration I would also add a column with the count of all events regarding that callee but (if I understand well) I can do this only with a Tstats count.

Any ideas?

Thank a lot

Re: Add a column with count stats in addition to an existing column with avarage stats

to4kawa — Mon, 16 Mar 2020 12:30:08 GMT

tstats is fast.

....
| append [| tstats count where index=my_index by callee]
| stats values(duration) as duration by callee

how about this?

Re: Add a column with count stats in addition to an existing column with avarage stats

piefragnisp — Mon, 16 Mar 2020 13:38:44 GMT

@to4kawa just tried but seems not wotking

| tstats values where index=my_index by callee, timestampStart, timestampEnd  | eval duration = (strptime(timestampEnd, "%Y-%m-%dT%H:%M:%S.%6N%Z") - strptime(timestampStart, "%Y-%m-%dT%H:%M:%S.%6N%Z")) | stats avg(duration) as duration by callee | eval duration=round(duration,2)  | append [| tstats count where index=my_index by callee]

In addition I've tried this

| tstats values where index=my_index by callee,caller,  timestampStart, timestampEnd | eval duration = round((strptime(termine, "%Y-%m-%dT%H:%M:%S.%6N%Z") - strptime(inizio, "%Y-%m-%dT%H:%M:%S.%6N%Z")),2)| stats avg(duration) as duration , count(callee) as total by callee | eval duration=round(duration,2) | table callee, caller, duration, total

and seems working

Re: Add a column with count stats in addition to an existing column with avarage stats

manjunathmeti — Mon, 16 Mar 2020 15:55:17 GMT

Hi @piefragnisp,

Try this:

| tstats values where index=my_index by callee, timestampStart, timestampEnd 
| eval duration= round((strptime(timestampEnd , "%Y-%m-%dT%H:%M:%S.%6N%Z") - strptime(timestampStart, "%Y-%m-%dT%H:%M:%S.%6N%Z")),2) 
| stats avg(duration) as duration by callee 
| eval duration=round(duration,2) 
| append 
    [| tstats count where index=my_index by callee] 
| stats values(*) by * by callee 
| table callee, duration, count

Re: Add a column with count stats in addition to an existing column with avarage stats

to4kawa — Mon, 16 Mar 2020 20:38:22 GMT

good job, accept your answer.