Splunk Search

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

rajyah
Communicator
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
1 Solution

rajyah
Communicator

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

View solution in original post

0 Karma

rajyah
Communicator

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!

0 Karma

rajyah
Communicator

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

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

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!

0 Karma

HiroshiSatoh
Champion

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

0 Karma

rajyah
Communicator

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

HiroshiSatoh
Champion

Is Coach's field in monthly_budget?

0 Karma

rajyah
Communicator

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

renjith_nair
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
Happy Splunking!
0 Karma

rajyah
Communicator

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

renjith_nair
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

Happy Splunking!
0 Karma

rajyah
Communicator

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...