Can I make sichart behave like chart?

[rharrigan]

So I have a working query that uses chart to visualize some data by some categories. Example:

index=myData | chart count by site pagetype

Which gives me a table with:

Site   site_type1   site_type2 
site1        55            505
site2        505           55

So I can visualize data with something like a stacked bar chart showing how each site type contributes to each site. Since we want to look at this over months though it can get quite slow to count that many events so I set up a summary index to run hourly to improve performance. It includes a split by site and pagetype so all of the information is preserved into hourly counts,

index=myData | sistats count by site pagetype other_thing1 other_thing2

This runs hourly to speed up report generation and has worked well for some reports. However when I try to replicate the above report with something like

index=summary_myData | sichart count by site pagetype

The table is different and looks like:

Site     site_type     psrsvd_gc
site1    site_type1      55
site2    site_type1      505
site1    site_type2      505
site2    site_type1      55

The data is clearly all there but this is a terrible way to look at it and the fact that the columns are not formatted correctly means that any visualizations I create are not useful. For example, if I try to create a bar chart I don't get separate bars for each site type.

How can I reconcile the two to look the same so that this speedup is invisible to my report users? Is it even possible?

I have tried:

 sichart count by page over page_type

Which doesn't change anything

sichart count(eval(page_type=="page_type1")) as page_type1 count(eval(page_type=="page_type2")) as page_type2 by site

This also just adds together all of the counts and ignores the as clauses.

[DalJeanis · Accepted solution]

Once you have taken the data out to play, you don't have to stick to the si commands.

This puts the sample data back in as you showed it...

| makeresults
| eval mydata="site1,site_type1,55 site2,site_type1,505 site1,site_type2,505 site2,site_type2,55"
| makemv mydata | mvexpand mydata | table mydata 
| makemv delim="," mydata 
| eval Site=mvindex(mydata,0), site_type =mvindex(mydata,1), psrsvd_gc=mvindex(mydata,2)
| table Site site_type psrsvd_gc

This reformats it back to the way you want it...

| rename psrsvd_gc as count
| chart sum(count) as count by Site site_type

---

By the way, that output format looks like a great example of the results of the untable command, so exploring how the untable and xyseries commands work could help you effectively manipulate the summary data if it gets any more complicated than the stuff you showed us here.

[rharrigan]

Thanks for the help. It turns out xyseries is what I needed to pull apart the fields so I'll vote this as closed. For anyone interested the results for me was something like | sichart count by site site_type | table site site_type psrsvd_gc | xyseries site site_type psrsvd_gc which yielded the correct table formatting as described above.

[DalJeanis]

Yes, that data sample you posted just screamed "I was created by untable"... and untable's inverse is xyseries.

...Or, in this simple case, chart works too.