How to combine data from different sources and get one report?

[Laya123]

Hi Team,

May be you feel that this is a repetitive questio,n but I didn't get response, so I opened a new question.

i want to create a funnel report in Splunk I need to join different data sources. I am getting output but not giving accurate results. I will give example that will give no confusion.

I have 4 different indexes and sourcetypes with unique pid in all sources but all these sources are inter-related.
I have 4 types of logs: 1. SecurityIISlog 2. Securitylog 3. WebIIS log 4. IVW log.
All these 4 types of logs are for one online survey.
For example, we will send one link to one person to do the survey.

Once he clicks on the link, the first securityIISlog will be generated.
If this person is eligible for the survey he will get a status of Accept then second Securitylog will be generated.
More conditions are checked here and if still eligible he will get a status of Accept (if not, he will get status of Reject, so he will be stopped and he can’t move to 3rd and 4th level).
The third WebIIS log will be generated here, also will have status of Success (any web page level issues will generate http errors like 500, 400 and he can’t move to 4th level).
The fourth IVW log will be generated with the status of survey completed or Timed Out or Survey Stopped.
In each type of log we have some status for this pid.
I want a funnel report which will have the status of the pid from all sources in one report.
If the survey is having 6 respondents and only 4 were reached till 4th level, I want 6 respondents in my output with corresponding status.

I have individual searches for all these sources.

From type 1 logs (securityIISlog)

Search:

index=iis sourcetype=iis host=ABC cs_method="GET" |stats values(Status) as Status_Tiis by _time host pid i_project

Output:

_time                 host    pid    i_project  Status_Tiis
10-05-2016 01:05:25 ABC     A     khduei       Accept
10-05-2016 01:10:45 ABC     B     khduei       Accept
10-05-2016 05:05:25 ABC     C     khduei       Reject
10-05-2016 06:15:35 ABC     A     neodue       Accept
10-05-2016 06:15:45 ABC     B     neodue       Accept
10-05-2016 07:20:25 ABC     X     khduei       Accept

From type 2 logs (Securitylog)

Search:

index=security sourcetype=security host=ABC |stats values(Status) as Status_T by _time host pid i_project

Output:

_time                 host  pid i_project   Status_T
10-05-2016 01:05:28 ABC  A    khduei       Accept
10-05-2016 01:10:47 ABC  B    khduei       Accept
10-05-2016 06:15:36 ABC  A    neodue       Accept
10-05-2016 06:15:46 ABC  B    neodue       Reject
10-05-2016 07:20:27 ABC  X    khduei       Accept

Type 3 logs (Web IIS log)

Search:

index=iis sourcetype=iis host=xyz |stats values(sc_status) as IIS_Status by _time host pid i_project

Output:

_time                 host  pid i_project   IIS_Status
10-05-2016 01:05:29 XYZ     A     khduei       Success 
10-05-2016 01:10:46 XYZ     B     khduei       Success
10-05-2016 06:15:38 XYZ  A    neodue       Success
10-05-2016 07:20:29 XYZ  X    khduei       Success

In Type 4 logs (IVW log)

Search:

index=ibm sourcetype=ivw host=IJK |stats values(Status) as IVW_Status by _time host respID project_name

Output:

_time                 host  pid Project   IVW_Status
10-05-2016 01:06:31 XYZ  A    khduei       completed
10-05-2016 01:12:15 XYZ  B    khduei       completed
10-05-2016 06:17:50 XYZ  A    neodue       Survey stopped
10-05-2016 07:45:35 XYZ  X    khduei       completed

I am using the search below to merge the above outputs in one report; I am getting some results, but it’s not giving all results:

index=iis sourcetype=iis host=ABC cs_method="GET" |eval mytime=strftime(_time, "%d%m%Y %H:%M:%S") |stats values(Status) as Status_Tiis by mytime pid i_project|table mytime i_project pid Status_Tiis|join type=outer [search index=security sourcetype= security host=ABC |eval mytime=strftime(_time, "%d%m%Y %H:%M:%S")|where isnotnull(i_project) |stats list(Status) as Status_T by mytime i_project pid |table mytime host i_project pid Status_T|join type=outer[search index=iis sourcetype=iis host=XYZ cs_method=GET |eval mytime=strftime(_time,"%d%m%Y %H:%M:%S") | stats values(sc_status) as Status_Wiis by mytime i_project pid ]]|table mytime host i_project pid Status_Tiis Status_throttler Status_Wiis

I want an output like:

_time               pid   i_project   Status_Tiis   Status_T   IIS_Status   IVW_Status   Duration (Sec) 
10-05-2016 01:05:25  A   khduei   Accept     Accept  Success      Completed 91
10-05-2016 01:10:45  B   khduei   Accept        Accept   Success      Completed 89
10-05-2016 05:05:25  C   khduei   Reject    
10-05-2016 06:15:35  A   neodue   Accept        Accept   Success      Survey stopped   147
10-05-2016 06:15:45  B   neodue   Accept        Reject                               1
10-05-2016 07:20:25  X   khduei   Accept        Accept   Success      Completed 1500

[woodcock · Accepted solution]

Try this:

(index=iis sourcetype=iis host=ABC cs_method="GET") OR
(index=security sourcetype=security host=ABC ) OR
(index=iis sourcetype=iis host=xyz) OR
(index=ibm sourcetype=ivw host=IJK)
| eval i_project=coalesce(i_project, project_name)
| eval pid=coalesce(pid, respID)
| eval Status_Tiis=if((searchmatch("index=iis sourcetype=iis host=ABC cs_method=\"GET\"")), Status, null())
| eval Status_T=if(searchmatch("index=security sourcetype=security host=ABC"), Status, null())
| eval IIS_Status=if(searchmatch("index=iis sourcetype=iis host=xyz"), sc_status, null())
| eval IVW_Status=if(searchmatch("index=ibm sourcetype=ivw host=IJK"), Status, null())
| stats min(_time) AS _time max(_time) AS endTime values(*Status*) AS *Status* by i_project pid
| eval Duration=tostring((endTime - _time), "duration")
| table _time pid i_project Status_Tiis Status_T IIS_Status IVW_Status Duration

[woodcock]

You can (probably) replace this line:

| eval IIS_Status=if(searchmatch("index=iis sourcetype=iis host=xyz"), sc_status, null())

With this line:

| rename sc_status AS IIS_Status

[Laya123]

Hi,

I need one more help for this data; In my data I have same pids with same project but there is time difference. now using this query I am getting only one pid with min time.

Example if i have Project=A pid=1 and time=24-06-2016 12:30:20 and same project with same pid but time=24-06-2016 12:30:25 then using the query I am getting report like

Project      pid     TIIS_status     T_Status     WebIIS_STatus     IVW Status
A              1             200       Accept               200       Timedout
                             200       Accept               200        Stopped
                             200       Accept               200

like this i have each project may have more than one shared pid with different times; but using the above query I am getting only first pid with status; but I want the the second shared pid (second one) has to repeat in next line with their respective status.

I have used list instead of values to get all pids but it is showing all pids in one row only; but i want to see it in different rows.

EXample

I want my out like this

Project      pid     TIIS_status     T_Status     WebIIS_STatus      IVW Status
A              1             200       Accept               200        Timedout
A              1             200       Accept               200         Stopped
A              1             200       Accept               200

is it possible? can you help me to do this

Thanks in advance

[woodcock]

Just add this:

... | mvexpand pid