Lookup table of key=value limits with a random assignment of key=value fields indicating consumption with different field names

[mjones414]

I have a lookup table that has information such as

resources_available_queue_a=1000
resources_available_queue_b=23
resources_available_queue_abtest=123

and so on, up to about 140 different queues.

then I have thousands of events coming into an index with the following fields:

Event1: resources_assigned_queue_a=883
Event2: resources_assigned_queue_b=20

I'm trying to take the lookup table as a capacity value and use resources assigned as a consumption value, but given the names are completely different and each event will only ever have 1 and I can't know ahead of time which one it will have, I'm struggling with the splunk logic to a join or a lookup to associate the fields in a consumed/capacity type output

End goal is I'd like to have a timechart or even just a stats table showing percentages of resources consumed of the resources assigned for all the queues.

[woodcock · Accepted solution]

Hold on to your britches! Put your "table" into a lookup file and then try this:

<Your Search Here>
| stats [|inputlookup <Your Lookup File Here>
| untable foo name value
| eval function = name
| table function name
| format "" "" ")" "" "" ""
| rex field=search mode=sed "s/  function=/max(/g s/name=/AS curr_/g s/\"//g s/ \)/)/g"]
| inputlookup append=t <Your Lookup File Here>
| eval foo="bar"
| selfjoin foo
| fields - foo
| foreach curr_* [ eval pct_<<MATCHSTR>> = 100 * curr_<<MATCHSTR>> / <<MATCSTR>> ]

You might swap max for avg or first.

[woodcock]

@mjones414 Come back and click Accept. You have several answers.

[mjones414]

Thank you so much! This did get me the results I needed. Sorry for the very late acceptance!

[woodcock]

My answer assumes that your lookup file looks like this:

resources_available_queue_a,resources_available_queue_b,resources_available_queue_abtest,...
1000,23,123,...

If it does not look like that you need to reformat it so that it does, then it will work.

[richgalloway]

Can you change the lookup table into comma-separated values? That's the format Splunk expects.

Assuming you can do that, you then need to match up the resources names. That's not so bad.

index=foo 
| foreach resources_assigned_* [ eval resource=resource_available_<<MATCHSTR>> 
  | lookup resources.csv resource output capacity | eval resourceUse = (<<FIELD>> * 100) / capacity ] 
| table resource capacity resourceUse

[mjones414]

I think you've put me on the right track, but I haven't quite got it working. For whatever reason it's saying it cannot match against my lookup table from inside of the subsearch.

foo  | foreach resource_assigned_*  [ eval JobGroup ="resources_available_<<MATCHSTR>>"  | lookup resource_groups.csv resources_available_<<MATCHSTR>> as JobGroup OUTPUT "resources_available_<<MATCHSTR>>" AS Capacity ] 

I can verify that there is an exact match of the resource group name in the lookup table and that the JobGroup field is being created with a string that matches whats inside the lookup table. Its as if lookup is taking MATCHSTR literally instead of using it as a token for foreach.

[mjones414]

I have also tried:

    foreach resources_assigned_*  [ eval resources_available_<<MATCHSTR>> = <<FIELD>> | lookup resource_groups.csv resources_available_<<MATCHSTR>> OUTPUT resources_available_<<MATCHSTR>> AS Capacity   ] 

The lookup table's structure is like this:

resources_available_Queue_A,resources_available_Queue_B
29,100

Unfortunately not like:
Resource_Limits,Value
resources_available_Queue_A,29
resources_available_Queue_B,100

[richgalloway]

Your lookup table is not in the proper format, which is why Splunk is not able to find what it's looking for. See @woodcock's answer for a workaround or re-format resource_groups.csv.