How to select particular path using `where` ?

[JyotiP]

I have the followinf query

 sourcetype="server" host=*localqa*
    | stats count by Path 
    | rex field=Path "\/api\/(?<Path>.*)\/(v1|v2|v3)\/(?<Module>.*)" 
    | streamstats window=2 first(Path) as f_path count as c 
    | eval Path=case(c=1,Path,Path!=f_path,Path,1=1,"") 
    | table Path Module count

The output of the above query is

+-------------------------------------------------------------------------------+
|Path                                   | Module                        |count  |
+-------------------------------------------------------------------------------+
|profile                                | profileTravellroute           |212    |
|statements/trialbalance                | trialbalance                  |14     |
|iteneries/breakout                     | execution                     |1041   |
|                                       | orderDetails                  |117    |
|reporting/trans                        | schemes                       |712    |
|                                       | fixedIncome                   |40     |
|reporting                              | FinalizedReports              |161    |
|                                       | PendingReports                |8      |
|                                       | reportEntries                 |14     |
|                                       | closedReport                  |22     |
|                                       | reportTimes                   |82     |
|                                       | Reportposition                |40     |
|                                       | ReportStates                  |68     |
+-------------------------------------------------------------------------------+

I want to select records only for reporting

+-------------------------------------------------------------------------------+
|Path                                   | Module                        |count  |
+-------------------------------------------------------------------------------+
|reporting/trans                        | schemes                       |712    |
|                                       | fixedIncome                   |40     |
|reporting                              | FinalizedReports              |161    |
|                                       | PendingReports                |8      |
|                                       | reportEntries                 |14     |
|                                       | closedReport                  |22     |
|                                       | reportTimes                   |82     |
|                                       | Reportposition                |40     |
|                                       | ReportStates                  |68     |
+-------------------------------------------------------------------------------+

Tried with

sourcetype="server" host=*localqa*
| stats count by Path 
| rex field=Path "\/api\/(?<Path>.*)\/(v1|v2|v3)\/(?<Module>.*)" 
| streamstats window=2 first(Path) as f_path count as c 
| eval Path=case(c=1,Path,Path!=f_path,Path,1=1,"") 
| where Like (Path, 'reporting%')
| table Path Module count

But not working only the following section are coming

 +------------------------------------------------------------------------------------------------+
    |Path                                           |Module                                |Count       |
    +------------------------------------------------------------------------------------------------+
    |reporting/trans                        | schemes                            |712    |
                                                         | fixedIncome                      |40     |

[kamlesh_vaghela · Accepted solution]

@JyotiP

Can you please try these ?

sourcetype="server" host=*localqa* 
| stats count by Path 
| rex field=Path "\/api\/(?<Path>.*)\/(v1|v2|v3)\/(?<Module>.*)" 
| streamstats window=2 first(Path) as f_path count as c 
| eval Path=case(c=1,Path,Path!=f_path,Path,1=1,"") 
| table Path Module count

OR

sourcetype="server" host=*localqa* 
| stats count by Path 
| rex field=Path "\/api\/(?<Path>.*)\/(v1|v2|v3)\/(?<Module>.*)" 
| streamstats window=2 first(Path) as f_path count as c 
| eval Path=case(c=1,Path,Path!=f_path,Path,1=1,"") 
 | where like(Path,"%reporting%")
| table Path Module count

UPDATED Answer:

sourcetype="server" host=*localqa* 
| stats count by Path 
| rex field=Path "\/api\/(?<Path>.*)\/(v1|v2|v3)\/(?<Module>.*)" 
| streamstats window=2 first(Path) as f_path count as c 
| eval Path=case(c=1,Path,Path!=f_path,Path,1=1,"") 
| streamstats window=2 last(Path) as Lpath | eval Lpath=if(Lpath!="",Lpath,NULL) | filldown Lpath
| where like(Lpath,"%reporting%")
| table Path Module Count

Sample Search:

| makeresults | eval Path="profile", Module="profileTravellroute", Count="212"  | append [
| makeresults | eval Path="statements/trialbalance", Module="trialbalance", Count="12" ] | append [
| makeresults | eval Path="iteneries/breakout", Module="execution", Count="121" ] | append [
| makeresults | eval Path="", Module="orderDetails", Count="129" ] | append [
| makeresults | eval Path="reporting/trans", Module="schemes", Count="127" ] | append [
| makeresults | eval Path="", Module="fixedIncome", Count="122" ] | append [
| makeresults | eval Path="reporting", Module="FinalizedReports", Count="125" ] | append [
| makeresults | eval Path="", Module="reportEntries", Count="122" ] | append [
| makeresults | eval Path="", Module="Reportposition", Count="123" ] | table Path Module Count 
| streamstats window=2 last(Path) as Lpath | eval Lpath=if(Lpath!="",Lpath,NULL) | filldown Lpath
| where like(Lpath,"%reporting%")
| table Path Module Count

Thanks

[JyotiP]

@kamlesh_vaghela only one record from each path is retrieving in both the query, I want all the records

[kamlesh_vaghela]

@JyotiP
Please check my UPDATED answer.