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

[RanjiRaje]

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..

[gcusello]

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

[RanjiRaje]

Hi Sir,

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

[gcusello · Accepted solution]

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

[RanjiRaje]

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

[gcusello]

Hi @RanjiRaje,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉