Format Command: Field Order

[triest]

Does anyone know a way to control the field order for the format command?

For the default use case of format it AND's columns and OR rows (simplified, but effectively) and the field order doesn't matter, but the format command gives us the ability to manipulate the separators and some of those use cases require controlling the column order.

Imagine you have a list of users and systems they are allowed to log in on that looks like:

User: System
fred: workstation1
tom: workstation2, server1
susan: workstation3, server1, server2

If you have those in a a lookup, it would be nice to write a correlation search like:

| tstats count from datamodel=Authentication where [
  | inputlookup user_systems
  | rename user as Authentication.user system as Authentication.src
  | fields Authentication.user Authentication.src
  | format  "(" " ( " " AND NOT " " ) " " OR " ")"
]
by user src

and have that expand to:

| tstats count from datamodel=Authentication where 
  (user=fred AND NOT (Authentication.src=workstation1))  OR
  (user=tom AND NOT (Authentication.src=workstation2 OR Authentication.src=server1) ) OR
  (user=susan AND NOT (Authentication.src=workstation3 OR Authentication.src=server1 OR Authentication.src=server2))
  by user src

NOTE: my actual correlation search would be slight more complicated as I really only want alerted once per user, but I'd like to preserver the count by system to give the soc analyst better visibility. I'm just simplifying here for the sake of the question.

The problem is this search is currently expanding with src first:

| tstats count from datamodel=Authentication where 
  ((Authentication.src=workstation1) AND NOT user=fred)  OR
  ((Authentication.src=workstation2 OR Authentication.src=server1)  AND NOT user=tom ) OR
  ((Authentication.src=workstation3 OR Authentication.src=server1 OR Authentication.src=server2) AND NOT user=susan)
  by user src

I've tried adding both fields and table to control the order of the fields, but neither appears to have an affect. Is there a way to control the order of the fields to format?

[triest]

Since I hate searching and finding questions that don't have solutions, I thought I would provide the current known workarounds:

Workaround 1: List all users and just prefix sub-search with a NOT

| tstats count from datamodel=Authentication where NOT [
  | inputlookup user_systems
  | rename user as Authentication.user system as Authentication.src
  | fields Authentication.user Authentication.src
]
by user src

Workaround 2: Add a second sub-search to limit results to just monitored users

| tstats count from datamodel=Authentication where 
[
  | inputlooup user_systems
  | fields user
  | rename user as Authentication.user
]
NOT [
  | inputlookup user_systems
  | rename user as Authentication.user system as Authentication.src
  | fields Authentication.user Authentication.src
]
by user src

Workaround 3: Labeling monitored users (if using ES)

This is basically workaround 2, but is a slight optimization if you're using ES and are populating user_category. If you're using ES you may be using the user_systems lookup to add a category to the user (e.g. monitored) and since ES will already be including the identity lookup to add user_category, you can leverage that.

| tstats count from datamodel=Authentication where Authentication.user_category = "Monitored" AND NOT [
  | inputlookup user_systems
  | rename user as Authentication.user system as Authentication.src
  | fields Authentication.user Authentication.src
]
by user src

Workaround 4: Don't use the format command. This is probably what you want, but its not the most readable solution

| tstats count from datamodel=Authentication where [
  | inputlookup user_systems
  | search system=* user=*
  | fields user system
  | eval search="( Authentication.user=" + user + " AND NOT ( Authentication.src=" + mvjoin(system, " OR Authentication.src=") + " )"
  | stats values(search) as search
  | eval search=mvjoin(search, " OR ")
]
by user src

You can make it slightly more readable by using printf:

| tstats count from datamodel=Authentication where [
  | inputlookup user_systems
  | search system=* user=*
  | fields user system
  | eval search=printf("Authentication.user=%s AND NOT ( Authentication.src=%s )", user, mvjoin(system, " OR Authentication.src="))
  | stats values(search) as search
  | eval search=mvjoin(search, " OR ")
]

[dmarling]

If you replace the fields command on line 4 of your initial query to table, that should reorder the fields so they aren't alphabetically ordered, which is what appears to be happening. Let me know if that works.

[triest]

As stated in my initial question: "I've tried adding both fields and table to control the order of the fields, but neither appears to have an affect", so at least in Splunk 6.6 table isn't the solution. I've re-verified that just in case I was wrong about testing.

NOTE: Edited in an attempt to make less harsh.

[dmarling]

Apologies for missing that statement. The answer you gave yourself covers any of the options that I can think of. The documentation does not appear to allow for ordering in the format command so your workaround 4 would be what I would have done.

[triest]

Sorry if that come off harsh; you had no reason to apologize as you were just trying to help (and help at no cost to me). I was actually hoping some one would say it was fixed in a newer version.

I agree the 4 is probably the best although in this case 3 maybe the direction I go since it is a correlation search and it leads to a category in the identity lookup already. I think its easier to read which is important as we have a lot users at varying levels of Splunk expertise and I think there's value in being readable.

Hopefully some one will come up with a better solution; if there's not one today, maybe some time Splunk will say they've done an enhancement.