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

[chadman]

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?

[ngatchasandra]

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

[ngatchasandra]

Hi chadman,

Try with 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 host,"Total Disk GB","Available Disk GB",Model

[chadman]

Thanks, that also worked:-)

[NOUMSSI · Accepted solution]

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

[martin_mueller]

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

[chadman]

Thanks that worked!

[sideview]

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.

[chadman]

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.

[martin_mueller]

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

[chadman]

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.