How to sort values from append search?

[Lavender]

Hi,

Kindly help on sorting the values from append query as below:

index=* source=*
|stats sum(Tot) sum(in_prog) sum(submitted) sum(without_info) by Month
|append [search index=* source=* 
chart count by Month,with_info, |addtotals with_info]

|append [search index=* source=* 
chart count by Month,without_info, ]

|untable Month,Category,Total
|eval sort_field=case(Category="Tot",1,Category="in_prog",2,Category="Submitted",3,Category="without_info",4, Category="with_info",5)
|sort sortfield

|stats list(Category) as Category list(Total as Total by Month

Result:

Month            Category                     Total

Feb-23            Tot                                  1000

                           in_prog                           400

                           Submitted                      600

                            without_info                100

                             with info                          500

                             Occupation                   xxx

                             Business                          xxx

                              Null                                   xxx

                               Others                             xxx

From the above table, Occupation, Business are reasons derived from first append query and Null, Others are reasons derived from second append query and getting the append query results in the last.

So, requirement is to display the without_info reasons next to that field and with_info reasons next to that field like below. Kindly help on sorting.

Month            Category                     Total

Feb-23            Tot                                  1000

                           in_prog                           400

                           Submitted                      600

                            without_info                100

                             Null                                  xxx

                             Others                            xxx

                             with info                          500

                             Occupation                   xxx

                             Business                          xxx

[Lavender]

Hi ,

Thanks for your reply, but Category 7,8,9 is not a static one, it may vary depends on query. Also, requirement is to display the without_myinfo reasons (Null,Others) below to that and with _myinfo reasons (Occupation,business) below to that.Here it is diaplaying all reasons in the last.

[VatsalJagani]

@Lavender - Try something like this:

index=* source=*
| stats sum(Tot) sum(in_prog) sum(submitted) sum(without_info) by Month | eval query_no="query-1"
| append [search index=* source=*
chart count by Month,with_info, | addtotals with_info | eval query_no="query-2"]
| append [search index=* source=*
chart count by Month,without_info | eval query_no="query-3" ]
| untable Month, Category, query_no, Total
| sort Month, query_no

I think this should work!!!

[VatsalJagani]

@Lavender - I have not validated your existing query but I have included new stuff that you can use to achive what you need.

index=* source=*
| stats sum(Tot) sum(in_prog) sum(submitted) sum(without_info) by Month | eval query_no="query-1"
| append [search index=* source=*
chart count by Month,with_info, | addtotals with_info | eval query_no="query-2"]
| append [search index=* source=*
chart count by Month,without_info | eval query_no="query-3" ]
| untable Month, Category, query_no, Total
| eval sort_field=case(
Category="Tot", 1,
Category="in_prog", 2,
Category="Submitted", 3,
Category="without_info", 4,
Category="with_info", 5,
Category="Occupation", 6,
Category="Business", 7,
Category="Null", 8,
Category="Others", 9,
10)
| sort Month, query_no, sort_field

You may have to perform slight modification with this query to make it work. But you should get the idea on how you can do it. 

I hope this helps!!!