topic Re: join in Dashboards & Visualizations

join

pavithra — Wed, 12 Jun 2024 04:56:36 GMT

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

Re: join

bowesmana — Wed, 12 Jun 2024 05:16:12 GMT

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

Re: join

pavithra — Wed, 12 Jun 2024 05:18:21 GMT

yes

I have created regex to extract incident details and source host

Re: join

bowesmana — Wed, 12 Jun 2024 05:22:40 GMT

and your second data set contains these fields?

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

Re: join

pavithra — Wed, 12 Jun 2024 05:22:55 GMT

yes

Re: join

bowesmana — Wed, 12 Jun 2024 05:31:18 GMT

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.

Re: join

pavithra — Wed, 12 Jun 2024 09:19:35 GMT

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