Splunk Search

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

marina_rovira
Contributor

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! 🙂

1 Solution

marina_rovira
Contributor

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! 🙂

View solution in original post

0 Karma

marina_rovira
Contributor

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! 🙂

0 Karma

marina_rovira
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...