Hi guys,
I am having some issues extraction a comparaison between two different search,
Let's assume the following:
Result search A:
ID
X_584
X_571
X_568
X_999
Result search B:
ID | Valid
1 | X (571)
2 | X (584)
3 | X (382), X (584), X (571), X (568)
4 | X (568), X (571)
Desired result
ID | count | Exist
X_584 | 2 | True
X_571 | 3 | True
X_568 | 2 | True
X_999 | 0 | False
I tried to join and use match , but the problem persists first because :
- Search A has values with "_" while in search B is with space " ( )" --> tried to rex field but the search seemed so complicated
Then i was stuck on how to string match both values and put an eval for Exist field
Do you guys have any clue on how to solve this?
kind regards to those who thought about this 😉
@habrhi
Try this.
<<FIRST_SEARCH>>
| join type=left ID [
<<SECOND_SEARCH>> | rex max_match=0 field=Valid "\((?<data>\d+)\)" | stats count by data | eval ID="X_".data ] | eval Exist = if(isnull(count),"False","True") | fillnull value=0 count | table ID count Exist
Sample Search:
| makeresults | eval _raw="ID
X_584
X_571
X_568
X_999" | multikv forceheader=1
| join type=left ID [ | makeresults | eval _raw="ID Valid
1 X (571)
2 X (584)
3 X (382), X (584), X (571), X (568)
4 X (568), X (571)" | multikv forceheader=1 | table ID Valid | rex max_match=0 field=Valid "\((?<data>\d+)\)" | stats count by data | eval ID="X_".data
] | eval Exist = if(isnull(count),"False","True") | fillnull value=0 count | table ID count Exist
Thanks
@habrhi
Try this.
<<FIRST_SEARCH>>
| join type=left ID [
<<SECOND_SEARCH>> | rex max_match=0 field=Valid "\((?<data>\d+)\)" | stats count by data | eval ID="X_".data ] | eval Exist = if(isnull(count),"False","True") | fillnull value=0 count | table ID count Exist
Sample Search:
| makeresults | eval _raw="ID
X_584
X_571
X_568
X_999" | multikv forceheader=1
| join type=left ID [ | makeresults | eval _raw="ID Valid
1 X (571)
2 X (584)
3 X (382), X (584), X (571), X (568)
4 X (568), X (571)" | multikv forceheader=1 | table ID Valid | rex max_match=0 field=Valid "\((?<data>\d+)\)" | stats count by data | eval ID="X_".data
] | eval Exist = if(isnull(count),"False","True") | fillnull value=0 count | table ID count Exist
Thanks
Thanks alot, the rex field saved me. Appreciate it