topic Re: SPL query in Splunk Search

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

RanjiRaje — Tue, 04 Apr 2023 16:43:15 GMT

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

Re: SPL query

gcusello — Mon, 03 Apr 2023 16:00:35 GMT

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

Re: SPL query

RanjiRaje — Mon, 03 Apr 2023 16:04:42 GMT

Hi Sir,

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

Re: SPL query

gcusello — Mon, 03 Apr 2023 16:10:42 GMT

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

Re: SPL query

RanjiRaje — Mon, 03 Apr 2023 17:03:42 GMT

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

Re: SPL query

gcusello — Tue, 04 Apr 2023 06:36:02 GMT

Hi @RanjiRaje,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉