Splunk Search
Highlighted

Can you help me figure out why one of my table columns contains no values?

Path Finder
index=monthly_budget
| chart sum(TOTAL_BUDGET) over sports_category  by department limit=0
| transpose 0 header_field=sports_category
| addtotals fieldname=TOTAL
| rename column as "Department"
| fillnull value="-"

My example code looks like that. The table would probably look like this:

Department | BASKETBALL | GOLF | SWIMMING | TOTAL
High School | 123,123,456 | 123,123 | 123,123,432 | total of this
Elementary
College
Masters

What I want is to add another column for "Coach" for each department (I've already set up the automatic lookup for this). So the columns would be:

Department, Basketball, Golf, Swimming, TOTAL, Coach.

I tried:
-| fields + Coach
-| table Department, Basketball, Golf, Swimming, TOTAL, Coach // this works but the Coach column has no value.

Please help.

EDIT: The original search is fine. The coach is already in the fields list, I just want to call it as another column. Thank you!

0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

SplunkTrust
SplunkTrust

@rajyah ,

If you have a lookup already for Coach per department, assuming it as entries as

Department,Coach
High School ,CoachA
Elementary,CoachB

Try,

      index=monthly_budget
     | chart sum(TOTAL_BUDGET) over sports_category  by department limit=0
     | transpose 0 header_field=sports_category
     | addtotals fieldname=TOTAL
     | rename column as "Department"
     | fillnull value="-"
     | lookup "coach_lookup_name here" Department OUTPUT Coach
0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

Path Finder

I've already setup the automatic lookup for it sir. I just need to call the "Coach" field created from the lookup but I can't...

Thank you for your response sir.

0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

Path Finder

Tried your response sir but I can't.. The field "Coach" which was created by automatic lookup is sitting idly in the fields list..

0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

SplunkTrust
SplunkTrust

@rajyah , okie, thats because you dont have the fields in your chart and getting filtered out.
So either do the manual lookup after the chart command or include the coach field in the chart command, probably values(Coach) as Coach

0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

Champion

Here the field is only sportscategory ,department,sum(TOTALBUDGET) .

index=monthlybudget
| chart sum(TOTAL
BUDGET) over sports_category by department limit=0

For example, how about doing this?
| stats sum(TOTALBUDGET) by department,sportscategory, Coach

0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

Path Finder

Tried stats command sir but I didn't get the result I'm looking for. Chart command is the closest hint for me.

0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

Champion

Is Coach's field in monthly_budget?

0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

Path Finder

Coach and the Department fields are created by the automatic lookup. And when I check the fields after running my original search, I can see the field in the fields list. The original search is okay but the thing is I can't call the Coach field.

By the way, the department and the coach field are in the lookup table but I've already set it up using automatic lookup.

Thank you for your response sir.

0 Karma
Highlighted

Re: Can you help me figure out why one of my table columns contains no values?

Path Finder

solved it by appendcols! Thank you for your response. 😃

View solution in original post

0 Karma