Splunk Search

Get Distinct Count of Success & Total based on UserID

SplotchySplunkS
Engager

I'm trying to make one search that will accomplish the following:

Total Login Attempts: DC(USERID) WHERE ACTIVITY = "LOGIN"

Total Login Successes: DC(USERID) WHERE ACTIVITY = "LOGIN" AND RESULT="SUCCESS"
Total Logoff Attempts: DC(USERID) WHERE ACTIVITY = "LOGOFF"
Total Logoff Successes: DC(USERID) WHERE ACTIVITY = "LOGOFF" AND RESULT=SUCCESS"

All of the necessary data can be gathered in one search, Im just having trouble splitting it with chaining conditions. Is there a way to accomplish this by only running the search once?

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your current search giving all LOGIN/LOGOFF events
| eval type=split(ACTIVITY."#".ACTIVITY."-".RESULT,"#")
| eval temp=1 | chart dc(host) over temp by type
| table "LOGIN" "LOGIN-SUCCESS" "LOGOFF" "LOGOFF-SUCCESS"
| rename LOGIN as "Total Login Attempts" "LOGIN-SUCCESS" as "Total Login Successes" "LOGOFF" as "Total Logoff Attempts" "LOGOFF-SUCCESS" as "Total Logoff Successes"

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your current search giving all LOGIN/LOGOFF events
| eval type=split(ACTIVITY."#".ACTIVITY."-".RESULT,"#")
| eval temp=1 | chart dc(host) over temp by type
| table "LOGIN" "LOGIN-SUCCESS" "LOGOFF" "LOGOFF-SUCCESS"
| rename LOGIN as "Total Login Attempts" "LOGIN-SUCCESS" as "Total Login Successes" "LOGOFF" as "Total Logoff Attempts" "LOGOFF-SUCCESS" as "Total Logoff Successes"

SplotchySplunkS
Engager

Is there an easy way to add percentages for both login and logoff? Wouldn't it be something like:
|eval percent = (LOGIN-SUCCESS/LOGIN)*100

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Yes... just do it before the rename command and include field names with hyphen in single quotes.

|eval percent = ('LOGIN-SUCCESS'/LOGIN)*100
0 Karma

SplotchySplunkS
Engager

I actually just noticed that LOGIN-FAILURE from line 3 isn't loading any results (even though there are some). There is a FAILURE_REASON field that is added in this specific scenario. Would this be what is causing the problem?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

That will be correct. If for failed logins, you don't get an event with ACTIVITY="LOGIN" and RESULT="FAILURE", they won't be counted here.

0 Karma

SplotchySplunkS
Engager

There was an ID difference in the logs. Got it all figured out. Thanks for the help!!

0 Karma

mhassan24
Explorer

Hi somesoni2,

Would you mind explaing how the following works? Would it result in Activity being repeated twice?

| eval type=split(ACTIVITY."#".ACTIVITY."-".RESULT,"#")

Best,
-Hassan

0 Karma

somesoni2
SplunkTrust
SplunkTrust

For every event, it creates a multivalued field (output of split command), with values as ACTIVITY and ACTIVITY-RESULT. (e.g. for each LOGIN event, the field type will have values either LOGIN and LOGIN-SUCCESS OR LOGIN and LOGIN-FAILURE). They since it's a multivalued field, when you do aggregation (chart/stats etc) by a multivalued field, each aggregation will be performed for each value of the multivalued field. E.g. dc(host) by type will calculate dc(host) for LOGIN and dc(host) for LOGIN-SUCCESS as well, so aggregation on an event is done twice. Generally counting twice is an issue but here it's the requirement, as there is an overlap between LOGIN and LOGIN-SUCCESS type of events.

0 Karma

mhassan24
Explorer

Thanks for the detailed explanation!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Does the RESULT field is available in all events? If yes what all values it can hold? SUCCESS/FAILURE/Any other or default value?

0 Karma

SplotchySplunkS
Engager

Yes, both the RESULT field and the ACTIVITY field are available for all events. The only possible results are SUCCESS or FAILURE.

0 Karma

mhassan24
Explorer

Maybe something like:

 | multisearch [search ACTIVITY = "LOGIN" | eval type="search1"] [ACTIVITY = "LOGIN" AND RESULT="SUCCESS"| eval type="search2"] [ACTIVITY = "LOGOFF"| eval type="search3"] [ACTIVITY = "LOGOFF" AND RESULT=SUCCESS"| eval type="search4"]
 | chart dc(USERID) over ACTIVITY by type
0 Karma

rjthibod
Champion

I can only give a partial response without seeing the beginnig of your search to show where you get the data from.

You can use chart and untable to accomplish this is you are not trying to keep other fields

| chart dc(USERID) by ACTIVITY RESULT
| untable ACTIVITY RESULT dc(USERID)

If you need to keep other fields, then you need to eval a new field that indicates the conditions and then run stats or eventstats with dc(USERID) against the field name you just created.

0 Karma

sivapuvvada
Path Finder

you can use base search in so that the search will get the results from Indexer one time and you can use the results where you want .

Please refer below link

http://docs.splunk.com/Documentation/Splunk/6.2.5/Viz/Savedsearches#Post-process_examples

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 ...