Tips on using sort and count: It is possible to generate a: "* |stats values(y) count by x | sort -x" but where each value of y is count separately and also sorted?

[agarza]

I'm trying to generate a table where the output is something like this:

ValueY       ValueX 　　Count
ValueY1      ValueXa　　10000
             ValueXb　　1000
             ValueXc　　100
             ValueXz　　10
ValueY2      ValueXe　　5000
             ValueXf　　500
             ValueXg　　50
             ValueXz　　5
ValueY3      ValueXa　　2500
             ValueXg　　250
             ValueXc　　25
             ValueXz　　2

But right now I'm getting something like this:

ValueY　　ValueX 　　Count
ValueY1 　ValueXa　　11110
　　　　　ValueXb
　　　　　ValueXc
　　　　　ValueXd
ValueY2 　ValueXa　　5555
　　　　　ValueXb
　　　　　ValueXc
　　　　　ValueXd
ValueY3 　ValueXa　　2222
　　　　　ValueXb
　　　　　ValueXc
　　　　　ValueXd

Is there any way to sort the valueX by the amount of counts and display the count of every value of X?

[DalJeanis]

Your existing search , to match your data, must be

 your search 
|stats values(x) as x count as Count by y 

To have each X values separate, do this

your search
| stats count as Count by y x
| sort 0 y - Count
| stats  list(x) as x list(Count) as Count by y 

If you want the ys listed in descending count order, you will need an additional step...

your search
| stats count as Count by y x
| eventstats sum(Count) as yCount by y 
| sort 0 - yCount - Count
| stats  list(x) as x list(Count) as Count first(yCount) as yCount by y 

[agarza]

@DalJeanis

How can I restrict the amount of rows X values?
This because I have many Values, but I only want to display a fixed number of rows, and want to ensure this variable have a value.

[cmerriman]

how about something like this?

...| eventstats count as ycount by y|eval x_ycount=x+" - "+ycount|stats values(y) by x_ycount|rex field=x_ycount "(?<x>\w+)\s-\s(?<count>\d+)"| sort -x

The eventstats should count everything by y, and then create a new field concatenating x and the y_count fields. execute the stats command with the new concatenated field and then split it apart.

[agarza]

@cmerriman,

I'm sry, but I try your algorithm but without any luck, I see you stats the 1st count and save it in a new variable, but I don't think this approximation could help me, because I am already making a transaction function at the beginning of my algorithm, making this a really exhaustive process.

*
| transaction sessionid maxspan=60s maxpause=60s
| stats values(srcuser) as Usuario count as Visitas by url
| eval Usuario=mvindex(Usuario,0,5)
| sort -Visitas limit=30