Chart by X but sort by Y

[emmby]

I have a chart that shows the count of users of my app by the version of the app that they're using. It works great.

The only problem is that my version numbers are not alphabetically sortable (2.10.1234 comes after 2.2.2345 chronologically but not alphabetically), so they are not displayed in the proper order along the X axis.

I have a field, called buildnum, that has the correct ordering of the versions. If I display count of users by buildnum, the graph is in the correct order. However, I do not want buildnum in the X axis -- the viewers of my graph have no idea what buildnum is, they want to see the app version.

How can I sort the X axis of a chart by another field which is not displayed in the chart?

[aweitzman]

Then you'll definitely need a lookup. Then you can do something like this:

... | chart count(user) by buildnum, eventType | lookup buildnum_to_clientversion buildnum OUTPUT clientVersion | fields - buildnum | table clientVersion, *

The chart command should order things by buildnum. The lookup will go into the CSV file you've created that maps buildnum to clientVersion, pull out the clientVersion, and add it to the result. The fields command removes the buildnum field from the result, and finally, the table command reorders the fields so that the clientVersion comes first. Now your chart should show properly.

Read about lookups in the Splunk documentation. There are a couple of ways you can do them, so you need to pick the most appropriate way to do it for your environment.

[aweitzman · Accepted solution]

You should be able to just include the version number in the initial result, and then throw the build number away when it comes time to create your chart:

... | stats count(user) as "User Count" by buildnum, versionnum | sort buildnum | fields versionnum "User Count"

Alternatively, you can create a CSV lookup file to convert a buildnum to a versionnum and reference it that way:

... | stats count(user) as "User Count" by buildnum | sort buildnum | lookup buildnum_to_versionnum.csv buildnum OUTPUT versionnum | fields versionnum "User Count"

[emmby]

There's one more piece of information that I didn't realize was significant. My chart command currently looks like "chart count(user) by clientVersion, eventType" because I have a few different event types that I'm looking at. It seems that I can't add "buildnum" to the "by" field in stats because there are two fields already there. Is there a workaround for that?