Getting Data In

not getting expected results from using multiple sourcetypes

Laya123
Communicator

Hi,
I have a query which is giving the output from multiple sources. I am using stats values() but the output is not coming how I expected.

I am getting output like below, Projectname and pid are same but there were different K_Satuts but all values of K_Status is showing in one cell, but I want each K_Status in separate row.

starttime------------------------------------ endtime------------------------- ProjectName------------ Pid----------- sample ------------ K_Status
Mar 23, 2017 10:41:13--------------Mar 23, 2017 10:41:16------------------ A---------------------- 1---------------- X ---------------- a2345fvwwe3
--------------------------------------------------------------------------------------------------------------------------------------------------------------- xy2234vfs3344
--------------------------------------------------------------------------------------------------------------------------------------------------------------- kjsdhe23434jhj

I am expecting my output like below

starttime endtime ProjectName pid sample K_Status
Mar 23, 2017 10:41:13 Mar 23, 2017 10:41:16 A 1 X a2345fvwwe3
Mar 23, 2017 10:41:15 Mar 23, 2017 10:41:21 A 1 X xy2234vfs3344
Mar 23, 2017 10:45:15 Mar 23, 2017 10:47:21 A 1 X kjsdhe23434jhj

Can anyone help me how to do this

my Query:

(index=iis sourcetype=iis host=A cs_method="GET") OR
(index=th sourcetype=throt host=A) OR
(index=iis sourcetype=iis host=xyz cs_method=GET cs_uri_stem="mriweb.dll" OR cs_uri_stem="auth_fail") OR
(index=ibm sourcetype=ivw host=abc respondentID!=1 )
| rex field=respID ".._(?.)"
| eval ProjectName=coalesce(ProjectName, project_name)
| eval ProjectName= upper(ProjectName)
| eval pid=coalesce(pid, pid1)
| search ProjectName=
AND pid=*

| eval "ThIIS Status"=if((searchmatch("index=iis sourcetype=iis host=A cs_method=\"GET\"")), Description, null())
| eval "Thr Status"=if(searchmatch("index=th sourcetype=throt host=A"), T_status, null())
| eval "K_Status"=if(searchmatch("index=th sourcetype=throt host=A"), kid, null())
| eval "Reason Status"=if(searchmatch("index=iis sourcetype=iis host=xyz cs_method=GET"), Reason, null())
| eval BT=if(isnull(kid), "B_Throt", Description)
|eval IIS_S=if(BT="B_Throt",sc_status+"-"+"B_Throt",Description)
| eval "IIS Status"=if(searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), IIS_S, null())
| eval "S Status"=if(searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), s, null())
| eval "IVW Status"=if(searchmatch("index=ibm sourcetype=ivw host=ABC"), Queue, null())
| stats min(_time) AS startTime max(_time) AS endTime values(Status) AS Status by ProjectName pid
| eval startTime=strftime(startTime,"%b %d, %Y %T")
| eval endTime=strftime(endTime,"%b %d, %Y %T")
| sort _time
| table startTime endTime ProjectName pid "S Status" K_Status "ThIIS Status" "Thr Status" "IIS Status" "IVW Status" "Reason Status"

Thanks in advance

Tags (2)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Please repost your code, using the code button (101 010) to mark it, so that html-like items and asterisks are not read by the interface as formatting commands.

0 Karma

Laya123
Communicator

Thank you, I reposted my question.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try using mvexpand to break the multi-value field into separate events.

... | eval "K_Status"=if(searchmatch("index=th sourcetype=throt host=A"), kid, null()) 
| mvexpand K_Status
| filldown startTime, endTime, ProjectName, pid, "S Status"
| eval "Reason Status"=if(searchmatch("index=iis sourcetype=iis host=xyz cs_method=GET"), Reason, null())
...
---
If this reply helps you, Karma would be appreciated.
0 Karma

Laya123
Communicator

Thank you so much for your immediate response, but its not giving the output as I expected.

I don't want fill down the startime endtime projectname pid etc there as i have different time periods and status for same project and pid.

Thank you

0 Karma
Get Updates on the Splunk Community!

Observability | How to Think About Instrumentation Overhead (White Paper)

Novice observability practitioners are often overly obsessed with performance. They might approach ...

Cloud Platform | Get Resiliency in the Cloud Event (Register Now!)

IDC Report: Enterprises Gain Higher Efficiency and Resiliency With Migration to Cloud  Today many enterprises ...

The Great Resilience Quest: 10th Leaderboard Update

The tenth leaderboard update (11.23-12.05) for The Great Resilience Quest is out >> As our brave ...