topic Re: how can I re-order my table with the join command? in Splunk Search

how can I re-order my table with the join command?

chadman — Mon, 28 Sep 2020 19:31:46 GMT

I have a search using the join command and it works well, but I'm not sure how to re-order my table. My search is
sourcetype=ejsysinfo_sort host="ws1"| head 1 | rename HD as "Total Disk GB" |table host,"Total Disk GB",Model |join [ search sourcetype=ejlog_sort host="ws1"| head 1 | rename Available_D as "Available Disk GB"| table "Available Disk GB"]
So I have a table that shows host,"Total Disk GB",Model,"Available Disk GB". How can I switch the table order to show host,"Total Disk GB","Available Disk GB",Model?

Re: how can I re-order my table with the join command?

martin_mueller — Tue, 14 Apr 2015 12:58:53 GMT

You can reorder a table with the table command by listing the columns in the order you want.

Re: how can I re-order my table with the join command?

NOUMSSI — Tue, 14 Apr 2015 13:01:33 GMT

Hi,
Try this:

sourcetype=ejsysinfo_sort host="ws1"| head 1 | rename HD as "Total Disk GB" |table host,"Total Disk GB",Model |join [ search sourcetype=ejlog_sort host="ws1"| head 1 | rename Available_D as "Available Disk GB"| table "Available Disk GB"] |table host,"Total Disk GB","Available Disk GB", Model

I just add this code at the end of your query:

...|table host,"Total Disk GB","Available Disk GB", Model

Re: how can I re-order my table with the join command?

ngatchasandra — Mon, 28 Sep 2020 19:28:19 GMT

Hi chadman,

Try with this:

Re: how can I re-order my table with the join command?

chadman — Tue, 14 Apr 2015 13:09:08 GMT

I'm using the table command in my seach, but not sure how to make it work with my subsearch with join. It displays the data I want in the table, but in the wrong order. In my example I could only get this to work by using the table command twice in my search.

Re: how can I re-order my table with the join command?

chadman — Tue, 14 Apr 2015 13:15:09 GMT

Thanks that worked!

Re: how can I re-order my table with the join command?

chadman — Tue, 14 Apr 2015 13:15:35 GMT

Thanks, that also worked:-)

Re: how can I re-order my table with the join command?

ngatchasandra — Tue, 14 Apr 2015 13:16:28 GMT

The table command oder automatically the tables in order which you specify! Try with this

sourcetype=ejsysinfo_sort host="ws1"| head 1 | rename HD as "Total Disk GB" |join [ search sourcetype=ejlog_sort host="ws1"| head 1 | rename Available_D as "Available Disk GB"] |table host,"Total Disk GB","Available Disk GB",Model

Re: how can I re-order my table with the join command?

sideview — Tue, 14 Apr 2015 15:47:53 GMT

Note: unless one or both sourcetypes is very sparsely occurring in time, it's probably faster to use this search instead.

host="ws1" ( sourcetype=ejsysinfo_sort OR sourcetype=ejlog_sort ) | head 1000 | stats first(HD) as "Total Disk GB" last(Available_D) as "Available Disk GB" by host Model | table host "Total Disk GB" "Available Disk GB" Model

It may feel like the join version is faster because it only gets 2 events off disk, but in reality splunk is probably getting quite a lot off disk for a split second and then truncating each search to 1 row. And the join version runs two searches so you get twice the search-dispatch overhead.

Re: how can I re-order my table with the join command?

chadman — Tue, 14 Apr 2015 17:12:29 GMT

Sideview, I tried your search and it seems to work ok, but looks like it's looking at 28000 events instead of 2. It also takes a little longer. So I have one sourcetype that is rarely updated and another that gets updates every min and in this case is about 28000 events. So I was thinking that the "head" command would help speed that up by only grabing the most recent event in the search. I'm still new to all this and trying to get the best searches created for my users.

Re: how can I re-order my table with the join command?

martin_mueller — Tue, 14 Apr 2015 17:22:31 GMT

If one sourcetype is rarely updated you may want to consider moving that to a lookup instead of (on top of) indexing it.