How to find count of transactions with unique id having more than 2 jobid's and ran more than 5 mins

[rkishoreqa]

I need to build a query to get count of transactions having multiple 'jId' and time difference greater than 5 mins. 
Want to find 'Applname' which is having  'ASNumber'  with multiple 'jId's.

|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];"
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "\<ASNumber\>(?<ASNumber>[^\<]+)\<[^\<]"
|stats values(jId) as jId by ASNumber
|stats earliest(_time) as start latest(_time) as end by jId,sourcetype
|eval diff=end-start

the stats will be like as below:
Applname1  -  3
Applname2  -  2
Applname3  -  1

Can anyone help me on this.

[richgalloway · Accepted solution]

An important thing to remember about the stats command is that it throws away all fields it doesn't use.  In this query, the first stats command discards everything except jId and ASNumber so there is no _time or sourcetype field for the second stats command to use.  Try this query as an alternative

|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];"
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "\<ASNumber\>(?<ASNumber>[^\<]+)\<[^\<]"
|eventstats count(jId) as jIdcount by ASNumber
|where jIdcount > 1
|stats range(_time) as diff, first(ASNumber) as ASNumber by jId

[rkishoreqa]

Hi  richgalloway, 

How can I get the count of 'ASNumber'  in a new column.

[richgalloway]

Like this?

|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];"
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "\<ASNumber\>(?<ASNumber>[^\<]+)\<[^\<]"
|eventstats count(jId) as jIdcount by ASNumber
|where jIdcount > 1
|stats range(_time) as diff, first(ASNumber) as ASNumber, count(ASNumber) as count by jId

[rkishoreqa]

Hi , 

Based on your suggestion I prepared queries for two different apps as below.  Now I need to combine these two and get a single stats table.

Stats table like as : 

jId  Applname       diff   ASNumber - StNumber -  count

xy     app1              23        983723                                   2
uw    app2             98                                377813            1

Query 1:
|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];"
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "\<ASNumber\>(?<ASNumber>[^\<]+)\<[^\<]"
|eventstats count(jId) as jIdcount by ASNumber
|where jIdcount > 1
|stats range(_time) as diff, first(ASNumber) as ASNumber, count(ASNumber) as count by jId,Applname

Query 2:
|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];" 
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "StNumber\":\"(?P<StNumber>.[^\"\,\"]*)"
|eventstats count(jId) as jIdcount by StNumber
|where jIdcount > 1
|stats range(_time) as diff, first(StNumber) as StNumber,count(StNumber) as count by jId,Applname

[rkishoreqa]

I prepared the below query to fetch the values from multiple apps and apply stats on it.   I just added 4 queries into one by using the 'append', but not simplified.  I did't understood how to apply 'append' on eventstats and stats. 

Can anyone help me on this.

index="tibcodev"
|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];"
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "SNumber\":\"(?P<SNumber>.[^\"\,\"]*)"
|eventstats count(jId) as jIdcount by SNumber
|where jIdcount > 1
|stats range(_time) as diff, first(SNumber) as SNumber,count(SNumber) as count by jId,Applname
|append [search index="tibcodev"
|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];"
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "\<ASNumber\>(?<ASNumber>[^\<]+)\<[^\<]"
|eventstats count(jId) as jIdcount by ASNumber
|where jIdcount > 1
|stats range(_time) as diff, first(ASNumber) as ASNumber,count(ASNumber) as count by jId,Applname]
|append [search index="tibcodev"
|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];"
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "CNumber\":\"(?P<CNumber>.[^\"\,\"]*)"
|eventstats count(jId) as jIdcount by CNumber
|where jIdcount > 1
|stats range(_time) as diff, first(CNumber) as CNumber,count(CNumber) as count by jId,Applname]
|append [search index="tibcodev"
|rex field=_raw "ApplicationName:\s+\[(?P<Applname>.*)];"
|rex field=_raw "jobId: (?<jId>\w+);"
|rex field=_raw "PNumber\":\"(?P<PNumber>.[^\"\,\"]*)"
|eventstats count(jId) as jIdcount by PNumber
|where jIdcount > 1
|stats range(_time) as diff, first(PNumber) as PNumber, count(PNumber) as count by jId,Applname]