topic Re: Help on subsearch in Splunk Search

Help on subsearch

jip31 — Wed, 30 Sep 2020 00:09:04 GMT

Hello

I use the search below
it works fine..... BUT for some host, I cant catch the fields there is in the subsearch
It's strange because if I execute the subsearch index=master-data-lookups sourcetype=":view_splunk_assets" apart, I have results....
So what is the problem please?

index="X" sourcetype="Y" source="Z"  EventCode=6008
| dedup host 
| table _time host EventCode 
| join type=left host 
    [ search index=master-data-lookups sourcetype=":view_splunk_assets" 
    | stats count by HOSTNAME SITE ROOM TOWN CLIENT_USER COUNTRY OS 
    | fields - count 
    | rename HOSTNAME as host] 
| table _time host COUNTRY TOWN SITE ROOM CLIENT_USER OS 
| sort -_time limit=10

Re: Help on subsearch

riddhichandaran — Fri, 12 Apr 2019 13:32:08 GMT

@jip31 I think the issue is you are performing left join here so it returns all host of the main search and only returns the host of subsearch which is common with the main search so for the host which is from the main search does not have fields of subsearch

if you want all host with these fields remove type=left

Hope it helps!

Re: Help on subsearch

dmarling — Fri, 12 Apr 2019 13:32:21 GMT

Hi @jip31 , I recognize this query. 🙂 The join is case sensitive so if the data of the HOSTNAME has different case than the host in the other query it won't match. This should fix that if that's the case:

index="X" sourcetype="Y" source="Z" EventCode=6008 
| dedup host 
| eval host=lower(host)
| table _time host EventCode 
| join type=left host 
    [ search index=master-data-lookups sourcetype=":view_splunk_assets" 
    | stats count by HOSTNAME SITE ROOM TOWN CLIENT_USER COUNTRY OS 
    | fields - count 
    | rename HOSTNAME as host
    | eval host=lower(host)] 
| table _time host COUNTRY TOWN SITE ROOM CLIENT_USER OS 
| sort -_time limit=10

If that works, I'll convert this to an answer.

Re: Help on subsearch

somesoni2 — Fri, 12 Apr 2019 14:28:17 GMT

Give this a try

(index="X" sourcetype="Y" source="Z"  EventCode=6008) OR (ndex=master-data-lookups sourcetype=":view_splunk_assets" )
| eval host=coalesce(HOSTNAME,host)
| time=if(EventCode="6008",_time,null())
| stats values(sourcetype) as sts max(time) as _time values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY) values(OS) as OS by host
| where NOT (mvcount(sts)=1 AND sts=":view_splunk_assets")
 | table _time host COUNTRY TOWN SITE ROOM CLIENT_USER OS 
 | sort -_time limit=10

Re: Help on subsearch

jip31 — Sat, 13 Apr 2019 09:27:15 GMT

hello it doesnt works and its not a case sensitive issue because all the host are uppercase...

Re: Help on subsearch

jip31 — Sat, 13 Apr 2019 09:39:59 GMT

hi
with this, I have host match well
BUT.....
There is something wrong
when I execute my search I have host which correspond to index="X" sourcetype="Y" source="Z" EventCode=6008 even if for some of them the matching not works (reason why I have opened this topic 😉
but if I delete type=left, these host doenst appear in the result
normally the should appear and match with the csv table....
I dont understand anything...

Re: Help on subsearch

jip31 — Sat, 13 Apr 2019 09:44:31 GMT

hi somesoni
when I launch it it says "mismatch quote or parenthesis"

Re: Help on subsearch

jip31 — Wed, 30 Sep 2020 00:05:47 GMT

your request works, thanks, but it is very long...
is it possible to accelerate it please?
could you explain me why when I m doing index="x" sourcetype=y source="z" EventCode=6008 on a 30 days period I have 6 events so 6 hosts but when I m doing `index="x" sourcetype=y source="z" EventCode=6008 (Level=1 OR Level=2 OR Level=3)

I have also 6 events on the same period but only 3 events are matching with my CSV file?
Last question : the host list I want to compare is in a csv file
Do I just have to add [| inputlookup host.csv]after (index="X" sourcetype="Y" source="Z" EventCode=6008) OR (ndex=master-data-lookups sourcetype=":view_splunk_assets" )??
Thanks in advance