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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...