Splunk Enterprise
×

Are you a member of the Splunk Community?

Sign in or Register with your Splunk account to get your questions answered, access valuable resources and connect with experts!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ask a Question

Customized Query: How to combine 2 queries?

vishwa
Path Finder
‎11-19-2022 02:10 PM

Hi All, 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



Labels (2)
0 Karma
Reply

vishwa
Path Finder
‎11-19-2022 02:11 PM

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

vishwa
Path Finder
‎11-19-2022 02:12 PM

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

 

Tags (1)
0 Karma
Reply

vishwa
Path Finder
‎11-20-2022 05:29 AM

hi, anyone can help me from the above posts????

0 Karma
Reply
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...