Splunk Search
Highlighted

Find which hosts a user has not logged in to

New Member

Newbie here, so please be kind!

Not sure if this is even possible, but I need to find out if a user has never logged in to a host. So far I have this:

sourcetype="WMI:UserAccounts" user="Bob" | stats count by host

That gives me the hosts Bob has logged in to, but not the hosts that Bob has not logged in to (which is what I need).

Can this be done?

0 Karma
Highlighted

Re: Find which hosts a user has not logged in to

Builder

Hi Gsd,

Welcome to Splunk! 🙂 Subsearches are going to be your friend here.

Get a list of hosts that Bob has logged into (I've spread the search out for readability, but you can copy/paste as is):

sourcetype="WMI:UserAccounts" user="Bob" 
| table host 
| dedup host

This will give you a list of all hosts that Bob has logged in to, removing duplicate values.

Use this list as a filter to only display hosts that Bob didn't log in to.

sourcetype="WMI:UserAccounts" 
| stats count by host 
| search NOT [
    search sourcetype="WMI:UserAccounts" user="Bob" 
    | table host 
    | dedup host ]
| stats count by host

This should give you a list of servers that Bob has NOT logged into, and the number of times anyone other than Bob logged into them (which from your original search is what I assume you're after?

Hope this helps 🙂

REF:

Highlighted

Re: Find which hosts a user has not logged in to

SplunkTrust
SplunkTrust

I think I may have fudged up deleting my comment - gsd basically said "that should work".

Additionally, I think you should integrate the subsearch filter into the main search to avoid loading the entire set of data instead of just loading the hosts bob hasn't logged in to. As another speedup you can probably run the outer search using the metadata command, but that's for when you pass out of the Splunk Newbie phase 🙂

0 Karma
Highlighted

Re: Find which hosts a user has not logged in to

Ultra Champion

here is the metadata one that shows all hosts, and the user in a column

| metadata type=hosts index=*
| JOIN type=outer host [
search sourcetype="WMI:UserAccounts" user="Bob"
| table host user
| dedup host ]

Highlighted

Re: Find which hosts a user has not logged in to

New Member

R. Turk,

Thanks for the quick response. I was able to get the answer this way, but only partially (the subsearch auto-finalized after 60 seconds). I've been lurking the splkunk>answers site and it seems like there is an with this.

martin_muller and yannK,

I'll try the metadata search and let you know if that worked.

0 Karma
Highlighted

Re: Find which hosts a user has not logged in to

Builder

Hi GSD - check the answer from @martin_meuller as his is a more efficient way of doing the same thing (and it removes the timeout issue).

0 Karma
Highlighted

Re: Find which hosts a user has not logged in to

New Member

R.Turk - I'll check martin_mueller's answer. Thanks for your help!

0 Karma
Highlighted

Re: Find which hosts a user has not logged in to

Champion

Welcome, In terms of performance sub searches consume two CPUs ( one for the main search and one for the subsearch). Subsearches have a significantly lower returned event limit. Performing using field extractions in your base search is costlier as Splunk has to apply extracts immediately. I recommend specifying index if possible. Consider using fields before performing any stats or eval this will improve search performance as Splunk only has to work with result necessary for final output.


#if you are looking just for bob
index=myindex sourcetype="WMI:UserAccounts" Bob | dedup host | fields host

The easiest way without the expense of a sub search.


#if you are trying to find where bob has not been
index=myindex sourcetype="WMI:UserAccounts" NOT Bob | dedup host | fields host
or
index=myindex sourcetype="WMI:UserAccounts" | fields host, user | where NOT user="Bob"

Take the time to Read Splunk "SEARCH PROCESSING LANGUAGE (SPL)
PRIMER AND COOKBOOK" its a free PDF download.

Hope this helps or gets you started. Dont forget to vote up and accept answers.
Cheers,

Highlighted

Re: Find which hosts a user has not logged in to

Builder

Hi Bmacias84 - the issue with this search is that all it's is doing is filtering out entiries where Bob wasn't the user, so if you have entries when "Bob" and "Bill" have both logged into "server1", the search will still display "server1". Admittedly the User will also be displayed, but OP asked for a list of servers that Bob hasn't logged into. I don't think this can be done without using a sub-search 🙂

0 Karma
Highlighted

Re: Find which hosts a user has not logged in to

New Member

hi R.Turk - Thanks for the quick response. I was able to get the answer this way, but only partially (the subsearch auto-finalized after 60 seconds). I've been lurking the splkunk>answers site and it seems like there is an issue with subsearches timing out.

0 Karma