topic Re: Print searches with zero results in Splunk Search

Print searches with zero results

jfraley — Tue, 02 Jul 2019 20:20:13 GMT

I have the following search:

index=ldap_csv |rename uid as user, extraced_host as host 
   | join user [search source=/var/log/secure   vendor_action="Accepted"
   | stats count as total_logins,  earliest(_time) as "first_login" latest(_time) as "last_login" by host user 
   |eval "first_login"=strftime(first_login, "%m/%d/%y - %I:%M:%S:%p")
   |eval "last_login"=strftime(last_login, "%m/%d/%y - %I:%M:%S:%p") ] 
   |table user host  first_login last_login total_logins

that returns exactly what I need except the results with zero total_logins. I have read and tried a number of solutions, but can not get the results I need. Any guidance here would be appreciated.

Re: Print searches with zero results

woodcock — Tue, 02 Jul 2019 22:36:52 GMT

Never use join. Try this:

(index=ldap_csv) OR (index=YouShoulAlwaysSpecifyAnIndex AND sorucetype=AndSourcetypeToo AND source=/var/log/secure AND vendor_action="Accepted"
| eval user=if(index=="ldap_csv", uid, user)
| eval host=if(index=="ldap_csv", extraced_host, host)
| stats count AS total_logins,  min(_time) AS first_login max(_time) AS last_login BY host user index
| search index!="ldap_csv"
| fieldformat first_login = strftime(first_login, "%m/%d/%y - %I:%M:%S:%p")
| fieldformat last_login = strftime(last_login, "%m/%d/%y - %I:%M:%S:%p")
| table user host first_login last_login total_logins

Re: Print searches with zero results

bandit — Wed, 30 Sep 2020 01:10:09 GMT

Append query that returns a list of all users

something like this for your first query:
all user query here | table user | eval total_logins=0
| append [ search query with actual events ]
| stats count AS total_logins, min(_time) AS first_login max(_time) AS last_login BY host user index
| eval count=count-1

Re: Print searches with zero results

jfraley — Wed, 30 Sep 2020 01:13:54 GMT

I actually pasted the wrong search. This is what I have:

index="syslog_main" sourcetype=linux_secure vendor_action="Accepted"
[ search index=ldap_csv |fields uid extracted_host |rename uid as user, extracted_host as host ]
| stats count as total_logins, earliest(_time) as "first_login" latest(_time) as "last_login" by host user
| eval "first_login"=strftime(first_login, "%m/%d/%y - %I:%M:%S:%p")
|eval "last_login"=strftime(last_login, "%m/%d/%y - %I:%M:%S:%p")
| table user host first_login last_login total_logins

I only want the users and hosts from the index=ldap_csv in my table, I also need to know when there are no entries in the index=syslog_main for those users.

Re: Print searches with zero results

jfraley — Wed, 30 Sep 2020 01:13:56 GMT

woodstock, I like your format, but the results are not what I am looking for. I only want the table to contain the users and hosts from the index=ldap_csv. Your results returned a concatenated table. I also need to know when the the user/host does not have any entries in the syslog_main index.

Re: Print searches with zero results

jfraley — Wed, 30 Sep 2020 01:14:05 GMT

This is what I have now

index="syslog_main" sourcetype="linux_secure" vendor_action="Accepted"
[ search index=ldap_csv |fields uid extracted_host |rename uid as user, extracted_host as host ]
| stats count as total_logins, earliest(_time) as "first_login" latest(_time) as "last_login" by host user
| eval "first_login"=strftime(first_login, "%m/%d/%y - %I:%M:%S:%p")
|eval "last_login"=strftime(last_login, "%m/%d/%y - %I:%M:%S:%p")
| table user host first_login last_login total_logins

Re: Print searches with zero results

jfraley — Wed, 03 Jul 2019 14:53:45 GMT

I tried your suggestion. I actually put the snippet in multiple places to see if I could get the 0 results, but I never got them.

Re: Print searches with zero results

bandit — Fri, 05 Jul 2019 15:24:44 GMT

I switched the join to append.

Re: Print searches with zero results

jfraley — Wed, 30 Sep 2020 01:14:56 GMT

I was able to get the results I needed using:

index=syslog_main sourcetype="linux_secure" vendor_action="Accepted" |stats count as found by user host |appendcols [search index="ldap_csv" |rename uid as user, extracted_host as host]|table user host found

Re: Print searches with zero results

woodcock — Fri, 05 Jul 2019 22:36:27 GMT

Try this:

(index=ldap_csv) OR (index=YouShoulAlwaysSpecifyAnIndex AND sorucetype=AndSourcetypeToo AND source=/var/log/secure AND vendor_action="Accepted"
| eval user=if(index=="ldap_csv", uid, user)
| eval host=if(index=="ldap_csv", extraced_host, host)
| stats count AS total_logins,  min(_time) AS first_login max(_time) AS last_login dc(index) AS index_count values(index) AS indexes BY host user
| search indexes="ldap_csv"
| fieldformat first_login = strftime(first_login, "%m/%d/%y - %I:%M:%S:%p")
| fieldformat last_login = strftime(last_login, "%m/%d/%y - %I:%M:%S:%p")
| table user host first_login last_login total_logins index_count

Re: Print searches with zero results

woodcock — Fri, 05 Jul 2019 22:36:45 GMT

See my new answer.

Re: Print searches with zero results

jfraley — Wed, 30 Sep 2020 01:19:23 GMT

Thanks, with just a few modifications I have exactly what I was looking for.

This is what I now have:

(index=ldap_csv) OR (index=syslog_main AND sourcetype="linux_secure" AND source=/var/log/secure AND vendor_action="Accepted")
| eval user=if(index=="ldap_csv", uid, user)
| eval host=if(index=="ldap_csv", extracted_host, host)
| stats count AS total_logins, min(_time) AS first_login max(_time) AS last_login dc(index) AS index_count values(index) AS indexes BY host user
| search indexes="ldap_csv"
| fieldformat first_login = strftime(first_login, "%m/%d/%y - %I:%M:%S:%p")
| fieldformat last_login = strftime(last_login, "%m/%d/%y - %I:%M:%S:%p")
| eval total_logins=total_logins-1
| eval first_login=if(first_login==last_login,"",first_login)
| eval last_login=if(first_login=="","",last_login)
| table user host first_login last_login total_logins index_count