Splunk Search

How can I get a fixed number of columns returned in my search?

Stu_Art
New Member

Hi,

Hope someone can point me in the right direction.

I have a search that pulls a count by 'UserID' of the number of times that they have accessed available Menus in a month. UserIDs (~30k)are in rows and Menu names are in the Columns (~40)

This is great apart from, not all Users access all the Menus each month, so the results vary in the number of Columns returned. (so currently Appending in an Access Database)

Is there a way to always return 'all' Columns and insert a '0' for each UserID?

Many thanks for any help.

Stu

Tags (1)
0 Karma

gkanapathy
Splunk Employee
Splunk Employee

This is tricky. Splunk generally won't make columns if it's not in the data, since it would have no way of knowing what those column values are. But you know what those are, so you should first make a lookup table containing those, e.g.

menus.csv:

menutitle
"file"
"edit"
"view"
"menu4"
...

Now, I assume you're using a search like:

sourcetype=mysourcetype | ... | chart count by userid,menutitle

You'd have to change to something like:

sourcetype=mysourcetype | ...
| append [ inputlookup menu.csv | eval userid="dummy" ]
| chart count by userid,menutitle
| where userid!="dummy"
0 Karma

gkanapathy
Splunk Employee
Splunk Employee

This is really just a trick. We just add a dummy user who accesses every menu, get the normal counts, then just remove that user.

0 Karma

sideview
SplunkTrust
SplunkTrust

What format is the indexed data in? It sounds like maybe it's a CSV format? with a header row like "UserID,menu1,menu2,menu3,..." ?

0 Karma

sbrant_splunk
Splunk Employee
Splunk Employee

If you want fixed output, take a look at the table command:

http://docs.splunk.com/Documentation/Splunk/6.0.2/SearchReference/Table

As for filling in values that are null, take a look at the fillnull command:

http://docs.splunk.com/Documentation/Splunk/6.0.2/SearchReference/Fillnull

putting those together for a command such as:

<your search> | table field1 field2 field3 UserID | fillnull value="0" UserID
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...