topic Re: Table joins for this scenario in Dashboards & Visualizations

How to joins Tables for this scenario?

Shan1490 — Thu, 23 Jul 2020 04:03:19 GMT

Search results

Job,Id aaa,1234 ccc,2345 ddd,9879 fff,6743 eee,8754 zzz,4006

Lookup file

Job1, Job2 , Job3 ccc,eee,zzz ddd,fff,aaa

Output table should look like below:

Job1,id1,Job2,id2,Job3,id3 ccc,2345,eee,8754,zzz,4006 ddd,9879,fff,6743,aaa,1234

Tried join, append, appendcols but all are returning incorrect results

Re: Table joins for this scenario

to4kawa — Wed, 22 Jul 2020 22:22:37 GMT

sample:

| makeresults | eval _raw="Job1, Job2, Job3 ccc,eee,zzz ddd,fff,aaa" | multikv forceheader=1 | table Job* | eval header="Job" | untable header job thing | streamstats count(eval(job="Job1")) as session | append [| makeresults | eval _raw="Job,Id aaa,1234 ccc,2345 ddd,9879 fff,6743 eee,8754 zzz,4006" | multikv forceheader=1 | table Job Id ] | eval thing=coalesce(thing,Job) | selfjoin thing | sort session job | eval {job}=thing | table session Job1 Job2 Job3 Id | streamstats count as number by session | foreach number [| eval Id{<<FIELD>>} = Id] | stats values(Job*) as Job* values(Id*) as Id* by session | table Job1 Id1 Job2 Id2 Job3 Id3

recommend:

| inputlookup yourlookup
| eval header= .... | append [search yoursearch ] | eval thing= ....

Re: Table joins for this scenario

Shan1490 — Thu, 23 Jul 2020 04:09:20 GMT

Hi to4kawa,

I have mentioned only 6 sample records, there are more than 500+ records as part of my search and in lookup file as well

Also I can't append my search because the search is based on two different lookup. in that case how to use

recommend:

| inputlookup yourlookup
| eval header= .... | append [search yoursearch ] | eval thing= ....

Re: Table joins for this scenario

to4kawa — Thu, 23 Jul 2020 09:47:29 GMT

Is there a problem with my query?

Re: Table joins for this scenario

Shan1490 — Thu, 23 Jul 2020 17:30:25 GMT

Search results

Index=appln sourcetype=alog|lookup abc.csv..........| eval.....|eval.....| values(Job) ,values(id)

Above search results 600+ rows in below format

Job,Id

aaa,1234

ccc,2345

ddd,9879

fff,6743

eee,8754

zzz,4006

Lookupfile (has 600+ rows)

Job1, Job2 , Job3

ccc,eee,zzz

ddd,fff,aaa

Output table should look like below(600 + rows)

Job1,id1,Job2,id2,Job3,id3

ccc,2345,eee,8754,zzz,4006

ddd,9879,fff,6743,aaa,1234

In the above case how will I get id1, id2 ,id3 results from above search by joining with lookup file

Re: Table joins for this scenario

to4kawa — Thu, 23 Jul 2020 17:36:03 GMT

your query doesn't use my query's method. and I'm not sure your query.

What's the problem with my query?