Splunk Search

Help with data enrichment

Danielle_marks
Explorer

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 🙂

Labels (2)
0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

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 | eval from="events"
| append [
| 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 | rename hostName as host  | eval from="lookup"]
| stats values(customerName) as customerName values(from) as from by host hostGuid
| where mvcount(from)=2 | fields - from

View solution in original post

Danielle_marks
Explorer

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? 

0 Karma

somesoni2
Revered Legend

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
0 Karma

somesoni2
Revered Legend

Give this a try

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 | eval from="events"
| append [
| 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 | rename hostName as host  | eval from="lookup"]
| stats values(customerName) as customerName values(from) as from by host hostGuid
| where mvcount(from)=2 | fields - from

Danielle_marks
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...