Splunk Search

Search to display for list of applications to which users never logged in

rvenkata
Explorer

Hello All,

I would like to list down the applications where users have never logged in. I have a input.csv file with the list of applications and I was able to write a search to list down the applications by login count. Now I want to list the applications where users have logged in (Login Count=0). My query to list all the apps and with login count is as follows: 

sourcetype="aduit" success NOT AUTHN_ATTEMPT
| lookup app_lookup.csv Connection_ID as connectionid OUTPUT App_Name
| stats count(App_Name) as "Number of Successful Logins" by App_Name
| sort - "Number of Successful Logins"  

How can I get list of apps with 0 logins?

Thanks,
Rakesh Venkata

Labels (3)

rvenkata
Explorer

Hello @Ron_Naken @gkanapathy @lguinn2 

 

I see that you guys have responded to similar posts previously. Can you please shed your valuable inputs?

Thanks in advance. 

 

0 Karma

hc_joycechen
Explorer

Try:

| inputlookup app_lookup.csv
| join type=left App_Name
[ search sourcetype="aduit" success NOT AUTHN_ATTEMPT
| lookup app_lookup.csv Connection_ID as connectionid OUTPUT App_Name
| stats count(App_Name) as "Number of Successful Logins" by App_Name
]
| where isnull("Number of Successful Logins")

hc_joycechen
Explorer

app_lookup.csv  OR App_lookup.csv ??

It's different.

| inputlookup App_lookup.csv
| join type=left App_Name
[ search sourcetype="aduit" success NOT AUTHN_ATTEMPT
| lookup App_lookup.csv Connection_ID as connectionid OUTPUT App_Name
| stats count(App_Name) as "Number of Successful Logins" by App_Name
]
| where isnull("Number of Successful Logins")

0 Karma

rvenkata
Explorer

Hello @hc_joycechen 

Thanks for your response. 

I tried your solution it is not yielding any results. I modified your suggestion to following:

| inputlookup App_lookup.csv

| join type=left Connection_ID

[ search sourcetype="aduit" tid success NOT AUTHN_ATTEMPT

| lookup App_lookup.csv Connection_ID AS connectionid OUTPUT App_Name

| stats count(App_Name) as LoginCount by App_Name

]

| where isnull(LoginCount)

 

So my csv has two columns Connection_ID & App_Name and connectionid is a field in my events. My above solution results all apps in the csv but not apps which users never logged in. Following is a sample of my csv.

 

Connection_IDApp_Name
app1.domain.comApp1
app2.domain.comApp2

 

Can you please help me our here?

 

Thanks in advance. 

Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...