topic Re: How can I create a query where I can show whether a user has or hasn't logged in within the last 90 days and create a visualization of my results? in Getting Data In

How can I create a query where I can show whether a user has or hasn't logged in within the last 90 days and create a visualization of my results?

willadams — Tue, 20 Feb 2018 00:58:14 GMT

Hi,

I am trying to write a search for figuring out whether a user has logged in within the last 90 days. At the end of the process, I want to show some visualization showing the number of users that have logged in in the last 90 days and the number of users that haven't logged in in the last 90 days.

The log file is a PowerShell generated CSV file (doing a get-aduser with various selects). One of those variables is "LastLogonDate". The CSV inputs this as "19-Feb-18 2:28:46 PM" for example. I have extracted from this the date so have an extracted field called "DateLastLogon" that shows as "19-Feb-18".

My calculation is meant to look at this extracted field and compare it to the current time (timenow) and do a count of the number of users where DateLastLogon is greater than 90 days and a second search where the DateLastLogon is less than 90 days.

To start off with I tried this

index="myindex"
| eval lastlogontime=strftime(DateLastLogon,"%d-%b-%y")
| eval timenow=strftime(now(),"%d-%b-%y")
| eval timediff=(timenow-lastlogontime)
| table timediff

I also tried

index="myindex"
| eval lastlogontime=strftime(DateLastLogon,"%d-%m-%y")
| eval timenow=strftime(now(),"%d-%m-%y")
| eval timediff=(timenow-lastlogontime)
| table timediff

I am now stuck and require assistance as while both pieces of code will run and not error, I am getting "no results" found

Re: How can I create a query where I can show whether a user has or hasn't logged in within the last 90 days and create a visualization of my results?

sduff_splunk — Tue, 20 Feb 2018 03:46:58 GMT

I suggest you try re-running your searches and making them either the last 24 hours, or perhaps even All Time. Are you certain that there is data in the "myindex" that you are searching, for the right time period? Remove the line "| table timediff", what fields are being calculated (lastlogontime, timenow, timediff).

Re: How can I create a query where I can show whether a user has or hasn't logged in within the last 90 days and create a visualization of my results?

willadams — Tue, 20 Feb 2018 04:02:56 GMT

I get the same result whether it's "24 hours" or "All Time". There is data in the log that is produced. For example:

"CN=User1", "True", "19-Feb-17 09:55:05 AM", "29-Jan-2016 8:35:01 AM", "False", "False"

This log is just "username", "state", "lastlogon", "passwordlastset", othervariable1, othervariable2

I extracted fields from this so that lastlogon is just showing "19-Feb-17"

So my extract field for the information I am interested in is "19-Feb-17". I am not using the time column from the index input.

Re: How can I create a query where I can show whether a user has or hasn't logged in within the last 90 days and create a visualization of my results?

HiroshiSatoh — Tue, 20 Feb 2018 05:01:41 GMT

For example, it is calculated in this way.

|noop|stats count as text|eval text="19-Feb-17 09:55:05 AM"
|eval lastlogontime=strptime(text,"%d-%b-%y")
|eval timediff_s=now()-lastlogontime
|eval timediff_day=floor(timediff_s/60/60/24)