topic Re: Search to display for list of applications to which users never logged in in Splunk Search

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

rvenkata — Mon, 19 Oct 2020 19:10:40 GMT

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

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

hc_joycechen — Tue, 20 Oct 2020 07:35:34 GMT

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")

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

rvenkata — Tue, 20 Oct 2020 17:30:53 GMT

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_ID	App_Name
app1.domain.com	App1
app2.domain.com	App2

Can you please help me our here?

Thanks in advance.

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

rvenkata — Tue, 20 Oct 2020 17:34:16 GMT

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.

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

hc_joycechen — Wed, 21 Oct 2020 01:29:26 GMT

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")