Splunk Search

Find which hosts a user has not logged in to

gsd
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
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Thinking about this a bit more, you should be able to do what the OP needs without a subsearch:

sourcetype="WMI:UserAccounts" | stats count(eval(User="Bob")) as bobcount by host | where bobcount=0

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

Thinking about this a bit more, you should be able to do what the OP needs without a subsearch:

sourcetype="WMI:UserAccounts" | stats count(eval(User="Bob")) as bobcount by host | where bobcount=0

gsd
New Member

marti_mueller - this query worked! Thanks for your help!

0 Karma

gsd
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

bmacias84
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,

rturk
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

rturk
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:

gsd
New Member

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

0 Karma

rturk
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

gsd
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

yannK
Splunk Employee
Splunk Employee

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 ]

martin_mueller
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...