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

[Stu_Art]

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

[gkanapathy]

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"

[gkanapathy]

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.

[sideview]

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,..." ?

[sbrant_splunk]

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