join query issue

[pavithra]

Hi all

I am trying to fetch incident details from servicenow, but its showing duplicate values 

index=acn_lendlease_certificate_tier3_idx tower=Entrust_Certificate
| join type=left source_host max=0
[search index=acn_ac_snow_ticket_idx code_message=create uid="*Saml : Days to expire*" OR uid="*Self_Signed : Days to expire*" OR uid="*CA : Days to expire*" OR uid="*Entrust : Days to expire*"
| rex field=_raw "\"(?<INC>INC\d+),"
| rex field=uid "(?i)^(?P<source_host>.+?)__"
| table INC uid log_description source_host
| dedup INC uid log_description source_host
| rename INC as "Ticket_Number"]
| fillnull value="NA" Ticket_Number
| stats latest(tower) as Tower, latest(source_host) as source_host , latest(metric_value) as "Days To Expire", latest(alert_value) as alert_value, latest(add_info) as "Additional Info" by instance,Ticket_Number
| eval alert_value=case(alert_value==100,"Active",alert_value==300,"About to Expire", alert_value==500,"Expired")
| search Tower="*" alert_value="*" alert_value="About to Expire"
| sort "Days To Expire"
| dedup instance
| rename instance as "Serial Number / Server ID", Tower as "Certificate Type" , source_host as Certificate , alert_value as "Certificate Status"

[PickleRick]

1. Please post your SPL in code block or preformatted paragraph next time - it improves readability

2. We don't know your data - maybe it's that you have duplicates in your events. Or maybe it's some search flaw. Can't tell just by looking at the search itself.

3. What do you mean by "duplicate values"? Multivalued fields with repeated value? Multiple results sharing the same value in one (all?) of the fields?

4. Typically you diagnose such things by either removing steps from the end and checking whether the intermediate results make sense or starting from the beginning and adding steps one by one and checking if the results make sense.

5. Join is usually (but not always) _not_ the way to go. In your case the joined subsearch contains a wildcard at the beginning of the search term which means that if you have a significant amount of data to search the subsearch might be siliently finalized and return incomplete/wrong results.