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!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...