How to get a range of number?

[jialiu907]

I am looking for a range of number within my results of my search query but I am getting no results back after adding in a where clause. 

This is my original search query. 

index=os sourcetype=ps (tag=dcv-na-himem)
NOT tag::USER="LNX_SYSTEM_USER" 
| timechart span=1m eval((sum(RSZ_KB)/1024/1024)) as Mem_Used_GB by USER useother=no
| sort Mem_Used_GB desc
| head 20

This is some of the results.
 

This is the new search where I am looking for a range of data between 128 and 256 and I am getting no results back, even with events matched. I have also played with time line and range of the where clause and still nothing.

index=os sourcetype=ps (tag=dcv-na-himem)
NOT tag::USER="LNX_SYSTEM_USER" 
| timechart span=1m eval((sum(RSZ_KB)/1024/1024)) as Mem_Used_GB by USER useother=no
| where Mem_Used_GB >= 128 AND Mem_Used_GB <= 256
| sort Mem_Used_GB desc
| head 20

[ITWhisperer]

You could add an untable

index=os sourcetype=ps (tag=dcv-na-himem)
NOT tag::USER="LNX_SYSTEM_USER" 
| timechart span=1m eval((sum(RSZ_KB)/1024/1024)) as Mem_Used_GB by USER useother=false
| untable _time USER Mem_Used_GB
| where Mem_Used_GB >= 128 AND Mem_Used_GB <= 256
| sort Mem_Used_GB desc
| head 20

[livehybrid]

Hi @jialiu907 

After you have applied your timechart you no longer have "Mem_Used_GB" as each field/column is the name of the USER containing the value. If you used "stats" instead of you "timechart" you would get the "Mem_Used_GB" column however you wont get the _time element in.

It isnt clear what your usecase is but if you use bin and stats you might get the desired outcome?

index=os sourcetype=ps (tag=dcv-na-himem)
NOT tag::USER="LNX_SYSTEM_USER" 
| bin span=1m _time
| stats sum(eval(RSZ_KB/1024/1024)) as Mem_Used_GB by _time, USER
| where Mem_Used_GB >= 128 AND Mem_Used_GB <= 256
| sort Mem_Used_GB desc
| head 20

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[jialiu907]

I am getting an error with the stats command after trying your query. 

Error in 'stats' command: The argument 'eval((sum(RSZ_KB)/1024/1024))' is invalid.

[livehybrid]

Hi @jialiu907 

Sorry, I took your original timechart field eval which I believe may be incorrect. I do not have test data to check this but please can you try the following in place of the previous stats block?

| stats sum(eval(RSZ_KB/1024/1024)) as Mem_Used_GB by _time, USER

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[jialiu907]

I am getting results back but it is a bit different than the desired results. 

The original search query results is separated by USER and that is what I am looking for.

This is the result I am getting after running your search query. It is only including the top 20 Mem_Used_GB and not seperated by USER. Is there a way to separate it by USER also?

[ITWhisperer]

Please clarify your requirement. Do you want the top 20 memory over the whole period used and which user that was and on which day? Or, do you want the top 20 users who used the most memory each day? Or, do you want the top 20 users who used the most memory over all and how much they used each day? Or something else?

[jialiu907]

I am looking for the top 20 memory usage by individual users over a period of time that is the middle part of the range, for example between 128 and 256 GB.

I have already figured the first part of what I am looking for in the original search query, which is the top 20 memory usage by individual users over a period of time. I am now looking to see if its possible to see that number but over a certain range instead.

[ITWhisperer]

Is this what you mean?

index=os sourcetype=ps (tag=dcv-na-himem)
NOT tag::USER="LNX_SYSTEM_USER" 
``` Calculate memory used by each user each minute ```
| timechart span=1m eval((sum(RSZ_KB)/1024/1024)) as Mem_Used_GB by USER useother=false
``` Convert to a table ```
| untable _time USER Mem_Used_GB
``` Find memory usage in range ```
| where Mem_Used_GB >= 128 AND Mem_Used_GB <= 256
``` Find top 20 ```
| sort 20 Mem_Used_GB desc
``` Convert back to chart format ```
| xyseries _time user Mem_Used_GB

[jialiu907]

I am not getting any results back with this search.

[ITWhisperer]

Sorry - try with USER (instead of user)

index=os sourcetype=ps (tag=dcv-na-himem)
NOT tag::USER="LNX_SYSTEM_USER" 
``` Calculate memory used by each user each minute ```
| timechart span=1m eval((sum(RSZ_KB)/1024/1024)) as Mem_Used_GB by USER useother=false
``` Convert to a table ```
| untable _time USER Mem_Used_GB
``` Find memory usage in range ```
| where Mem_Used_GB >= 128 AND Mem_Used_GB <= 256
``` Find top 20 ```
| sort 20 Mem_Used_GB desc
``` Convert back to chart format ```
| xyseries _time USER Mem_Used_GB

[jialiu907]

This is the result but it is still not what I am looking for.

I have been trying some stuff on my end as well and I got an result that is close to what I am looking for but not. 

index=os sourcetype=ps (tag=dcv-na-himem)
NOT tag::USER="LNX_SYSTEM_USER" 
| timechart span=1m eval((sum(RSZ_KB)/1024/1024)) as Mem_Used_GB by USER useother=no WHERE max in top20
| sort USER desc
| head 20

This is the result.

It is displaying the results in the way I am looking for, just not the right results. I am looking for the middle 20 instead of the top 20 or bottom 20. Is there an way or command to just display the middle 20 using the search query above?

[ITWhisperer]

Essentially, you need to do the hard work! First untable the stats from the timechart results, find each user's maximum, sort the results by maximum and user, then count the users and find the "middle 20", then convert back to chart format.

index=os sourcetype=ps (tag=dcv-na-himem)
NOT tag::USER="LNX_SYSTEM_USER" 
| timechart span=1m sum(eval(RSZ_KB/1024/1024)) as Mem_Used_GB by USER useother=false limit=0
| untable _time USER Mem_Used_GB
| eventstats max(Mem_Used_GB) as max by USER
| sort 0 max USER desc
| streamstats dc(USER) as user_number
| eventstats dc(USER) as total
| where user_number > (total - 20)/2 and user_number < 20+((total - 20)/2)
| xyseries _time USER Mem_Used_GB