Have 2 DB connection and i want to compare the DB1 connection HRA field keeping as primary key say here in this example
it is HRA field with DB2 connection ex below field is HRK ,so that it compares with all the data values and if present it should give HRA field value else "yet to be applied"
Sample query to explain the scenario::
| makeresults
| eval HRA="AAAA,BBBB,CCCC"
| eval HRA=split(HRA,",")
| mvexpand HRA
|append [| makeresults |search
| eval HRK="DDDD,BBBB,CCCC"
| eval HRK=split(HRK,",")
| mvexpand HRK]
expected output of this will be as below:
HRA HRK _time
AAAA yet to be applied
BBBB BBBB
CCCC CCCC
| makeresults
| eval HRA="AAAA,BBBB,CCCC"
| eval HRA=split(HRA,",")
| mvexpand HRA
|append [| makeresults |search
| eval HRK="DDDD,BBBB,CCCC"
| eval HRK=split(HRK,",")
| mvexpand HRK]
| appendpipe
[ eval tmp=coalesce(HRA,HRK)
| selfjoin tmp]
| streamstats count
| reverse
| dedup HRA
| sort count
| table HRA HRK _time
| fillnull value="yet to be applied"
Hi, @naliniasb
I make the query from your sample. how about this?
Hi @naliniasb:
can ou try by join command:
| makeresults
| eval HRA="AAAA,BBBB,CCCC"
| eval HRA=split(HRA,",")
| mvexpand HRA
| join type=left HRA [| makeresults
| eval HRA="DDDD,BBBB,CCCC"
| eval HRA=split(HRA,",")
| mvexpand HRA
| eval HRK=HRA | table HRA, HRK]
| eval HRK=if(isnull(HRK),"yet to be applied",HRK)
nope this is not going to work under join you have given same HRA which is not expected
i have 2 DB connection and my requirement is with one of primary key field i need to compare the other DB connection with field(all the values in that field) and say if present then keep same value if not set "yet to be applied"
Can anyone suggest on this