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 🙂
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
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?
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
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
It works! you are the best, thank you so much