Splunk Search

Help with creating an index search based on the _time value (beginner)

Path Finder

Beginner here, I'm trying to run a search on unique logins for a web-based application. The current logs, however, do not indicate the information I need to be able to count which app the user logged into. 

It may be easier to illustrate the search:

Annotation 2020-07-09 120311.png

What I am trying to archive is on the _time value all those events (hidden) are triggered at the exact same time. I want to use that value as a unique ID to evaluate all the events that happened at that time as a group. 

The information I require is from a_app

Could some explain to me a way to archive this?

I guess in summary if the UserAuthicationQuery had an actual log that identified what the user was logging into it would then work but the a_app for this process is done in a central location and not associated with the actual app the user is entering.

Labels (1)
0 Karma

SplunkTrust
SplunkTrust

@Bassik,

As you said , it might not be accurate. However, if you want to get the app list for a user with time as a common factor (seconds' precision) , try this

"your search"
|eval timeIdentifier=strftime(_time,"%Y-%m-%d-%H-%M-%S")
|stats values(a_app)  as appList by timeIdentifier,cs_username

appList should have the list of apps. We converted time to string just to make sure that we take until seconds precision. You may user _time directly as well

0 Karma

Path Finder

Hi Renjith, thanks for assisting. This did help however, I still cannot collectively gather all other events that the _time variable is when associated with the UserAuthenticationQuery. 

I need the "timeIdentifier" to be the factor based on my search. I think there maybe a need to do a nested query here. Because now that I have "timeIdentifier", my next search would be something on the lines of:

Search : where _time=timeIdentifier.

If I described it easier to see below, this is a mock of a report whereby the "UserAuthenticationQuery" will always have App1 as the a_app however, it is not the actual app the user is logging into. The a_action below is the next identifier that holds the app the user logins into. 

I need all the apps associated with the "timeIdentifier" but triggered by the "UserAuthenticationQuery" _time.

 

Annotation 2020-311.png

 

0 Karma

SplunkTrust
SplunkTrust

Subsearch should work here. 

Try this and test against your data

 

index="test" sourcetype="apps"
    [search index="test" sourcetype="apps" a_action="UserAuthenticationQuery"| fields timeIdentifier]

 

replace with your index and other search terms

0 Karma

Path Finder

Ok, thanks again. The second approach gave me some more information but unfortunately did not get me the results needed. I got multiple times now.

I kind of only need all the a_app values times where the a_action="UserAuthenticationQuery" and then use that time to populate all other events (a_app, dedup cs_username)

 
 

Annotation 2020-311.png

So below would be the results I would be after. Keep in mind that I would have more than just 2 events... I could have hundreds but the key here is that the sequential event would contain the correct app the user logged into, which would be App2.

Also note I cannot include "UserAuthenticationQuery" in my search because then it eliminates the search results needed to find that sequential event.

Annotation 2020.png

The key unique ID here is only the _time field. Thus I need this to pull all the events that happen at that exact time. I am basing my assumption that when a user logs in this is all done within milliseconds.

The results I got from your second approach was:

Annotation list.png

And you first approach I got nothing...

 

Now I was assuming that we are still keeping your original suggestions to the search right?

(eval timeIdentifier=strftime(_time,"%Y-%m-%d:%H:%M:%S") |stats values(a_app) as appList by timeIdentifier,cs_username )

0 Karma

Path Finder

So just a thought, to make it understandable. I could do a search like this:

index=iis_prod sourcetype=iis site=AWS  a_action=UserAuthenticationQuery | eval timeIdentifier=strftime(_time,"%Y-%m-%d:%H:%M:%S") |stats values(a_app) as appList by timeIdentifier,cs_username

And I'd get these results (mock)

Bassik_2-1594395843338.png

But the actual fact is that I don't want App1 because I know that that isn't the app the user logged into. App1 one is gateway to the forwarding app. But because I can capture that time the user logged into App1, I know that if I could find all associated events at that UserAuthenticationQuery time I could identify the app.

Hope that helps

 

 

 

0 Karma

SplunkTrust
SplunkTrust

Do you have the App1 and the forwarding apps in the same index?

Below should pick up the _time from the events which matches action=UserAuthenticationQuery and apply that to the outer search.

index=iis_prod sourcetype=iis site=AWS  
[search index=iis_prod sourcetype=iis site=AWS a_action=UserAuthenticationQuery|fields _time]

second approach will not work if you have multiple UserAuthenticationQuery events. Let me know if you see any events for the above search

0 Karma

Path Finder

Yes, all the apps are in the same IIS index but not on the UserAuthenticationQuery event.

 

I get an error on that search and nothing if i remove the pipe

 

Bassik_0-1594445570724.png

 

0 Karma

SplunkTrust
SplunkTrust

you dont need a pipe before second search.

Subsearch creates the list of items in the subsearch as OR'ed condition and apply to the main search.

 

0 Karma

Path Finder

Hi again,

Thanks but as I said removing the pipe I get no results

0 Karma

SplunkTrust
SplunkTrust

@Bassik ,

Just to be on the same page, here is a sample data created and the respective search. Please try this and let me know where is the issue

Main Data Set (8 records)

|makeresults|eval a_app="App1 App2 App2 App2 App1 App3 App3 App3"|makemv a_app|mvexpand a_app
|appendcols [|makeresults|eval a_action="UserAuthenticationQuery action1 action2 action3 UserAuthenticationQuery action1 action2 action3"|makemv a_action|mvexpand a_action]
|appendcols [|makeresults|eval timeIdentifier="2020-06-22:15:22:24 2020-06-22:15:22:24 2020-06-22:15:22:25 2020-06-22:15:22:26 2020-06-22:16:12:22 2020-06-22:16:12:22 2020-06-22:16:12:25 2020-06-22:16:12:26"|makemv timeIdentifier|mvexpand timeIdentifier ]
|appendcols [|makeresults|eval cs_username="111111 111111 111111 111111 222222 222222 222222 222222" | makemv cs_username|mvexpand cs_username]
| eval _time=strptime(timeIdentifier,"%Y-%m-%d:%H:%M:%S")

 

Inner Data Set with action=UserAuthenticationQuery (2 records)

[|makeresults|eval a_app="App1 App2 App2 App2 App1 App3 App3 App3"|makemv a_app|mvexpand a_app
|appendcols [|makeresults|eval a_action="UserAuthenticationQuery action1 action2 action3 UserAuthenticationQuery action1 action2 action3"|makemv a_action|mvexpand a_action]
|appendcols [|makeresults|eval timeIdentifier="2020-06-22:15:22:24 2020-06-22:15:22:24 2020-06-22:15:22:25 2020-06-22:15:22:26 2020-06-22:16:12:22 2020-06-22:16:12:22 2020-06-22:16:12:25 2020-06-22:16:12:26"|makemv timeIdentifier|mvexpand timeIdentifier ]
|appendcols [|makeresults|eval cs_username="111111 111111 111111 111111 222222 222222 222222 222222" | makemv cs_username|mvexpand cs_username]
| eval _time=strptime(timeIdentifier,"%Y-%m-%d:%H:%M:%S")
| where a_action="UserAuthenticationQuery"
    | fields timeIdentifier ]

 

Combined search to find all events from the main data set where the timeIdentifier same as the timeIdentifiers of UserAuthenticationQuery (4 records)

|makeresults|eval a_app="App1 App2 App2 App2 App1 App3 App3 App3"|makemv a_app|mvexpand a_app
|appendcols [|makeresults|eval a_action="UserAuthenticationQuery action1 action2 action3 UserAuthenticationQuery action1 action2 action3"|makemv a_action|mvexpand a_action]
|appendcols [|makeresults|eval timeIdentifier="2020-06-22:15:22:24 2020-06-22:15:22:24 2020-06-22:15:22:25 2020-06-22:15:22:26 2020-06-22:16:12:22 2020-06-22:16:12:22 2020-06-22:16:12:25 2020-06-22:16:12:26"|makemv timeIdentifier|mvexpand timeIdentifier ]
|appendcols [|makeresults|eval cs_username="111111 111111 111111 111111 222222 222222 222222 222222" | makemv cs_username|mvexpand cs_username]
| eval _time=strptime(timeIdentifier,"%Y-%m-%d:%H:%M:%S")
| search 
    [|makeresults|eval a_app="App1 App2 App2 App2 App1 App3 App3 App3"|makemv a_app|mvexpand a_app
|appendcols [|makeresults|eval a_action="UserAuthenticationQuery action1 action2 action3 UserAuthenticationQuery action1 action2 action3"|makemv a_action|mvexpand a_action]
|appendcols [|makeresults|eval timeIdentifier="2020-06-22:15:22:24 2020-06-22:15:22:24 2020-06-22:15:22:25 2020-06-22:15:22:26 2020-06-22:16:12:22 2020-06-22:16:12:22 2020-06-22:16:12:25 2020-06-22:16:12:26"|makemv timeIdentifier|mvexpand timeIdentifier ]
|appendcols [|makeresults|eval cs_username="111111 111111 111111 111111 222222 222222 222222 222222" | makemv cs_username|mvexpand cs_username]
| eval _time=strptime(timeIdentifier,"%Y-%m-%d:%H:%M:%S")
| where a_action="UserAuthenticationQuery"
    | fields timeIdentifier ]

  

If this is what you expect, you can apply the same in your dataset regardless it's same index or different index

Another approach combining both in a single search  (added an extra event for multiple values of UserAuth

|makeresults|eval a_app="App1 App2 App2 App2 App1 App1 App3 App3 App3"|makemv a_app|mvexpand a_app
|appendcols [|makeresults|eval a_action="UserAuthenticationQuery action1 action2 action3 UserAuthenticationQuery UserAuthenticationQuery action1 action2 action3"|makemv a_action|mvexpand a_action]
|appendcols [|makeresults|eval timeIdentifier="2020-06-22:15:22:24 2020-06-22:15:22:24 2020-06-22:15:22:25 2020-06-22:15:22:26 2020-06-22:15:22:26 2020-06-22:16:12:22 2020-06-22:16:12:22 2020-06-22:16:12:25 2020-06-22:16:12:26"|makemv timeIdentifier|mvexpand timeIdentifier ]
|appendcols [|makeresults|eval cs_username="111111 111111 111111 111111 111111 222222 222222 222222 222222" | makemv cs_username|mvexpand cs_username]
| eval _time=strptime(timeIdentifier,"%Y-%m-%d:%H:%M:%S")
| eventstats values(eval(if(a_action=="UserAuthenticationQuery",timeIdentifier,null()))) as authTime by cs_username
| where isnotnull(mvfind(authTime,timeIdentifier))

Path Finder

Ok sweet, this one gives me (sample) results that I would like. From that, I would like to be able to list stats on these results. But this is great. Thanks.

How can I apply this to the actual data set without the |makeresults|?

Obviously there will be a numerous amount of usernames so I assume I'll be removing most of the |makeresults| with my own index. I just don't know what to include?

Bassik_0-1594602341975.png

 

0 Karma

SplunkTrust
SplunkTrust

You should replace the main data set (until the |search) with your actual events and the second data set with (after |search [ this one]] with the events for UserAuthenticationQuery

for e.g

If app details and the UserAuthentication are in different index

index="your index which app details" "other search terms with app details"|eval timeIdentifier=strftime(_time,"%Y-%m-%d:%H:%M:%S")
|search [index=iis_prod sourcetype=iis site=AWS  a_action=UserAuthenticationQuery | eval timeIdentifier=strftime(_time,"%Y-%m-%d:%H:%M:%S")|fields timeIdentifier ]

 

If you have everything in the same index

index=iis_prod sourcetype=iis site=AWS
| eval _time=strptime(timeIdentifier,"%Y-%m-%d:%H:%M:%S")
| eventstats values(eval(if(a_action=="UserAuthenticationQuery",timeIdentifier,null()))) as authTime by cs_username
| where isnotnull(mvfind(authTime,timeIdentifier))

 

0 Karma

Path Finder

Yeah, I actually did this while I was waiting for your response and now I get no results whatsoever.

Bassik_0-1594617590537.png

If I remove your 

| eval _time=strptime(timeIdentifier,"%Y-%m-%d:%H:%M:%S")
| eventstats values(eval(if(a_action=="UserAuthenticationQuery",timeIdentifier,null()))) as authTime by cs_username
| where isnotnull(mvfind(authTime,timeIdentifier))

I get results. 

0 Karma

SplunkTrust
SplunkTrust

That's because you don't have any matching records for the "where" condition

Try removing "where" condition and execute the search.

Check if you have a matching entry of timeIdentifier in authTime (multi value). If authTime is empty, there are no events which satisfies the condition a_action="UserAuthenticationQuery"

 

0 Karma

Path Finder

Ok, now I get results however not in a table format as like how you sampled. I get 2,994 events. 

I do not see how :

| where isnotnull(mvfind(authTime,timeIdentifier)) would produce no results. There should not be any NULL values in that field.

0 Karma

Path Finder

Hi, so this is the closest I can get but I know that this is not what I want in its entirety. If I apply the search for a_action="UserAuthenticationQuery" then I only get the app that is via the authorisation gateway app (which is not the correct app). If I can somehow append the additional a_app event that follows the UserAuthenticationQuery event.

Bassik_0-1594679397052.png

So essentially I know for a fact that in the time frame I have specified there is a total of 13 users that have logged in because the UserAuthenticationQuery event is an identifier.

This is another example of how I would like it (see below) you can see that under appList there are multiple a_apps listed. Take user 00000045. At that time of 2020-06-22:15:22:24 we can see that the user logged in and the following event also has the same time. This event holds the actual app the user logged into. However, this is picking up every other event which has an app in the a_app field and not limiting the results to only the UserAuthenticationQuery and following event. These results give me 2,730. I only want 13 users.

Bassik_1-1594679424554.png

I don't know if I'm making it confusing. I do apologise as it makes sense in my head what I want but cannot provide the context or syntax to construct it.

0 Karma

SplunkTrust
SplunkTrust

No problem, if you are looking only for the next action and not all of them, replace eventstats part with the below and try

 

| eventstats values(eval(if(a_action=="UserAuthenticationQuery",timeIdentifier,null()))) as authTime by cs_username|streamstats count reset_on_change=true by cs_username,timeIdentifier
| where isnotnull(mvfind(authTime,timeIdentifier)) AND count < 3
0 Karma

Path Finder

ok thanks however I get nothing when I put:

| where isnotnull(mvfind(authTime,timeIdentifier)) AND count < 3

And if I remove this I get the same 2,730 results again

May I ask what exactly this is requesting?

 

is it where "is not nothing in fields authTime,timeIdentifier?

0 Karma

SplunkTrust
SplunkTrust

This is based on the assumption that UserAuthenticationQuery and other events are in same index.

By using

| eventstats values(eval(if(a_action=="UserAuthenticationQuery",timeIdentifier,null()))) as authTime by cs_username

We get a new Field authTime which is equal to the timestamp of UserAuthenticationQuery

|streamstats count reset_on_change=true by cs_username,timeIdentifier

will count the events in the order

| where isnotnull(mvfind(authTime,timeIdentifier)) AND count < 3

will filter those events which have authTime (timestamp of UserAuthenticationQuery) equal to their timestap and count < 3 will only include the UserAuthenticationQuery and only one next event.

If this does not work for you, can you change the initial dummy dataset I provided (with makeresults) and make them similar to your actual events and then also give the expected output from them?

|makeresults|eval a_app="App1 App2 App2 App2 App1 App3 App3 App3"|makemv a_app|mvexpand a_app
|appendcols [|makeresults|eval a_action="UserAuthenticationQuery action1 action2 action3 UserAuthenticationQuery action1 action2 action3"|makemv a_action|mvexpand a_action]
|appendcols [|makeresults|eval timeIdentifier="2020-06-22:15:22:24 2020-06-22:15:22:24 2020-06-22:15:22:25 2020-06-22:15:22:26 2020-06-22:16:12:22 2020-06-22:16:12:22 2020-06-22:16:12:25 2020-06-22:16:12:26"|makemv timeIdentifier|mvexpand timeIdentifier ]
|appendcols [|makeresults|eval cs_username="111111 111111 111111 111111 222222 222222 222222 222222" | makemv cs_username|mvexpand cs_username]
| eval _time=strptime(timeIdentifier,"%Y-%m-%d:%H:%M:%S")
0 Karma

Path Finder

To quote:

This is based on the assumption that UserAuthenticationQuery and other events are in the same index?

The only key value is the timestamp, this has to be the trigger point on all other actions captured at this time.

IF UserAuthenticationQuery=2020-06-22:15:22:24 then give me the apps under a_app at this timestamp.

----------

We get a new Field authTime which is equal to the timestamp of UserAuthenticationQuery

The results I get on this is everything is at 1 count. However, I get what you are doing here, you are creating a new field with the same timestamp as the UserAuthenticationQuery event.

-----------

| where isnotnull(mvfind(authTime,timeIdentifier)) AND count < 3

will filter those events which have authTime (timestamp of UserAuthenticationQuery) equal to their timestamp and count < 3 will only include the UserAuthenticationQuery and only one next event.

This does not seem to work. I always get no results when I enter this in.

-----------

your makeresults work but from what point do I stop what your makeresults and replace with my search?

Do I just remove your |makeresults| with actual data or remove that part and put in the field names?

Many thanks for assisting me...:)

0 Karma