Newb trying to get unique count of users... failing miserably.

[Adam_Marx]

I'm trying to get a unique list of users accessing Essbase servers and databases daily/monthly/qtrly but am struggling with the querying process and the results.

I've tried the following searches but only get a list of "all" users and never unique users.

index="essbaseDB" "set active on database" | eval customer="User \[(.*?)\]" | timechart span=1d dc(customer) as distinct_users

index="essbaseDB" "set active on database" | timechart span=1d count as count_user by "set active on database" 

The second example got me a little closer by day but still not unique.

The record looks similar to the following:

[Sun Aug 6 05:10:16 2017]Local/essbaseDB///140378835617536/Info(1013210)
User [JSmith@domain] set active on database [essbaseDB]

Any suggestions or ideas are welcome.

Adam,

[Richfez · Accepted solution]

Your first example should be pretty close but you need to change it to a rex.

index="essbaseDB" "set active on database" | rex "User \[(?<customer>[^\]]*)\]" | timechart span=1d dc(customer) as distinct_users

That should get you a list of distinct_users. You'll also see a field on the left in "interesting fields" that will be called customer.

Happy Splunking,
Rich

[Adam_Marx]

Many thanks for the additional insight and your expertise it will go a long way in getting me more involved with splunk and its many uses.

Happy Thanksgiving to you and yours!

Adam,

[Adam_Marx]

Thanks for the post Rich that result is significantly better than my attempts, many thanks!

May I ask a follow up or two, I can post individually if that's the preferred method...

• Why the need to provide a variable ( I assume) for the rex command? I tested the regex and it successfully parsed the record and it also created a new "field".

• Assuming I wanted to total for all the individual DB's could I add ...| rex database="database [(.*?)]" and somehow group by DB as well? potentially there could be dozens of DB's.

• Assuming I wanted to see actual user names on a qtrly basis, etc. is that also a possibility?

Thanks,

[Richfez]

Sure, to practically every question you had. 🙂 Taking them in mostly order...

The rex command is one of several ways to extract "fields" out of you data. You can use rex right in your SPL (for testing, sometimes for one-off things). You could convert most rex's to a REPORT-X or EXTRACT-X in your props.conf, which would make them automatically apply to your data and automatically extract these fields from the raw data. You could also use the field extractor (look at the bottom of the interesting fields for a link to "+Extract More Fields" ).

If by "create a variable" you mean adding/creating the field, in this case you wanted to "do things" but only to that piece of data so the field creates a handle by which you can refer to that little piece. There's a special way to build a variable like that in rex and regex - you enclose the variable name in <> brackets inside the extraction. You can try out the tutorial at somewhere like regexone.com to learn more.

So, for the individual DBs - Absolutely! You are completely on the right track, but you didn't use the 101010 "code" button to paste that code so some of it got eaten. That's OK though, you probably had something like this:

...| rex "database \[(?<database>[^\]]*)\]"

That should do it. But why stop there? You can do both of these in one rex -

...| rex "User \[(?<customer>[^\]]*)\].*database \[(?<database>[^\]]*)\]"

Now that you have all those fields - customer and database - you can do all sorts of things.

Lastly, if you want quarterly or whatever numbers, that's just adjusting your timeframe either in the base search with earliest=-90d (for the last 90 days) or via the time drop-down in the upper right, then running a ... | stats count by customer or dc(customer) or any other of the stats functions.

Some miscellaneous things you can do - Assume ALL of these start with your index=... and the rex.

1) Distinct count of users:

...| stats count as distinct_users by customer

2) Timechart of the number of users

...| timechart dc(customer) as distinct_users

3) Timechart of DB "touches" (I don't know exactly what these mean, you'll have to think on this yourself and decide if it's good info or not)

... |timechart count by database

4) Count users by databases
... | stats count customer by database

At this point, the sky is the limit.

Even - try this over, say, the past 30 days (I've hardcoded a -30d in there, so remove that if you want to play with other settings) - change to the visualization tab once you get it to run data!

index="essbaseDB" "set active on database" earliest=-30d 
| rex "User \[(?<customer>[^\]]*)\].*database \[(?<database>[^\]]*)\]"
| timechart dc(customer) AS Distinct_Customers 
| predict Distinct_Customers 

Hopefully that gets you started! BTW, links for docs on stats, timechart, and predict. There is more available on the left side of any of those pages, so feel free to click them!

If you want more, I'd suggest starting with Splunk Fundamentals I - it's a free, web-based class that'll take you perhaps a day to complete but will give you a decent grounding in some of the possibilities.