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.
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.
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"
If you want fixed output, take a look at the table command:
As for filling in values that are null, take a look at the fillnull command:
putting those together for a command such as:
<your search> | table field1 field2 field3 UserID | fillnull value="0" UserID