topic not getting expected results from using multiple sourcetypes in Splunk Search

not getting expected results from using multiple sourcetypes

Laya123 — Thu, 30 Mar 2017 12:26:30 GMT

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

Re: not getting expected results from using multiple sourcetypes

somesoni2 — Thu, 30 Mar 2017 16:53:24 GMT

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

Re: not getting expected results from using multiple sourcetypes

Laya123 — Tue, 29 Sep 2020 13:30:02 GMT

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

Re: not getting expected results from using multiple sourcetypes

Laya123 — Mon, 03 Apr 2017 04:37:35 GMT

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

Re: not getting expected results from using multiple sourcetypes

Laya123 — Mon, 03 Apr 2017 09:39:35 GMT

hi,

any help on this

Thanks

Re: not getting expected results from using multiple sourcetypes

Laya123 — Tue, 04 Apr 2017 18:42:40 GMT

please tell me whether it is possible or not

Thanks

Re: not getting expected results from using multiple sourcetypes

somesoni2 — Tue, 04 Apr 2017 19:01:26 GMT

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"

Re: not getting expected results from using multiple sourcetypes

Laya123 — Thu, 06 Apr 2017 10:11:09 GMT

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

Thank you