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!
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.
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?
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.
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!
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.
@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