How do I join data between an index and lookup table or two indexes?

[marina_rovira]

Hi there!

I have an issue. On one hand, I have an index with a lot of information and duplicated values. And on the other hand, I have another file, a static file, that shares a field with the other one.

This second file, I have it as an index and also as a lookup table, because I cannot make my search work, so I was trying different things.

The first file has these fields:
Created At, Number, Cti Item... and others

The second file:
Customer,Service Group,Service Def,Sid...and others

My search has to pick the number of Number(dedup values) that each have "Service Def"
For now, I have this using the join command:

index="mttfr"   "Cti Item"="SID*"  | rename "Cti Item" as Sid_temp | eval month = strftime(_time,"%m")  | search month=10 | eval year= strftime(_time,"%Y") | search year=2015 | dedup Number | eval Sid=if(match(Sid_temp, ","),mvindex(split(Sid_temp, ","),0), Sid_temp) | fields Number,Sid,month,Sid_temp | join Sid type=left [search index="service_definitions"]  | table Number,Sid,"Service Def",Sid_temp

Is not working for all Sid values. Some of them have the "Service Def" empty in the result table, but in the file, they do have this value.

Could someone help me? As I said, if you find it easier making this static second file as lookup table, I've already did it, so there a possibility is there.

Thank you! 🙂

[marina_rovira · Accepted solution]

I resolve it myself with a lookup table!

This is my search:

index="mttfr" |  rename "Configuration Items" as Sid_temp | eval Sid=if(match(Sid_temp, ","),mvindex(split(Sid_temp, ","),0), Sid_temp) | search Sid="SID*" | lookup lookup-table.csv  Sid    | dedup Number |  eval month = strftime(_time,"%m")  | search month=* | eval year= strftime(_time,"%Y") | search year=2015 | chart count(Number) by month "Service Def"

In this way, the column NULL is almost 0, but it depends also by the character afetr the SID* sentence.
Now I have a column by month that shows how much Number I have for each "Service Def".

Thank you anyway to the people that at least, think about it! 🙂

[marina_rovira]

The split statement is because the field "Cti Item" can have more information than what I need, and the information I don't what is behind a comma.