topic Re: Help with creating an index search based on the _time value (beginner) in Splunk Search

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

Bassik — Thu, 09 Jul 2020 02:05:36 GMT

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:

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.

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

renjith_nair — Thu, 09 Jul 2020 06:36:56 GMT

@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

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

Bassik — Fri, 10 Jul 2020 01:34:05 GMT

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.

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

renjith_nair — Fri, 10 Jul 2020 15:27:00 GMT

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

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

Bassik — Fri, 10 Jul 2020 15:34:24 GMT

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)

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.

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:

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 )

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

Bassik — Fri, 10 Jul 2020 15:47:49 GMT

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)

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

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

renjith_nair — Sat, 11 Jul 2020 04:45:38 GMT

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

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

Bassik — Sat, 11 Jul 2020 05:34:06 GMT

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

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

renjith_nair — Sat, 11 Jul 2020 11:49:44 GMT

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.

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

Bassik — Sat, 11 Jul 2020 12:14:54 GMT

Hi again,

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

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

renjith_nair — Sun, 12 Jul 2020 11:56:19 GMT

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

Inner Data Set with action=UserAuthenticationQuery (2 records)

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

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

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

Bassik — Mon, 13 Jul 2020 01:36:59 GMT

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?

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

renjith_nair — Mon, 13 Jul 2020 05:09:03 GMT

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

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

Bassik — Mon, 13 Jul 2020 05:22:47 GMT

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

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.

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

renjith_nair — Mon, 13 Jul 2020 05:32:21 GMT

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"

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

Bassik — Mon, 13 Jul 2020 06:10:39 GMT

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.

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

Bassik — Mon, 13 Jul 2020 22:33:10 GMT

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.

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.

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.

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

renjith_nair — Tue, 14 Jul 2020 02:50:22 GMT

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

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

Bassik — Tue, 14 Jul 2020 03:14:04 GMT

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?

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

renjith_nair — Tue, 14 Jul 2020 03:17:29 GMT

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?