topic Re: Query related to earliest & latest functions in Splunk Search

Query related to earliest & latest functions

mdyunusraza — Thu, 29 Jun 2017 06:14:25 GMT

Hi All,

I need help to figure out a query which give me a report based on time. I have tried out my hands with some query but due to my beginners knowledge of splunk, i have not been able to get it working.

Basically I have following fields from two sources

Source 1:
DATE USER LOGINTIME LOGTOUTTIME
27/06/2017 USER1 10:00 10:50
27/06/2017 USER1 11:00 11:25

Source 2:
DATE USER APPNAME APPSTARTIME APPENDTIME DEVICENAME
27/06/2017 USER1 APP1 10:02 10:10 PC1
27/06/2017 USER1 APP2 10:03 10:15 PC1
27/06/2017 USER1 APP3 10:04 10:09 PC1
27/06/2017 USER1 APP4 11:03 11:15 PC2
27/06/2017 USER1 APP5 11:04 11:09 PC2

The above data comes when a user logs in to webserver and launches the application. But the same user can go to another machine and login again to the same webserver and launch applications again. So the results of such actions are depicted in table above.

What I want to achieve is using the LOGINTIME & LOGOUTTIME as boundary for each unique LOGINTIME of Source1, I want to capture the first application launch time. A user may have launched other apps but I dont want that.

so the result should look like this in each individual row. Is this possible?

DATE USER LOGINTIME APPNAME APPSTARTTIME LOGOUTTIME
27/06/2017 USER1 10:00 APP1 10:02 10:50
27/06/2017 USER1 11:00 APP4 11:03 11:25

Re: Query related to earliest & latest functions

cmerriman — Thu, 29 Jun 2017 11:51:09 GMT

using the data you provided, i've come up with this:

|makeresults|eval data="DATE=27/06/2017 USER=USER1 LOGINTIME=10:00 LOGOUTTIME=10:50,DATE=27/06/2017 USER=USER1 LOGINTIME=11:00 LOGOUTTIME=11:25,DATE=27/06/2017 USER=USER1 APPNAME=APP1 APPSTARTTIME=10:02 APPENDTIME=10:10 DEVICENAME=PC1,DATE=27/06/2017 USER=USER1 APPNAME=APP2 APPSTARTTIME=10:02 APPENDTIME=10:15 DEVICENAME=PC1,DATE=27/06/2017 USER=USER1 APPNAME=APP3 APPSTARTTIME=10:04 APPENDTIME=10:09 DEVICENAME=PC1,DATE=27/06/2017 USER=USER1 APPNAME=APP4 APPSTARTTIME=11:03 APPENDTIME=11:15 DEVICENAME=PC2,DATE=27/06/2017 USER=USER1 APPNAME=APP5 APPSTARTTIME=11:09 APPENDTIME=10:09 DEVICENAME=PC2"|makemv data delim=","|mvexpand data|eval _raw=data|kv|eval time=coalesce(LOGINTIME,APPSTARTTIME)|sort USER DATE time|streamstats window=1 current=f values(LOGINTIME) as LOGIN values(LOGOUTTIME) as LOGOUT by USER DATE|table DATE USER LOGIN APPNAME APPSTARTTIME LOGOUT|search LOGIN=*

you might have to make your date into epoch to sort by it correctly, but otherwise adding this to your search should work:

|eval time=coalesce(LOGINTIME,APPSTARTTIME)|sort USER DATE time|streamstats window=1 current=f values(LOGINTIME) as LOGIN values(LOGOUTTIME) as LOGOUT by USER DATE|table DATE USER LOGIN APPNAME APPSTARTTIME LOGOUT|search LOGIN=*

Re: Query related to earliest & latest functions

mdyunusraza — Fri, 30 Jun 2017 04:00:52 GMT

Thanks for the reply. It is giving other fields except the APNAME and APSTARTTIME. Below is the actual query from my system. The only change that I made to your suggested query is that I added RENAME as the DATE and USER fields in my both searches are different. Not sure if that is what is creating the issue as my original post mentioned DATE and USER filed name as same in both the sources

(index=* sourcetype="mywebserver" ) OR (index=main sourcetype=sql source=sqldb1 OR source=mi_input://sqldb1) | fields DBDATE,DBUSER,DBLOGIN,DBLOGOUT,APDATE,APSTARTTIME,APUSER,APNAME | rename DBDATE as DATE DBUSER as USER APDATE as DATE APUSER as USER | eval time=coalesce(DBLOGIN,APSTARTTIME)|sort USER DATE time|streamstats window=1 current=f values(DBLOGIN) as LOGIN values(DBLOGOUT) as LOGOUT by USER DATE|table DATE USER LOGIN StartTime LOGOUT|search LOGIN=*

Re: Query related to earliest & latest functions

cmerriman — Fri, 30 Jun 2017 11:25:09 GMT

you cannot rename two different fields to the same name (DBDATE and APDATE, DBUSER and APUSER), you'll have to use coalesce.
|eval DATE=coalesce(DBDATE,APDATE)|eval USER=(DBUSER,APUSER)
see if that helps.

Re: Query related to earliest & latest functions

mdyunusraza — Fri, 14 Jul 2017 07:29:32 GMT

sorry for the delayed response. That worked. Thanks for your help