Help with data enrichment

[Danielle_marks]

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 🙂

[Danielle_marks]

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? 

[somesoni2]

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

[somesoni2 · Accepted solution]

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]

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