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!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...