Splunk Search

Chart by X but sort by Y

emmby
Engager

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?

Tags (2)
1 Solution

aweitzman
Motivator

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"

View solution in original post

aweitzman
Motivator

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.

0 Karma

aweitzman
Motivator

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
Engager

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?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...