how to combine 2 separate queries and list there data in tabular format.

Aps17 — Thu, 01 Oct 2020 14:03:16 GMT

e.g

QUERY 1: host=jtcstcxbsswb* source="/usr/IBM/HTTPServer/logs/access*" httpmethod="GET" statuscode="200" loaninfo="/api*" OR Requestinfo="*/" OR sitename="*/LoginAccountUserName"

|eval APFields=split(loaninfo,"/")

|eval APNumOfFields=mvcount(APFields)

|eval AP2ndFromLast=mvindex(APFields,APNumOfFields-2)

|eval APLoanNumber=mvindex(APFields,6)

|eval APLast=mvindex(APFields,-1)

|search APLast="loans" OR APLast="summary" OR APLast="payments"

|timechart count(APLast), Avg(cookie) as URT by APLast

Query 2 :sourcetype=apigee:digit* host=JTCLSGLAPGERT* APIProduct=*-Authenticated-Product
|timechart span=5m distinct_count(LoginAccountUserName)

i want something like this

host=jtcstcxbsswb* source="/usr/IBM/HTTPServer/logs/access*" httpmethod="GET" statuscode="200" loaninfo="/api*"

|eval APFields=split(loaninfo,"/")

|eval APNumOfFields=mvcount(APFields)

|eval AP2ndFromLast=mvindex(APFields,APNumOfFields-2)

|eval APLoanNumber=mvindex(APFields,6)

|eval APLast=mvindex(APFields,-1)

|search APLast="loans" OR APLast="summary" OR APLast="payments"

|stats count(APLast), Avg(cookie) as URT by APLast

|append [search sourcetype=apigee:digit* host=JTCLSGLAPGERT* APIProduct=*-Authenticated-Product
|timechart span=5m distinct_count(LoginAccountUserName) ]
|bin _time|stats count(APLast), Avg(cookie) as URT ,distinct_count(LoginAccountUserName) by APLast

I am able to get the data as
Time | count(APLAST) | URT | LoginAccountUserName (I see only zero values in LoginAccountUserName)

how to fetch the LoginAccountUserName data from 2nd query and list it here.

Re: how to combine 2 separate queries and list there data in tabular format.

richgalloway — Thu, 01 Oct 2020 14:54:05 GMT

Looking at the last bits of the merged query

|stats count(APLast), Avg(cookie) as URT by APLast |append [search sourcetype=apigee:digit* host=JTCLSGLAPGERT* APIProduct=*-Authenticated-Product |timechart span=5m distinct_count(LoginAccountUserName) ] |bin _time |stats count(APLast), Avg(cookie) as URT ,distinct_count(LoginAccountUserName) by APLast

we see that before the append we have results containing fields APLast, count(APLast), and URT.

The append command produces additional results with fields _time, and distinct_count(LoginAccountUserName).

The final stats command then attempts to blend the two sets of results by the APLast field. but cannot do so because append did not return a field by that name.

The bin_time command is wasted since the final stats command does not use _time.

I hope that explains why you're not getting the expected results. I can't offer a solution to the problem because I don't know your data well enough to determine how to relate LoginAccountUserName to anything else.

topic how to combine 2 separate queries and list there data in tabular format. in Splunk Search

how to combine 2 separate queries and list there data in tabular format.

Re: how to combine 2 separate queries and list there data in tabular format.