Help on subsearch

[jip31]

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

[somesoni2]

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

[jip31]

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

[jip31]

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)

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

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

[dmarling]

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.

[jip31]

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

[riddhichandaran]

@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!

[jip31]

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