topic Re: Customise query in Splunk Enterprise

How do I customize search for table output?

mahesh27 — Mon, 06 Mar 2023 14:36:56 GMT

Search:

index=xxxx sourcetype=xxxxx home_feature!=connectapp application_name IN(artical, login, management, pageout) |table Description application _time count |sort Description _time home_feature application_name streamstats current=f window=1 values( Description) as desp values(home_feature) as app values(_time) as totaltime values (count) as totalcount |eval siml=if(home_feature == app AND Description == desp, count - totalcount,0) |eval siml2=if(siml <0, Count, siml) |where siml2 > 0 |eval time=strftime(now(), %d/%m/%YT%H:%M:%S) |stats sum(value) by home_feature, application_name

Output:

home_feature	application_name	sum(value)
ampt.gc.com	login	298
ampt.gc.com	pageout	2341
https:gtt.com	artical	4567
wcw.gft.com	management	678
app.df.com	login	499
rt.hj.com	pageout	567
tt.com	artical	345
ggt.com	management	178

but i need the output as shown below:

_time	home_feature	login	pageout	management	artical
03/02/2023T14:05:15	ampt.gc.com	298	100	678	567
03/02/2023T12:05:15	ampt.gc.com	345	345	12341	789
03/02/2023T11:05:15	https:gtt.com	100	45678	9087	4567
03/02/2023T10:05:15	wcw.gft.com	456	567	678	789
03/02/2023T09:05:15	app.df.com	900	345	23499	3215
03/02/2023T08:05:15	rt.hj.com	789	125	567	678
03/02/2023T06:05:15	tt.com	12	34	345	45
03/02/2023T04:05:15	ggt.com	23	14	178	34

how to achieve this?

Re: Customise query

ITWhisperer — Fri, 03 Mar 2023 20:06:51 GMT

index=xxxx sourcetype=xxxxx home_feature!=connectapp application_name IN(artical, login, management, pageout) |table Description application _time count |sort Description _time home_feature application_name streamstats current=f window=1 values( Description) as desp values(home_feature) as app values(_time) as totaltime values (count) as totalcount |eval siml=if(home_feature == app AND Description == desp, count - totalcount,0) |eval siml2=if(siml <0, Count, siml) |where siml2 > 0 |chart sum(value) by home_feature, application_name |eval time=strftime(now(), %d/%m/%YT%H:%M:%S)

Re: Customise query

mahesh27 — Sun, 05 Mar 2023 20:40:13 GMT

Hi @ITWhisperer .

|chart sum(value) by home_feature, application_name

I tried the above query but i am getting the output as:

home_feature	NULL
ampt.gc.com	678
https:gtt.com	345
ggt.com	45678
gct.com	567
gtt.com	199

And also i tried the query

|chart sum(value) over home_feature by application_name

This is also giving same results as shown above.

Re: Customise query

ITWhisperer — Sun, 05 Mar 2023 23:14:14 GMT

If stats by application_name is giving you login, pageout etc. I am not sure why chart by application_name is giving NULL.

Please can you share some sample events (anonymised of course) and the exact search that you used to get these results?

Re: Customise query

mahesh27 — Mon, 06 Mar 2023 22:34:56 GMT

@ITWhisperer , Now i am getting the expected results, the mistake i did is in chart i was mentioning as chart sum(value), but i should mention as chart sum(diff).
Thank you for your response, it helped me.