Using Lookup to determine Field Value

[dlm]

I have a unique situation with my customer. I want to create a lookup table that the customer can put  fields they want the value for.

lookup has column called fieldvalue . ie. CPU in the list. 

if that field is cpu is in the table for instance, then we have to run a calculation with the Cpu field. for all the events who have cpu. 

fields customer selects are number fields. The things i have tried are not returning the value in the cpu field. 

Without discussing customer stuff, using calculated fields won't work, KPI stuff won't work. For what they want, I need to do it this way.

[livehybrid]

Hi @dlm 

Im not entirely sure what it is you're trying to achieve so this might not be the best way to achieve it, but hoepfully one of the below examples might help! 

If you can give us more details (ideally with examples) then we might be able to give a better specific answer 🙂 

I started by creating a lookup:

The examples work around using a subsearch to get the list from the lookup

Option 1:

This adds a prefix of my_ to the fields listed in the lookup

| makeresults 
| eval CPU=45, Memory=12.3, Disk=84.4, Network=92 
| rename [| inputlookup fields.csv 
| eval fieldName=fieldName+" AS my_"+fieldName
    | stats list(fieldName) as search ]

Option 2:

This uses "table" to only list the fields in the lookup, with an optional field showing the fields (example of foreach)

| makeresults 
| eval CPU=45, Memory=12.3, Disk=84.4, Network=92 
| table 
    [| inputlookup fields.csv 
    | stats list(fieldName) as search] 
| foreach * 
    [| eval fields=mvappend(fields,"<<FIELD>>")]

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[dlm]

Sorry it took so long to get back.  The second Option is starting to get where I need to be. I appreciate the code. How do I keep the host from the original log and have the second column in that has the value I want to compare the columns too. 

I am using ITSI but I Originally I thought if I were looking at the event in this custom log using things we all know.

LOG:

host                    CPU     MeM          UsePct       Swapused 

Apple1               5            3                  2                      7

Apple2               4             1                12                     9

Apple3               1              2                4                      8      

Lookup

host     fieldName     Comparefield

*              CPU                   7

*             MEM                   4

*             Swapused        2

Code

I thought I could do the foreach line in the log

If (<field> Log<=<fieldName>Lookup, "OK", <fieldName>"Error")

[ITWhisperer]

| makeresults
| fields - _time
| eval _raw="host,CPU,MeM,UsePct,Swapused
Apple1,5,3,2,7
Apple2,4,1,12,9
Apple3,1,2,4,8"
| multikv forceheader=1
| table host,CPU,MeM,UsePct,Swapused
| lookup hostmetrics.csv host
| foreach *
    [| eval fieldvalue=if(fieldname="<<FIELD>>",<<FIELD>>,fieldvalue)]
| eval metric=if(fieldvalue < value,"OK","Error")

I set up hostmetrics.csv like this

| makeresults format=csv data="host,fieldname,value
Apple1,CPU,4
Apple3,MeM,2
Apple2,UsePct,8"
| outputlookup hostmetrics.csv

[dlm]

Love the code but  it seemed to only do one value in the lookup. What if that event (comparing host in table to event) has 2 fields that don't have null values that need compared to the 2 in the lookup table.  Like in your example they all had the same columns, 3 fields were in the table and the event had 4 different fields.

But I have something to start playing with. I will continue to play with this while onboarding other stuff. Look forward to hearing from you again.

[dlm]

I forgot to say when I was doing the spl, I did the mvexpand on the field column so I can just look at each field individually for that line in the log. Then I can alert only on something that is bad. But having the host and the value to compare was where I have issues.

[richgalloway]

It sounds like you've settled on what might be a unsuitable solution to the problem.  Tell us more about the problem itself and we may be able to suggest a better solution.

Lookup tables are for enriching events with additional fields based one or more fields already in the events.  It's not a conditional-execution mechanism.

If this part of a dashboard (or can be made into a dashboard) then you have better options.  You can have inputs the user can select to determine which calculations are made.  That is well-trodden ground so let us know if that path sounds feasible.

[dlm]

At this location, We handle setting up ITSI and not SA for teams for monitoring. They work with us as we need them. I am in ITSI creating alerts with correlation searches our correlation searches have about 20 lines of required fields that show in the alerts after the calculations. All I need to know is determine if the fields for the event meets or exceeds that percent criteria, if it does it'll generate a of low or high based on what they put in the lookup for the severity.  I could do a case statement in the code but I am trying not to hard code. If I put it into the lookup, if the customer changes their mind on the percents later or they want it to be a low alert instead of critical, they can modify the table without the code being touched.  If you do a custom KPI, I haven't been able to allow the required fields that have to be in the alert for the monitoring group.

Here, once the code for that index goes live then it is considered production. Which means, one small change of code requires going thru the testing process between us, the team and the monitoring group who watches the alerts.  It's a whole ordeal.  

SO, If I can create a table where the team can say a field and the percent  then it is easier.  Each event in the log the customer is creating has multiple fields to check. The only thing I care about is the host, the field value and the severity. 

I am trying to avoid hard coding. If I can't come up with a way to use the lookups, I will do it.  I know that this is NOT what people normally do, but sometimes you have to think outside the box to make life easier. Teams don't know what they want and constantly change their minds. When we are working to onboard new indexes in the building for infrastructure and applications...our team of 4 doesn't have time to do a lot of changes when someone changes their minds.