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

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

rkishoreqa — Thu, 24 Dec 2020 16:01:56 GMT

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.

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

richgalloway — Thu, 24 Dec 2020 20:15:19 GMT

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

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

rkishoreqa — Mon, 28 Dec 2020 17:30:11 GMT

Hi richgalloway,

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

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

richgalloway — Mon, 28 Dec 2020 18:13:38 GMT

Like this?

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

rkishoreqa — Tue, 29 Dec 2020 16:52:02 GMT

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

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

rkishoreqa — Wed, 30 Dec 2020 13:27:58 GMT

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]