Splunk Enterprise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

How to combine two searches?

Vani_26
Path Finder
‎11-20-2022 07:53 AM

Can u help me on this issue.
As character are exceeding i am posting the question 3 times total.

I am trying to combine 2 queries to get the result, i am getting the result, but not as expected.

Query1:

index=app-map-idx   shname=niht_map_*
| append [| inputlookup   customerdata.csv
|addinfo
| where  _time>=info_min_time AND _time<=info_max_time 
|fields - info*
|eval done=1, shname=device."_name",  source=device."_name"]
|table _time   sys operation web pass fail fvb  quantity done shname
|eventstats sum(done) as done by shname sys
|bucket _time span=w
|stats sum(*) as * values(done) as done dc(web) as webb by _time shname  sys operation
|appendpipe [| search sys="wi lapcdmb" operation="login page" OR operation="app page" OR operation="userpage" OR operation="custpage"]
|foreach pass fail  fvb  quantity [|eval  <<FIELD>>=<<FILED>>*0.09]
|eval sys="pim"]
| eval  "pass percentage"=pass/quantity*100
|eval iel=fail/quantity*100
|fillnull fvb value=0
|rename  pass as Pass,   fail as Fail,  sys as Sys,  fvb as Fvb,   quantity as Quantity,  operation as Operation,  webb as Webb
|lookup application.csv Sys  OUTPUT   App  mtr
|search mtr=given
|table  _time pass percentage iel App Sys  Operation Webb Pass Fail Quantity Fvb  
|sort 0 Sys


Output:

_time App Sys Operation Webb Pass Fail Quantity Fvb pass percentage iel
2022-11-10 Custom Customer sys sigin app - customer 1 20 0 20 1 100 0
2022-11-10 Cli app CA sigin app - application 2 30 10 40 10 99 0.01
2022-11-10 sigin app SG sigin app - client 2 10 1 11 1 100 0
2022-11-10 user app UA sigin app - app1 1 60 0 60 16 80 0.05
2022-11-10 user app UA sigin app - app2 4 50 0 50 0 20 0.9
2022-11-10 user app UA sigin app - app3 5 100 9 109 0 0 0
2022-11-10 user app UA sigin app - app4 8 21 0 21 0 0 0
2022-11-10 user app UA sigin app - app5 9 23 0 23 0 0 0
2022-11-10 suzk app SA sigin app - app6 2 16 8 24 1 99 0
2022-11-10 qualis app QA sigin app - app7 1 100 10 110 8 10 0
2022-11-10 benz app BA sigin app - app8 2 500 11 511 6 20 0
2022-11-10 client app CA sigin app - app1 4 200 0 200 4 100 0
2022-11-10 client app CA sigin app - app2 1 800 1 801 1 100 0.11
2022-11-10 client app CA sigin app - app3 3 220 1 221 0 90 0.22
2022-11-10 client app CA sigin app - app4 2 1000 11 1011 0 99 0.45
2022-11-10 client app CA sigin app - app5 1 1089 12 2178 9 99 0.56



Query2:
index=blrm-app-idx  sourcetype=blrm_appl  "QA: ab"  OR "QA: cd"  OR "QA: ef"  OR "QA: gh" (procedure=POST web="/cate/surface*")  OR  (procedure=GET web="/choc/plain/otp*")  OR  (procedure=POST web="/may/duration")  OR  (procedure=GET web="/year/days")  OR   web="/nam/power/error")  OR  web="/move/cloud")  OR   web="/mont/days
NOT [|inputlookup  angryer.csv
|rename PI as result
|fields result]
| eval  type=case((faildigit=978 OR faildigit=435 OR faildigit=543 OR faildigit=987), "error",
(faildigit>98763 AND faildigit<98765 )  OR faildigit=123 OR faildigit=456 OR faildigit=789,  fvb,  isnull(faildigit), "pass",   isnotnull(faildigit), "error")
|search pipe = "error"
|eval operation=case(match(web,  "/cate/surface*"),  sigin app - customer,   procedure=GET AND  web="/choc/plain/otp*") ,  sigin app - application,   procedure=POST AND web="/may/duration",  sigin app - client,   procedure=GET  AND web="/year/days", sigin app - app1,  OR   web="/nam/power/error",  sigin app - app2  OR  web="/move/cloud", sigin app - app3 OR   web="/mont/days, sigin app - app4,  1=1, "unknown")
|eval  web = procedure." ".web
|eval sys= case (cliapp=ab,  applibase,   cliapp=cd,  cusbase,    cliapp=ef,  efffilm,   cliapp=gh    gohome,  1=1, null())
|bin _time span=1h
|stats dc(cust_ip) as effect_apps  by _time sys operation web

OUTPUT:

_time sys operation web effect_apps
2022-11-10 UA sigin app - app1 1 10
2022-11-10 UA sigin app - app2 2 12
2022-11-10 UA sigin app - app3 2 10
2022-11-10 UA sigin app - app4 1 40
2022-11-10 UA sigin app - app5 4 2
2022-11-10 CA sigin app - app1 5 6
2022-11-10 CA sigin app - app2 8 1
2022-11-10 CA sigin app - app3 9 3
2022-11-10 CA sigin app - app4 2 4
2022-11-10 CA sigin app - app5 1 8


contunuation i am repying to this post...........

Labels (1)
Labels
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

ITWhisperer
SplunkTrust
SplunkTrust
‎11-21-2022 06:37 AM

Sys Operation Webb do not exist in the subsearch of the join. Your fields are called sys operation and web and even then web does not appear in the final stats command so won't be returned by the subsearch anyway.

View solution in original post

Reply
Solved! Jump to solution

Vani_26
Path Finder
‎11-20-2022 07:54 AM

Combined 2 queries using appencols:
index=app-map-idx   shname=niht_map_*
| append [| inputlookup   customerdata.csv
|addinfo
| where  _time>=info_min_time AND _time<=info_max_time 
|fields - info*
|eval done=1, shname=device."_name",  source=device."_name"]
|table _time   sys operation web pass fail fvb  quantity done shname
|eventstats sum(done) as done by shname sys
|bucket _time span=w
|stats sum(*) as * values(done) as done dc(web) as webb by _time shname  sys operation
|appendpipe [| search sys="wi lapcdmb" operation="login page" OR operation="app page" OR operation="userpage" OR operation="custpage"]
|foreach pass fail  fvb  quantity [|eval  <<FIELD>>=<<FILED>>*0.09]
|eval sys="pim"]
| eval  "pass percentage"=pass/quantity*100
|eval iel=fail/quantity*100
|fillnull fvb value=0
|rename  pass as Pass,   fail as Fail,  sys as Sys,  fvb as Fvb,   quantity as Quantity,  operation as Operation,  webb as Webb
|lookup application.csv Sys  OUTPUT   App  mtr
|search mtr=given
|table  _time pass percentage iel App Sys  Operation Webb Pass Fail Qyantity Fvb  
|sort 0 Sys

|appendcols

[search  index=blrm-app-idx  sourcetype=blrm_appl  "QA: ab"  OR "QA: cd"  OR "QA: ef"  OR "QA: gh" (procedure=POST web="/cate/surface*")  OR  (procedure=GET web="/choc/plain/otp*")  OR  (procedure=POST web="/may/duration")  OR  (procedure=GET web="/year/days")  OR   web="/nam/power/error")  OR  web="/move/cloud")  OR   web="/mont/days
NOT [|inputlookup  angryer.csv
|rename PI as result
|fields result]
| eval  type=case((faildigit=978 OR faildigit=435 OR faildigit=543 OR faildigit=987), "error",
(faildigit>98763 AND faildigit<98765 )  OR faildigit=123 OR faildigit=456 OR faildigit=789,  fvb,  isnull(faildigit), "pass",   isnotnull(faildigit), "error")
|search pipe = "error"
|eval operation=case(match(web,  "/cate/surface*"),  sigin app - customer,   procedure=GET AND  web="/choc/plain/otp*") ,  sigin app - application,   procedure=POST AND web="/may/duration",  sigin app - client,   procedure=GET  AND web="/year/days", sigin app - app1,  OR   web="/nam/power/error",  sigin app - app2  OR  web="/move/cloud", sigin app - app3 OR   web="/mont/days, sigin app - app4,  1=1, "unknown")
|eval  web = procedure." ".web
|eval sys= case (cliapp=ab,  applibase,   cliapp=cd,  cusbase,    cliapp=ef,  efffilm,   cliapp=gh    gohome,  1=1, null())
|bin _time span=1h
|stats dc(cust_ip) as effect_apps  by _time sys operation
|appendpipe
[|lookup application.csv  App OUTPUT App mtr |search mtr=given]] 

OUTPUT:
Here i want to getting the effect_values from 2 query and not displaying in correct place.
I want the effect_apps values to be displayed by operation like:
under UA Sys , we have sigin app 

_timeAppSysOperationWebbPassFailQuantityFvbpass percentageieleffect_apps
2022-11-10CustomCustomer syssigin app - customer1200201100010
2022-11-10Cli appCAsigin app - application230104010990.0112
2022-11-10sigin appSGsigin app - client2101111100010
2022-11-10user appUAsigin app - app116006016800.0540
2022-11-10user appUAsigin app - app24500500200.92
2022-11-10user appUAsigin app - app3510091090006
2022-11-10user appUAsigin app - app48210210001
2022-11-10user appUAsigin app - app59230230003
2022-11-10suzk appSAsigin app - app621682419904
2022-11-10qualis appQAsigin app - app711001011081008
2022-11-10benz appBAsigin app - app82500115116200 
2022-11-10client appCAsigin app - app14200020041000 
2022-11-10client appCAsigin app - app21800180111000.11 
2022-11-10client appCAsigin app - app3322012210900.22 
2022-11-10client appCAsigin app - app4210001110110990.45 
2022-11-10client appCAsigin app - app5110891221789990.56 

 

Tags (1)
0 Karma
Reply
Solved! Jump to solution

Vani_26
Path Finder
‎11-20-2022 07:55 AM

CONTINUATION OF THE POST...........

But i need to get the output as shown  in the below format:

OUTPUT:
Here i am getting the effect_values from 2 query and not displaying in correct place.
I want the effect_apps values to be displayed by operation like for example
under Sys- UA  we have 5 operation sigin app - app1 ......... to  sigin app - app5,  effect_apps values should display in these places.
And in remaning places it should N/A.

_timeAppSysOperationWebbPassFailQuantityFvbpass percentageieleffect_apps
2022-11-10CustomCustomer syssigin app - customer12002011000N/A
2022-11-10Cli appCAsigin app - application230104010990.01N/A
2022-11-10sigin appSGsigin app - client21011111000N/A
2022-11-10user appUAsigin app - app116006016800.0510
2022-11-10user appUAsigin app - app24500500200.912
2022-11-10user appUAsigin app - app35100910900010
2022-11-10user appUAsigin app - app482102100040
2022-11-10user appUAsigin app - app59230230002
2022-11-10suzk appSAsigin app - app62168241990N/A
2022-11-10qualis appQAsigin app - app71100101108100N/A
2022-11-10benz appBAsigin app - app82500115116200N/A
2022-11-10client appCAsigin app - app142000200410006
2022-11-10client appCAsigin app - app21800180111000.111
2022-11-10client appCAsigin app - app3322012210900.223
2022-11-10client appCAsigin app - app4210001110110990.454
2022-11-10client appCAsigin app - app5110891221789990.568

 

0 Karma
Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎11-21-2022 12:41 AM

appendcols is hardly ever the solution - there are exceptions to this - this is not one of them!

You could try using a left join to get the additional data to line up with the values from the common fields.

0 Karma
Reply
Solved! Jump to solution

vishwa
Path Finder
‎11-21-2022 06:20 AM

when i use left join i am not getting values under effect_apps.

0 Karma
Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎11-21-2022 06:25 AM

What is the search you are using - note that field names are case-sensitive so Sys will not match with sys, Webb will not match with web, etc. 

0 Karma
Reply
Solved! Jump to solution

vishwa
Path Finder
‎11-21-2022 06:33 AM

i am using the below query by mention join, please let me know is this query correct ????

index=app-map-idx   shname=niht_map_*
| append [| inputlookup   customerdata.csv
|addinfo
| where  _time>=info_min_time AND _time<=info_max_time 
|fields - info*
|eval done=1, shname=device."_name",  source=device."_name"]
|table _time   sys operation web pass fail fvb  quantity done shname
|eventstats sum(done) as done by shname sys
|bucket _time span=w
|stats sum(*) as * values(done) as done dc(web) as webb by _time shname  sys operation
|appendpipe [| search sys="wi lapcdmb" operation="login page" OR operation="app page" OR operation="userpage" OR operation="custpage"]
|foreach pass fail  fvb  quantity [|eval  <<FIELD>>=<<FILED>>*0.09]
|eval sys="pim"]
| eval  "pass percentage"=pass/quantity*100
|eval iel=fail/quantity*100
|fillnull fvb value=0
|rename  pass as Pass,   fail as Fail,  sys as Sys,  fvb as Fvb,   quantity as Quantity,  operation as Operation,  webb as Webb
|lookup application.csv Sys  OUTPUT   App  mtr
|search mtr=given
|table  _time pass percentage iel App Sys  Operation Webb Pass Fail Qyantity Fvb  
|sort 0 Sys

|join type=left _time Sys Operation Webb

[search  index=blrm-app-idx  sourcetype=blrm_appl  "QA: ab"  OR "QA: cd"  OR "QA: ef"  OR "QA: gh" (procedure=POST web="/cate/surface*")  OR  (procedure=GET web="/choc/plain/otp*")  OR  (procedure=POST web="/may/duration")  OR  (procedure=GET web="/year/days")  OR   web="/nam/power/error")  OR  web="/move/cloud")  OR   web="/mont/days
NOT [|inputlookup  angryer.csv
|rename PI as result
|fields result]
| eval  type=case((faildigit=978 OR faildigit=435 OR faildigit=543 OR faildigit=987), "error",
(faildigit>98763 AND faildigit<98765 )  OR faildigit=123 OR faildigit=456 OR faildigit=789,  fvb,  isnull(faildigit), "pass",   isnotnull(faildigit), "error")
|search pipe = "error"
|eval operation=case(match(web,  "/cate/surface*"),  sigin app - customer,   procedure=GET AND  web="/choc/plain/otp*") ,  sigin app - application,   procedure=POST AND web="/may/duration",  sigin app - client,   procedure=GET  AND web="/year/days", sigin app - app1,  OR   web="/nam/power/error",  sigin app - app2  OR  web="/move/cloud", sigin app - app3 OR   web="/mont/days, sigin app - app4,  1=1, "unknown")
|eval  web = procedure." ".web
|eval sys= case (cliapp=ab,  applibase,   cliapp=cd,  cusbase,    cliapp=ef,  efffilm,   cliapp=gh    gohome,  1=1, null())
|bin _time span=1h
|stats dc(cust_ip) as effect_apps  by _time sys operation
|appendpipe
[|lookup application.csv  App OUTPUT App mtr |search mtr=given]] 

0 Karma
Reply
Solved! Jump to solution
Solution

ITWhisperer
SplunkTrust
SplunkTrust
‎11-21-2022 06:37 AM

Sys Operation Webb do not exist in the subsearch of the join. Your fields are called sys operation and web and even then web does not appear in the final stats command so won't be returned by the subsearch anyway.

Reply
Solved! Jump to solution

Vani_26
Path Finder
‎12-06-2022 01:57 PM

thank you @ITWhisperer , it really helped me to get this task done.

0 Karma
Reply
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...