Print searches with zero results

[jfraley]

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.

[woodcock · Accepted solution]

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

[jfraley]

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

[jfraley]

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

[jfraley]

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.

[bandit]

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

[jfraley]

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.

[bandit]

I switched the join to append.

[woodcock]

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

[jfraley]

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.

[jfraley]

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

[woodcock]

See my new answer.