Performing Calculation on the items of one cell

[zacksoft]

base search | stats values(trans_time) as TransTime by host | transpose

The output looks like this
Host1 Host2
5 3
6 3
2 1
3 4
4 4

The Bold are in One Row , And the Italics are all in another(one) row.
In row-1, I see the Hostnames, In row2's column1 all values(5,6,2,3,4) are present in one cell (not in separate rows) . Same with Host2's values. They are all put in one cell.

The idea is to perform arithmetic operation like adding all the trans_time of Host1 OR calculating the average tans_time of Host2 etc... But the eval/add total commands won't work !!!

[mayurr98 · Accepted solution]

hey

can you try something like this

<base_search>  trans_time=* | stats count as total_event_count avg(trans_time) as average sum(trans_time) as total_sum by host | table host total_sum average

average and sum and total count of events in one query! therefters you can use table command to keep whatever you want

let me know if this helps!

[mayurr98]

Hey what you mean by table cell value..talking about my eval i am later splitting into individual rows they are not in a single row...can you provide input and what output you need?

[zacksoft]

By 'table cell' I meant multiple values were in One row. And makemv as you specified splits it into multiple cell. I guess I understand your point now. Thank you.

[mayurr98]

Yes mvexpand does that..splits multiple values in a field to multiple rows so that you can perform statistical operations
Read the documentstion to know more !

[zacksoft]

Thanks it works. If I don't want to sum or average , just wanna count How many trans_time items are there in Host1 (i.e. in our example we have 5) . Can we do it ?

[mayurr98]

yes you can do that try this use dc(trans_time) to get unique trans time distinct count of trans time items

<base_search>  trans_time=* | stats dc(trans_time) as total_trans_time count as total_event_count avg(trans_time) as average sum(trans_time) as total_sum by host | table host total_sum average total_trans_time

let me know if this helps !

accept/upvote if it is working for you!

[zacksoft]

stats dc(trans_time) .
Does the above command give distinct counts only ? But I have duplicates and I want them to count as well , then how to proceed ? e.g. in Host2 the count is 5, despite having two 3s and two 4s.

[mayurr98]

okay then just use count(trans_time)

try this run anywhere command i have given a set of 1 2 2 3 4 4 4

| makeresults | eval trans_time="1 2 2 3 4 4 4" | makemv trans_time | mvexpand trans_time | stats count(trans_time) as count by trans_time

you get 1 one time 2 twice 3 one 4 threes

[zacksoft]

speaking about eval trans_time="" 1 2 3 4 5" ; In my case I have an entire cell of a table with 20 to 30 values. So manually defining is difficult. Can we not specify it to read from a table cell ?

[mayurr98]

hey @zacksoft pls upvote comments whichever helped you!