join

[pavithra]

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"
| selfjoin source_host
[ search index=acn_lendlease_certificate_tier3_idx tower=*
| table *]
| 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")
| where alert_value="Active"
| search Tower="*" AND alert_value="*"
| sort "Days To Expire"
| rename instance as "Serial Number / Server ID", Tower as "Certificate Type" , source_host as Certificate , alert_value as "Certificate Status"


I am trying to map incident number with respect to source_host using join command
but its not working as expected

[bowesmana]

Are you using selfjoin or join?

Either way, selfjoin is not the right command - join is also not the way to do things in Splunk as it has limitations, however, your SPL indicates your 2 data sets have 

index=acn_ac_snow_ticket_idx - INC (Ticket_Number) uid log_description source_host

index=acn_lendlease_certificate_tier3_idx - tower, metric_value, alert_value, add_info, instance, source_host

and you are trying to join these two on source_host

[pavithra]

yes 

I have created regex to extract incident details and source host

[bowesmana]

and your second data set contains these fields?

tower, metric_value, alert_value, add_info, instance, source_host, Ticket_Number

[pavithra]

yes

[bowesmana]

Your original search is rather unclear, so here is an attempt at removing the need to join, i.e. to do it the Splunk way, so it searches both data sets and creates the common fields 

Your original join attempt wanted source_host but you are splitting by ticket number, so is is possible that there can be multiple source_host per ticket number?

Can there also be more than one instance per ticket number?

(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*") OR
(index=acn_lendlease_certificate_tier3_idx tower=*)
| rex field=_raw "\"(?<INC>INC\d+)," 
| eval ticket=coalesce(INC, Ticket_Number)
| rex field=uid "(?i)^(?P<snow_source_host>.+?)__" 
| eval source_host=coalesce(snow_source_host, source_host)
| 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" values(instance) by ticket 
| eval alert_value=case(alert_value==100,"Active",alert_value==300,"About to Expire", alert_value==500,"Expired") 
| where alert_value="Active" 
| search Tower="*" AND alert_value="*" 
| sort "Days To Expire" 
| rename instance as "Serial Number / Server ID", Tower as "Certificate Type" , source_host as Certificate , alert_value as "Certificate Status"

Not sure if this will give you what you want - but if not, please provide some anonymised data for each data type and show how you are trying to combine them because it's not clear from the search.

[pavithra]

Hi bowesmana
Thanks for the efforts
we have data sets

index=acn_lendlease_certificate_tier3_idx tower=Self_Signed_Certificate
| 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
| eval alert_value=case(alert_value==100,"Active",alert_value==300,"About to Expire", alert_value==500,"Expired")
| where alert_value="About to Expire"
| search Tower="*" AND alert_value="*"
| sort "Days To Expire"
| rename instance as "Serial Number / Server ID", Tower as "Certificate Type" , source_host as Certificate , alert_value as "Certificate Status"

here i am trying to add one more coulmn called incident 
To extract the incident details with respect to certificate values
If inc is available , then it should display numbers, orelse null
To extract the INC, using the below query

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"