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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...