Splunk Search

How to append columns based on searches, and row values with lookups?

Builder

Hi all.

I have a lookup table (data.csv) that looks like:

ID     TYPE       PRICE
1      Type1      3,23
2      Type2      4,2

To check my lookup, I use | inputlookup data.csv. I should add a couple of new columns based on specific searches and operate with simple math on a report, for example:

ID     TYPE       PRICE      COUNTOFEVENTS      TOTAL
1      Type1      3,23       32                 103.36
2      Type2      4,2        2                  16.8

COUNTOFEVENTS in each row is a different search that returns a number, and TOTAL is the product of COUNTEVENTS and PRICE.

For example, for Type1, the search is index=main sourcetype=any1 FIELD1=A OR B | stats count and in Type2 is index=main sourcetype=any1 FIELD1=W OR X OR Z |stats count

How I can append columns and rows one by one? I tried by appendcols command without luck.

| inputlookup data.csv | TABLE ID,TYPE,PRICE | appendcols [search index=main sourcetype=any1 FIELD1=A OR B | stats count] | appendcols [search index=main sourcetype=any1 FIELD1=W OR X OR Z | stats count]

Any advice?

Thanks!

0 Karma

Legend

Try the following search:

index="main" sourcetype="any1" FIELD1="A" OR FIELD1="B" OR FIELD1="W" FIELD1="X" OR FIELD1="Z" | eval TYPE=case(FIELD1=="A" OR FIELD1=="B","Type1",FIELD1=="W" OR FIELD1=="X" OR FIELD1=="Z","Type2",1==1,"UNKNOWN") | lookup data.csv TYPE |stats count as COUNTOFEVENTS sum(PRICE) as TOTAL by TYPE | fillnull value=0 TOTAL 

Use Case statement to match FIELD1 values as A OR B to determine Type1, and W, X Z for Type2.
I have created default case 1==1 to call all other values of FIELD1 as UNKNOWN, however that is just a fail safe which should not occur. Since I have already filtered required FIELD1 values in my base search as A, B, W, X, Z. This is recommended search approach to filter required events as early as possible. If you put FIELD1=* as the only base search filter for FIELD1 then you would be able to get UNKNOWN Type, provided your events have FIELD1 values other than A, B W, X, Z as well.

PS: Your data.csv example has price with comma. I think it should be decimal.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Builder

Thanks! My problem is that the COUNTOFEVENTS column has a different search in each case (almost 150), meanwhile the TOTAL is the product of PRICE and COUNTOFEVENTS. Any suggestion?

0 Karma

Legend

I am not sure of exact query that might work for you, however you should consider eventstats to add the COUNTOFEVENTS to each event and then compute TOTAL.

index="main" sourcetype="any1" FIELD1="A" OR FIELD1="B" OR FIELD1="W" FIELD1="X" OR FIELD1="Z" | eval TYPE=case(FIELD1=="A" OR FIELD1=="B","Type1",FIELD1=="W" OR FIELD1=="X" OR FIELD1=="Z","Type2",1==1,"UNKNOWN") | lookup data.csv TYPE | eventstats count as COUNTOFEVENTS  values(PRICE) as PRICE by TYPE | eval TOTAL=PRICE*COUNTOFEVENTS | stats values(COUNTOFEVENTS) as COUNTOFEVENTS sum(TOTAL) as TOTAL | fillnull value=0 TOTAL

PS: I think the query might work without values(PRICE) as PRICE also.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Legend

Here are your searches:

index=main sourcetype=any1 FIELD1=A OR B | stats count 

index=main sourcetype=any1 FIELD1=W OR X OR Z |stats count

The first search is NOT looking for a value of either A or B in FIELD1. To do that, you actually need FIELD1=A OR FIELD1=B
And similarly for the second search.

Second, you have a lookup table that sets values for id and price based on a TYPE, but your events have FIELD1.
The fundamental problem is that you need to map the values for FIELD1 to the values for TYPE. There are two ways to do this:

First solution: instead of having a lookup based on type, add a FIELD1 column to your lookup table. Now the table looks like this:

 ID,TYPE,PRICE,FIELD1
 1,Type1,3.23,A
 1,Type1,3.23,B
 2,Type2 ,4.2,W
 2,Type2 ,4.2,X
 2,Type2,4.2,Z

(Sorry, I was lazy and substituted the American . for the , in the price field.) This solution allows the following search:

index=main sourcetype=any1 FIELD1=A OR FIELD1=B OR FIELD1=W OR FIELD1=X OR FIELD1=Z
| lookup yourLookup FIELD1 OUTPUT ID TYPE PRICE 
| stats count as COUNTOFEVENTS by  ID TYPE PRICE
| eval TOTAL = COUNTOFEVENTS * PRICE

You can also create an automatic lookup so that you don't even need the lookup command in the the above search.

Second solution: Define a second lookup table that maps FIELD1 to the appropriate TYPE. Leave the current lookup unchanged.
New lookup table:

FIELD1,TYPE
A,Type1
B,Type1
W,Type2
X,Type1
Z,Type1

Now of course you will need 2 lookups:

index=main sourcetype=any1 FIELD1=A OR FIELD1=B OR FIELD1=W OR FIELD1=X OR FIELD1=Z
| lookup newLookup FIELD1 OUTPUT TYPE
| lookup yourLookup TYPE OUTPUT ID PRICE
| stats count as COUNTOFEVENTS by  ID TYPE PRICE
| eval TOTAL = COUNTOFEVENTS * PRICE

Overall suggestions:
1. Please don't make your field names ALL CAPS. It is very SQL and it hurts my eyes. And it is completely unnecessary in Splunk.
2. Avoid joins and appends. They are generally not needed in Splunk and they can be expensive. Plus, append/join makes most searches more complex.
3. Try to forget your "SQL thinking." Splunk is very different. Once I quit structuring my searches based on my SQL background, everything got a lot easier.

In Splunk, you want to write a single search that retrieves all of the data at once. Then each step of the pipeline can filter, summarize or calculate based on the retrieved events. BTW, if your lookup table covers all the possible values for FIELD1, perhaps you can entirely eliminate the "FIELD1=A OR FIELD1=B OR FIELD1=W OR FIELD1=X OR FIELD1=Z" in the searches and simply search for "index=main sourcetype=any1"

Keep asking questions on answers.splunk.com! Lots of folks in the community will help you translate to the Splunk way of thinking, and simplify your searches!

0 Karma

Builder

Thank you. I don't use type as lookup, in fact, for this situation, i don't have a field to match. My problem: I need that certain users set prices one time per month and my plan is use to Lookup Editor App. I should "complement" the lookup with these two columns: countofevents and total (i learnt, no uppercases). Maybe my "approach" is incorrect to solve the problem, i don't know.

0 Karma

Legend

@lguinn SPL is case sensitive for Field Names and not field values. Which implies all of the following three are different Field1, FIELD1 and field1. I think it is up to the developer's Field Naming conventions and Splunk will not mind or complain about field casing.

When we are naming fields ourselves, especially through cvs files, we should be cautious not to start field names with number also not to have spaces. Splunk handles both but not so "gracefully" I would say.

Field values are case insensitive. Infact all SPL keywords (and even SPL commands can be in Uppercase as well) and Splunk will not complain.

Following is the documentation on transitioning from SQL to SPLUNK:
http://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/SQLtoSplunk

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!