Another method to connect two search results other than using the join command?

xnx_1012 — Sat, 16 May 2020 13:08:05 GMT

Hello is there another way to connect these two other than join... I have read that stats is faster than join ... is it possible that stats can connect all these together?

SPL 1

(index=* source=/var/log/secure* AND TERM(sudo) AND " root"  AND (TERM(adduser) OR TERM(chown) OR TERM(userdel) OR TERM(chmod) OR TERM(usermod) OR TERM(useradd))  AND COMMAND!="*egrep*") OR
(index="*" source=/var/log/secure* AND TERM(sshd) AND "Accepted password" AND TERM(from) AND TERM(port) 
    [ 
    search index=* source=/var/log/secure* AND TERM(sudo) AND (TERM(adduser) OR TERM(chown) OR TERM(userdel) OR TERM(chmod) OR TERM(usermod) OR TERM(useradd)) AND COMMAND!="*egrep*" 
    | regex _raw != ".*bin\/grep|.*bin\/man|.*bin\/which" 
    | regex _raw!= ".*user NOT in sudoers.*" 
    | stats latest(_time) as latest earliest(_time) as mod_time 
    | eval earliest= relative_time(mod_time, "-8h@s") 
    | fields earliest latest
    ])
| regex _raw != ".*bin\/grep|.*bin\/man|.*bin\/which|.*bin\/less|.*bin\/more"

| rex field=_raw "(?<=sudo:)\s*(?P<Users>[[:alnum:]]\S*[[:alnum:]])\s*(?=\:).*(?<=COMMAND\=)(?P<command>.*)"
| rex field=_raw "(?<=for)\s*(?P<Users>[[:alnum:]]\S*[[:alnum:]])\s*(?=from).*(?<=from)\s*(?P<ip>[[:digit:]]+\.[[:digit:]]+\.[[:digit:]]+\.[[:digit:]]+)"
| eval "Command/Events" = replace(command,"^(\/bin\/|\/sbin\/)","")
| eval Time = if(match(_raw,"(?<=sudo:)\s*[[:alnum:]]\S*[[:alnum:]]\s*(?=\:).*(?<=COMMAND\=)*") ,strftime(_time, "%Y-%d-%m %H:%M:%S"),null())
| eval Date = strftime(_time, "%Y-%d-%m")
| eval "Report ID" = "ABLR-007"
| eval "Agency HF" = if(isnull(agencyhf),"",agencyhf)
| stats list(Time) as Time list("Command/Events") as "Command/Events" latest(ip) as "IP Address" by Users Date host index "Report ID" "Agency HF"
| eval counter=mvrange(0,mvcount(Time))
| streamstats count as sessions
| stats list(*) as * by sessions counter
| foreach Time "Command/Events"  [ eval <<FIELD>> = mvindex('<<FIELD>>', counter)]
| fields - counter sessions
| rename index as Agency, host as Hostname
| where Users="root" AND isnull('IP Address')
| fields "Report ID" Time Agency Command/Events Hostname Users Date

SPL 1 Result

SPL 2

(index=* source=/var/log/secure* (TERM(su:) OR TERM(sudo:)) AND "opened for user root") OR
(index="*" source=/var/log/secure* AND TERM(sshd) AND "Accepted password" AND TERM(from) AND TERM(port))
| rex field=_raw ".*(?<=from)\s*(?P<ip>[[:digit:]]+\.[[:digit:]]+\.[[:digit:]]+\.[[:digit:]]+)"
| eval Date = strftime(_time, "%Y-%d-%m")
| rename host as Hostname
| stats values(ip) as "IP Address" by Date Hostname
| where isnotnull('IP Address')

SPL 2 Result

SPL 3 = When is SPL 1 and SPL2 consolidated together by JOIN command

(index=* source=/var/log/secure* AND TERM(sudo) AND " root"  AND (TERM(adduser) OR TERM(chown) OR TERM(userdel) OR TERM(chmod) OR TERM(usermod) OR TERM(useradd))  AND COMMAND!="*egrep*") OR
(index="*" source=/var/log/secure* AND TERM(sshd) AND "Accepted password" AND TERM(from) AND TERM(port) 
    [ 
    search index=* source=/var/log/secure* AND TERM(sudo) AND (TERM(adduser) OR TERM(chown) OR TERM(userdel) OR TERM(chmod) OR TERM(usermod) OR TERM(useradd)) AND COMMAND!="*egrep*" 
    | regex _raw != ".*bin\/grep|.*bin\/man|.*bin\/which" 
    | regex _raw!= ".*user NOT in sudoers.*" 
    | stats latest(_time) as latest earliest(_time) as mod_time 
    | eval earliest= relative_time(mod_time, "-8h@s") 
    | fields earliest latest
    ])
| regex _raw != ".*bin\/grep|.*bin\/man|.*bin\/which|.*bin\/less|.*bin\/more" 
| rex field=_raw "(?<=sudo:)\s*(?P<Users>[[:alnum:]]\S*[[:alnum:]])\s*(?=\:).*(?<=COMMAND\=)(?P<command>.*)"
| rex field=_raw "(?<=for)\s*(?P<Users>[[:alnum:]]\S*[[:alnum:]])\s*(?=from).*(?<=from)\s*(?P<ip>[[:digit:]]+\.[[:digit:]]+\.[[:digit:]]+\.[[:digit:]]+)"
| eval "Command/Events" = replace(command,"^(\/bin\/|\/sbin\/)","")
| eval Time = if(match(_raw,"(?<=sudo:)\s*[[:alnum:]]\S*[[:alnum:]]\s*(?=\:).*(?<=COMMAND\=)*") ,strftime(_time, "%Y-%d-%m %H:%M:%S"),null())
| eval Date = strftime(_time, "%Y-%d-%m")
| eval "Report ID" = "ABLR-007"
| eval "Agency HF" = if(isnull(agencyhf),"",agencyhf)
| stats list(Time) as Time list("Command/Events") as "Command/Events" latest(ip) as "IP Address" by Users Date host index "Report ID" "Agency HF"
| eval counter=mvrange(0,mvcount(Time))
| streamstats count as sessions
| stats list(*) as * by sessions counter
| foreach Time "Command/Events"  [ eval <<FIELD>> = mvindex('<<FIELD>>', counter)]
| fields - counter sessions
| rename index as Agency, host as Hostname
| where Users="root" AND isnull('IP Address')
| fields "Report ID" Time Agency Command/Events Hostname Users Date
| join left Date Hostname 'IP Address'
[search
(index=* source=/var/log/secure* (TERM(su:) OR TERM(sudo:)) AND "opened for user root") OR
(index="*" source=/var/log/secure* AND TERM(sshd) AND "Accepted password" AND TERM(from) AND TERM(port))
| rex field=_raw ".*(?<=from)\s*(?P<ip>[[:digit:]]+\.[[:digit:]]+\.[[:digit:]]+\.[[:digit:]]+)"
| eval Date = strftime(_time, "%Y-%d-%m")
| rename host as Hostname
| stats values(ip) as "IP Address" by Date Hostname
| where isnotnull('IP Address')
]

Re: Another method to connect two search results other than using the join command?

skoelpin — Tue, 19 May 2020 14:26:45 GMT

Yes it certainly can. Few things first, I see you're doing index=* and joining data on an additional query that searches the same index=* data. Step back and look at it from a thousand foot view..

You're querying a large bucket of data via index=*. You're then filtering down results. So you're querying against the same set of data each time duplicating your efforts. Rather than pulling multiple duplicate buckets into a single query set, you should rather search only what's needed and forego the duplicate data search. So rather than filtering out results, you should use eval and create flags which can be filtered down later OR query a single large bucket of data and get everything needed than filter down.

Rather than this

(index=* source=/var/log/secure* AND TERM(sudo) AND " root"  AND (TERM(adduser) OR TERM(chown) OR TERM(userdel) OR TERM(chmod) OR TERM(usermod) OR TERM(useradd))  AND COMMAND!="*egrep*") OR
 (index="*" source=/var/log/secure* AND TERM(sshd) AND "Accepted password" AND TERM(from) AND TERM(port) [ 
     search index=* source=/var/log/secure* AND TERM(sudo) AND (TERM(adduser) OR TERM(chown) OR TERM(userdel) OR TERM(chmod) OR TERM(usermod) OR TERM(useradd)) AND COMMAND!="*egrep*"

Use this

(index=* source=/var/log/secure* AND TERM(sudo) AND " root"  AND (TERM(adduser) OR TERM(chown) OR TERM(userdel) OR TERM(chmod) OR TERM(usermod) OR TERM(useradd))  AND COMMAND!="*egrep*" AND TERM(sshd) AND "Accepted password" AND TERM(from) AND TERM(port) )
| regex _raw != ".*bin\/grep|.*bin\/man|.*bin\/which" 
     | regex _raw!= ".*user NOT in sudoers.*" 
     | stats latest(_time) as latest earliest(_time) as mod_time 
     | eval earliest= relative_time(mod_time, "-8h@s") 
     | fields earliest latest

Re: Another method to connect two search results other than using the join command?

gjanders — Wed, 20 May 2020 05:50:13 GMT

Not an answer but have you looked at the presentation Master Joining Datasets Without Using Join?

topic Another method to connect two search results other than using the join command? in Splunk Search

Another method to connect two search results other than using the join command?

Re: Another method to connect two search results other than using the join command?

Re: Another method to connect two search results other than using the join command?