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

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

rajyah — Wed, 07 Nov 2018 03:30:36 GMT

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:

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!

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

renjith_nair — Wed, 07 Nov 2018 03:47:59 GMT

@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

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

HiroshiSatoh — Tue, 29 Sep 2020 21:54:27 GMT

Here the field is only sports_category ,department,sum(TOTAL_BUDGET) .

index=monthly_budget
| chart sum(TOTAL_BUDGET) over sports_category by department limit=0

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

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

rajyah — Wed, 07 Nov 2018 05:07:43 GMT

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.

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

rajyah — Wed, 07 Nov 2018 05:15:33 GMT

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

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

HiroshiSatoh — Wed, 07 Nov 2018 05:22:38 GMT

Is Coach's field in monthly_budget?

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

rajyah — Wed, 07 Nov 2018 05:42:19 GMT

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.

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

rajyah — Wed, 07 Nov 2018 05:51:02 GMT

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

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

renjith_nair — Wed, 07 Nov 2018 05:53:33 GMT

@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

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

rajyah — Wed, 07 Nov 2018 06:04:33 GMT

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

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

mstjohn_splunk — Wed, 07 Nov 2018 21:52:21 GMT

hi @rajyah

I'm glad you found a solution to your problem. Would you add more detail as to how you came to fix your table? Other users would love to hear your detailed explanation! Also, can you please approve this answer so others know where to look for the correct solution?

Thanks!

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

rajyah — Thu, 08 Nov 2018 01:40:32 GMT

I used:

   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"
     | appendcols [ search index=monthly_budget
     | stats values(Coach) as "Coach"  by department
     | fields - department]
     | fillnull value="-"

I know this isn't the best way to do this but it works for me. hehe It'll be really cool if there's another good way to do this. Thank you!