Splunk Search
Highlighted

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Path Finder

Hi again,

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

0 Karma