Splunk Search

not getting expected results from using multiple sourcetypes

Laya123
Communicator
Hi, 

I am reposting this question because when I posted first time i didnt use the code button (101 010). sorry for the inconvenience

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 they were different records with different time periods and  different K_Satuts, Reason status, IIS Status etc., but all values of Status are showing in one cell like below but I want each Status in separate row based on time period.

  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. though it is same project and pid but it started at different time periods and having different K_status.

    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

I am using the below query. 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 (1)
0 Karma

Laya123
Communicator

hi,

can any one help me to do my request. I tried different methods but no luck.

Your help in this matter would be greatly appreciated

Thanks

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

(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=upper(coalesce(ProjectName, project_name))
     | eval pid=coalesce(pid, pid1) 
     | search ProjectName= AND pid=* 
     | eval BT=if(isnull(kid), "B_Throt", Description)
     |eval IIS_S=if(BT="B_Throt",sc_status+"-"+"B_Throt",Description)
     | eval Status=case((searchmatch("index=iis sourcetype=iis host=A cs_method=\"GET\"")), Description, 
                         searchmatch("index=th sourcetype=throt host=A"), T_status, 
                         searchmatch("index=th sourcetype=throt host=A"), kid, 
                         searchmatch("index=iis sourcetype=iis host=xyz cs_method=GET"), Reason, 
                         searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), IIS_S, 
                         searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), s, 
                         searchmatch("index=ibm sourcetype=ivw host=ABC"), Queue, 1=1, null())
     | stats min(_time) AS startTime max(_time) AS endTime values(Status) AS Status by ProjectName pid Status
     | eval startTime=strftime(startTime,"%b %d, %Y %T") 
     | eval endTime=strftime(endTime,"%b %d, %Y %T") 
     | table startTime endTime ProjectName pid Status
0 Karma

Laya123
Communicator

Thank you so much for your reply.

its working but not as I expected. when I used your query all statuses of each pid is showing in same column but i want each status in separate column as below.

startTime endTime Proj pid Sam W_1 Kid T_Status I_Status V_Status

03/31/2017 4:23:36 AM 03/31/2017 4:25:36 AM A 1 S1 1dc a3fd Success Yes complete
03/31/2017 4:30:36 AM 03/31/2017 4:40:00 AM A 1 M1 1dc a3fd Success No complete
03/31/2017 5:23:36 PM 03/31/2017 6:00:00 PM A 1 V1 1dc a3fd Success Yes Quit
03/31/2017 4:24:36 AM 03/31/2017 4:25:00 AM B 2 S3 1dc a3fd Success Yes complete
03/31/2017 5:23:36 PM 03/31/2017 6:00:00 PM B 1 M1 1dc a3fd Success Yes timeout

Thanks

0 Karma

Laya123
Communicator

hi,

any help on this

Thanks

0 Karma

Laya123
Communicator

please tell me whether it is possible or not

Thanks

0 Karma

somesoni2
SplunkTrust
SplunkTrust

See if this works out for you

(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())
     | eval Status=coalesce('ThIIS Status','Thr Status','K_Status', 'Reason Status', 'IIS Status', 'S Status', 'IVW Status')
     | stats min(_time) AS startTime max(_time) AS endTime values(*Status) AS *Status by ProjectName pid Status
     | 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"
0 Karma

Laya123
Communicator

No luck, this is giving all statuses in one column, but I want each status in separate column

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 ...