Think I need to define the Login into a column and then search the count based on that whereby _time and Logins >200 are. The results I am getting is all the time events  ... View more

Hi this kind of works... but I am getting thousands of events where it should be only restricted to cs_username. This is my base search:   index=iis_prod sourcetype=iis site=AWS cs_Referer=*Login*  sc_status=200 OR 302 cs_username=* cs_User_Agent!=Varnish_Health_Probe ... View more

Ok so it all good then?   Big thanks buddy ... View more

Legend, this worked great. Just one concern I have. The addition of this line, below, is only looking for Chrome, If Internet Explorer was in the Browser field it would list it as "Other"?  | eval Browser=case(!isnull(Browser), Browser, match(BrowserInfo, "^Chrome"), "Chrome", 1==1,"Other") | rex field=cs_User_Agent "(?<BrowserInfo>(\Chrome).[^\+;]*)" | rex field=BrowserInfo "[:\/ ](?<Version>.*)" | eval Browser=case(match(BrowserInfo, "^Chrome"), "Chrome", 1==1,"Other") | eval Browser=if(!isnull(Version),Browser." ".Version, Browser) | eval Browser=case(like(cs_User_Agent,"%;+MSIE+8.0;%"), "Internet Explorer 8", like(cs_User_Agent,"%+MSIE+ 7.0%"), "Internet Explorer 8", like(cs_User_Agent,"%;+MSIE+9.0;%"), "Internet Explorer 9", like(cs_User_Agent,"%;+MSIE+10.0;%"), "Internet Explorer 10", like(cs_User_Agent,"%;+rv:11.0%"), "Internet Explorer 11", like(cs_User_Agent,"%;+Trident/7.0;+%"), "Internet Explorer 11", like(cs_User_Agent,"%+Chrome%"), "Chrome", 1==1, "Other") | eval OS=case(like(cs_User_Agent,"%Windows+NT+5.1%"), "Windows XP", like(cs_User_Agent,"%Windows+NT+ 6.0;%"), "Windows Vista", like(cs_User_Agent,"%Windows+NT+6.1;%"), "Windows 7", like(cs_User_Agent,"%Windows+NT+6.2%"), "Windows 8", like(cs_User_Agent,"%Windows+NT+6.3;%"), "Windows 8.1", like(cs_User_Agent,"%Windows+NT+10.0;%"), "Windows 10", 1==1, "Other") | eval Browser=case(!isnull(Browser), Browser, match(BrowserInfo, "^Chrome"), "Chrome", 1==1,"Other") | eval BrowserInfo=coalesce(BrowserInfo, Browser, Version) | table LHD, cs_username, a_app, OS, BrowserInfo, _time, cs_User_Agent ... View more

Great that worked however, now I have to create two new fields. Is there a way to append the results into all BrowserInfo? this is the code: | rex field=cs_User_Agent "(?<BrowserInfo>(\Chrome).[^\+;]*)" | rex field=BrowserInfo "[:\/ ](?<Version>.*)" | eval Browser=case(match(BrowserInfo, "^Chrome"), "Chrome") | eval Browser=if(!isnull(Version),Browser." ".Version, Browser) | eval Browser=case(like(cs_User_Agent,"%;+MSIE+8.0;%"), "Internet Explorer 8", like(cs_User_Agent,"%+MSIE+ 7.0%"), "Internet Explorer 8", like(cs_User_Agent,"%;+MSIE+9.0;%"), "Internet Explorer 9", like(cs_User_Agent,"%;+MSIE+10.0;%"), "Internet Explorer 10", like(cs_User_Agent,"%;+rv:11.0%"), "Internet Explorer 11", like(cs_User_Agent,"%;+Trident/7.0;+%"), "Internet Explorer 11", 1==1, "Other") | eval OS=case(like(cs_User_Agent,"%Windows+NT+5.1%"), "Windows XP", like(cs_User_Agent,"%Windows+NT+ 6.0;%"), "Windows Vista", like(cs_User_Agent,"%Windows+NT+6.1;%"), "Windows 7", like(cs_User_Agent,"%Windows+NT+6.2%"), "Windows 8", like(cs_User_Agent,"%Windows+NT+6.3;%"), "Windows 8.1", like(cs_User_Agent,"%Windows+NT+10.0;%"), "Windows 10", 1==1, "Other") | eval Browser=case(!isnull(Browser), Browser, match(BrowserInfo, "^Chrome"), "Chrome", 1==1,"Other") ... View more

Ok thanks, would it be reasonable to combine mine with yours. As I have Internet Explorer down its just the Chrome field I need and that is consistent. Issue when I tried is that one overwrites the other so "Other" replaces all IE or vica versa all Chrome replaces "Other" if you get my idea? This is what I had just to capture Chrome: | rex field=cs_User_Agent "(?<BrowserInfo>(\Chrome).[^\+;]*)" | rex field=BrowserInfo "[:\/ ](?<Version>.*)" | eval Browser=case(match(BrowserInfo, "^Chrome"), "Chrome", 1==1,"Other") | eval Browser=if(!isnull(Version),Browser." ".Version, Browser) | table LHD, cs_username, a_app, cs_User_Agent, Browser, BrowserInfo, Version,  a_request, _time | dedup cs_username sortby +LHD this look ok ... View more

Hi, this is great for Chrome but now it's all wrong with Internet Explorer. The results I get are not correct. Most of these are IE11 and yet I am now getting IE7. The Trident version is 7.0 but IE11 moved away from MSIE versions since IE11 and now its on the Trident platform. I think I need to drill down on what you're syntax is as I am not famliar with rex. ... View more

I could not find what I wanted in this: https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/TextFunctions ... View more

@renjith_nair  Hi sorry for the delay. I have worked out an alternative to my search. I have another question. Is there a way to dedup a field by another event? I seem to being getting my results but when I dedup username, it just randomly selects an event and lists that in my stats. I want to only dedup the username based on UserAuthenticationQuery event?   Thanks ... View more

@renjith_nair  Thanks again, yes I did remove it as mentioned. It didn't resolve anything other then list all events and not in a table format.  I have looked many times at the search construction and tried many things... I get what I want but do not understand the logic on how to link the two time events to no another.   ... View more

@renjith_nair  So I should remove it? If I do, it doesn't change anything I still get 2,730 results. I grabbed this from you on the first page you mentioned: @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 ... View more

As mentioned this is the closest I have: But it includes every event at those timestamps. I only want the timestamp in which a_action = UserAuthenticationQuery. if I include UserAuthenticationQuery  in search then it drops off all events. I don't know why it still doesn't append the multiple app values under appList field? ... View more

As mentioned this is the closest I have: But it includes every event at those timestamps. I only want the timestamp in which a_action = UserAuthenticationQuery. if I include UserAuthenticationQuery  in search then it drops off all events. I don't know why it still doesn't append the multiple app values under appList field? ... View more

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...:) ... View more

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? ... View more

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. ... View more

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. ... View more

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.  ... View more

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?   ... View more

Hi again, Thanks but as I said removing the pipe I get no results ... View more

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     ... View more

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       ... View more

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 ) ... View more