Dashboards & Visualizations

How to joins Tables for this scenario?

Shan1490
New Member

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

Labels (2)
0 Karma

to4kawa
Ultra Champion

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= ....

0 Karma

Shan1490
New Member

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= ....

0 Karma

to4kawa
Ultra Champion

Is there a problem with my query?

0 Karma

Shan1490
New Member

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

 

0 Karma

to4kawa
Ultra Champion

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

What's the problem with my query?

0 Karma
Get Updates on the Splunk Community!

User Groups | Upcoming Events!

If by chance you weren't already aware, the Splunk Community is host to numerous User Groups, organized ...

Splunk Lantern | Spotlight on Security: Adoption Motions, War Stories, and More

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...