Help on chart from subsearch- How to display a bar chart with the site field in x axis

[jip31]

hello

I need to display a bar chart with the site field in x axis

For each site, I need to display 2 bar

The first bar is the avg of retrans_bytes per site and the second bar is the avg of retrans_bytes per user (it means the user corresponding to the site)

Thats' why I use a subsearch for doing this

But I dont succedd to cross the results between the 2 search

could you help please?

`index` sourcetype="netp_tcp""  
| chart avg(retrans_bytes) as retrans_bytes by site user
| append 
    [| search `index` sourcetype="netp_tcp""  
| chart avg(retrans_bytes) as retrans_bytes by site ]

[PickleRick · Accepted solution]

Ouch. You already have the data, don't search for it twice!

One obvious approach would be to use eventstats instead of stats so you wouldn't lose the origina, events. But that would mean running the eventstats twice and since eventstats is a dataset processing command, it doesn't scale well.

<your search>
| eventstats avg(retrans_bytes) as avgretr_per_site_user by site user
| eventstats avg(retrans_bytes) as avgretr_per_site by site

Then you can pipe it to stats values() or something.

But that's - I think - kinda ineffective.

I'd try to approach it from a bit more "manual" side so I'd simply calculate number and sum of entries for each site and user. Then you can use those values to calculate the stats on your own. Something like

<your search>
| stats count(retrans_bytes) as count sum(retrans_bytes) as sum by site user
| streamstats sum(count) as sitecount sum(sum) as sitesum by site
| eval avg=sum/count
| eval siteavg=sitesum/sitecount

I suppose that's more effective that's the eventstats-based since stats is prone to map/reduce.