topic Re: Help with data enrichment in Splunk Search

Help with data enrichment

Danielle_marks — Thu, 07 Jul 2022 14:20:17 GMT

Hi everyone!

I would appreciate your help with the following search, I can't find how to do that,

I need to add the customer name to the list of hosts

1. the below search return a list of hosts and their Guid with certificates that going to be expired :

index= indexname environment=prod | eval host=rtrim(host, ".prod.net") | eval host=(host."-prod") |lookup host-guid hostName as host Output hostGuid |table host hostGuid

2. the below search return the customer name per host :

| inputlookup workspace where poolGuid!=* [| inputlookup workspaceServer where hostGuid=".*" | rename workspaceServerGuid as currentWorkspaceServerGuid | return currentWorkspaceServerGuid] | lookup workspaceServer workspaceServerGuid as currentWorkspaceServerGuid output hostGuid name as core | lookup host hostGuid output hostName | rename currentCustomerGuid as customerGuid name as workspaceName | lookup customer customerGuid output name as customerName | stats count by hostName hostGuid core customerName customerGuid workspaceName workspaceGuid | fields - count

how I can combine for those 2 queries and get the customer name just for hosts from the first search #1

Thank you 🙂

Re: Help with data enrichment

somesoni2 — Wed, 06 Jul 2022 14:05:31 GMT

Give this a try

Re: Help with data enrichment

Danielle_marks — Wed, 06 Jul 2022 17:32:50 GMT

It works! you are the best, thank you so much

Re: Help with data enrichment

Danielle_marks — Thu, 07 Jul 2022 06:22:38 GMT

Hi again,

I found few logical problems with the #2 query, so I created a data model and I need to use with tstats :

| tstats count from datamodel=datamodel_name where `ensure_model_activity`
[| inputlookup host-dc5prod where hostGuid=".*"
| eval host=hostName."*"
| return 10 host] by Datamodelfiled.workspaceGuid summariesonly=f allow_old_summaries=t
| rename Datamodelfiled.* as *
| lookup workspace workspaceGuid output name as workspaceName currentCustomerGuid as customerGuid
| lookup customer customerGuid output name as customerName
| table customerName customerGuid workspaceName workspaceGuid
| fillnull value="Not Found"

I tried to run it with the same append you created but maybe it works differently with tstats :

index=indexname environment=prod
| rex "Certificates for server agent will expire in (?<expiry>\d+) days"
| stats latest(expiry) AS "Days until expire" BY host environment
| sort 0 "Days until expire"
| eval host=rtrim(host, "prod.net")
| eval host=(host."-prod")
|lookup host hostName as host Output hostGuid
|table host hostGuid "Days until expire"| eval from="events"
| append [
| tstats count from datamodel=datamodelname where `ensure_model_activity`
[| inputlookup host where hostGuid=".*"
| eval host=hostName."*"
| return 10 host] by datamodelfiled.workspaceGuid summariesonly=f allow_old_summaries=t
| rename submissions.* as *
| lookup workspace workspaceGuid output name as workspaceName currentCustomerGuid as customerGuid
| lookup customer customerGuid output name as customerName
| table customerName customerGuid workspaceName workspaceGuid
| fillnull value="Not Found"
| stats count by host hostGuid customerName customerGuid workspaceName workspaceGuid
| fields - count | rename hostName as host | eval from="lookup"
]
| stats values(customerName) as customerName values("Days until expire") as days values(from) as from by host hostGuid
| where mvcount(from)=2 | fields - from

do you know what I did wrong here?

Re: Help with data enrichment

somesoni2 — Thu, 07 Jul 2022 13:51:44 GMT

Query #1 is giving fields host,hostGuid and "Days until expire". Your query #2 should contain field host and hostGuid (which it was in previous version). New version has fields customerName, customerGuid, workspaceName and workspaceGuid, thus there are no matching host/hostGuid causing query to fail.

If workspactName and workspaceGuid actually corresponds to host and hostGuid, rename them in query #2 so that they match, like this

index=indexname environment=prod | rex "Certificates for server agent will expire in (?<expiry>\d+) days" | stats latest(expiry) AS "Days until expire" BY host environment | sort 0 "Days until expire" | eval host=rtrim(host, "prod.net") | eval host=(host."-prod") |lookup host hostName as host Output hostGuid |table host hostGuid "Days until expire"| eval from="events" | append [ | tstats count from datamodel=datamodelname where `ensure_model_activity` [| inputlookup host where hostGuid=".*" | eval host=hostName."*" | return 10 host] by datamodelfiled.workspaceGuid summariesonly=f allow_old_summaries=t | rename submissions.* as * | lookup workspace workspaceGuid output name as workspaceName currentCustomerGuid as customerGuid | lookup customer customerGuid output name as customerName | table customerName customerGuid workspaceName workspaceGuid | fillnull value="Not Found" | stats count by host hostGuid customerName customerGuid workspaceName workspaceGuid | fields - count | rename workspaceName as host workspaceGuid as hostGuid | eval from="lookup" ] | stats values(customerName) as customerName values("Days until expire") as days values(from) as from by host hostGuid | where mvcount(from)=2 | fields - from