Splunk Search

Query related to earliest & latest functions

mdyunusraza
Observer

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

Tags (1)
0 Karma
1 Solution

cmerriman
Super Champion

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=*

View solution in original post

0 Karma

cmerriman
Super Champion

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=*
0 Karma

mdyunusraza
Observer

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=*

0 Karma

mdyunusraza
Observer

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

0 Karma

cmerriman
Super Champion

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.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...