Splunk Search

How do I write this search to find these 2 fields with the lookup hostname?

RanjiRaje
Explorer

Hi, I need your suggestion here. Please guide me

I have a lookup file with list of hosts. I need to compare it with splunk data and populate the matching count.

Query: 

index=idxname (sourcetype=sourceA desc=windows) OR (sourcetype=sourceB)
| fields device.hostname,event.HostName
| rename device.hostname as hostfield, event.HostName as hostfield
| lookup lookupfilename fieldname as hostfield OUTPUTNEW Platform
| fields hostfield,Platform
| stats dc(hostfield) as "totalcount" by Platform



I have 2 different sourcetype under the same index. sourceA has a field "device.hostname" and sourceB has a fields "event.HostName".

[  lookup file --> hostfield: AA,BB,CC

sourceA,device.hostname --> AA,XX,YY

sourceB,event.HostName --> CC,PP,KK

my output count should be 2    ]

If any of these 2 fields value is matching with the lookup hostname, then it should be considered.
I tried rename command. Please provide your inputs..

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @RanjiRaje,

pease try this:

index=idxname (sourcetype=sourceA desc=windows) OR (sourcetype=sourceB)
| rename device.hostname AS hostname event.HostName AS HostName
| eval hostfield=coalesce(hostname,HostName)
| lookup lookupfilename fieldname as hostfield OUTPUTNEW Platform
| stats dc(hostfield) AS "totalcount" BY Platform

Ciao.

Giuseppe

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @RanjiRaje,

instead rename, you could use eval coalesce to have one field called hostname:

index=idxname (sourcetype=sourceA desc=windows) OR (sourcetype=sourceB)
| eval hostfield=coalesce(device.hostname,event.HostName)
| lookup lookupfilename fieldname as hostfield OUTPUTNEW Platform
| stats dc(hostfield) AS "totalcount" BY Platform

Ciao.

Giuseppe

0 Karma

RanjiRaje
Explorer

Hi Sir,

I tried with eval - coalesce command too. But it didnt give the output.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @RanjiRaje,

pease try this:

index=idxname (sourcetype=sourceA desc=windows) OR (sourcetype=sourceB)
| rename device.hostname AS hostname event.HostName AS HostName
| eval hostfield=coalesce(hostname,HostName)
| lookup lookupfilename fieldname as hostfield OUTPUTNEW Platform
| stats dc(hostfield) AS "totalcount" BY Platform

Ciao.

Giuseppe

0 Karma

RanjiRaje
Explorer

Thanks sir for a quick response. renamed and tried using coalesce. It worked !!!!

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @RanjiRaje,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated :winking_face:

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...