Splunk Search

Could someone help on transpose command?

jip31
Motivator

hello

I transpose events like this

 

 

| eval time=strftime(_time,"%H:%M") 
| sort time 
| fields - _time _span _origtime _events 
| transpose 0 header_field=time column_name=KPI include_empty=true 
| rename "row1" as "7:00"
| sort KPI

 

 

 But I have a problem with my header_field

Sometimes it works well because time field is well displayed : 7:00, 8:00, 9:00.....

But sometimes (between 7:00 and 9:00 most of the times and I dont no why because after it works well), instead time fields, I have row1, row2, row3....

jip31_0-1651124956991.png

 

Is anybody have an idea concerning this issue

I try a workaround with the rename of row1, row2...., but the rename doesn't works

Could you help please?

Labels (1)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Your issue is that your first search is not finding any events

Try setting your search time:

<earliest>@d+7h</earliest>
<latest>@d+19h</latest>

and start your search like this:

| makeresults
| timechart span=1h count
| fields - count
| appendcols 
    [ search `index_mesurescontinues` sourcetype="ezoptima:signaler" type=* earliest=@d+7h latest=@d+19h 
    | fields signaler_id 
    | timechart span=1h dc(signaler_id) as "0. Signalements Utilisateur (ezOptima)" 
    | appendpipe 
        [ stats count as _events 
        | where _events = 0 
        | eval "0. Signalements Utilisateur (ezOptima)" = 0 ]
        ] 
| appendcols 
    [ search 

 

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Your issue is that your first search is not finding any events

Try setting your search time:

<earliest>@d+7h</earliest>
<latest>@d+19h</latest>

and start your search like this:

| makeresults
| timechart span=1h count
| fields - count
| appendcols 
    [ search `index_mesurescontinues` sourcetype="ezoptima:signaler" type=* earliest=@d+7h latest=@d+19h 
    | fields signaler_id 
    | timechart span=1h dc(signaler_id) as "0. Signalements Utilisateur (ezOptima)" 
    | appendpipe 
        [ stats count as _events 
        | where _events = 0 
        | eval "0. Signalements Utilisateur (ezOptima)" = 0 ]
        ] 
| appendcols 
    [ search 

 

jip31
Motivator

Many thanks ITWhisperer, it works fine!

0 Karma

bowesmana
SplunkTrust
SplunkTrust

If you don't have a field 'time' in your data then it will create 'row ...'.

What is the table before your transpose command?

0 Karma

jip31
Motivator

but I have a field time....

And like I said it works normaly after a little time so it's very strange...

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Please post an image of the search results before the transpose

0 Karma

jip31
Motivator
0 Karma

bowesmana
SplunkTrust
SplunkTrust

That was the search, not the results.

What do your results look like if you run the search but without the transpose command?

0 Karma

jip31
Motivator

jip31_0-1651129148055.png

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

So, there is no time field, but there is a _time field. 

If that is the data right before the transpose then that is why you get 'row X...'

To diagnose this, you will need to run that mammoth search and gradually remove the subsearches from the bottom to find out why of the appendcols is causing the problem.

Your transpose will ONLY work if the table you are converting has the correct structure.

The fact that you have extra rows is an indication that there is some search problem ABOVE the transpose

0 Karma

jip31
Motivator

Ok I am going to do this but i dont understand why most of the time it works well with the same search....

0 Karma

bowesmana
SplunkTrust
SplunkTrust

When you work backwards, you will understand why ...

I think your problem is that all your timechart statements are expected to return a table like this

_time	24. Portail SIBP / Espace CO - Utilisateurs ayant au moins 2 erreurs
2022-04-28 07:00	4
2022-04-28 08:00	4
2022-04-28 09:00	4
2022-04-28 10:00	4
2022-04-28 11:00	5
2022-04-28 12:00	5
2022-04-28 13:00	4
2022-04-28 14:00	4
2022-04-28 15:00	4
2022-04-28 16:00	4
2022-04-28 17:00	4
2022-04-28 18:00	0

 However, as soon as any one of those searches gets 0 events, then the appendcols is doing this

_time	24. Portail SIBP / Espace CO - Utilisateurs ayant au moins 2 erreurs
 	0

so in that case, _time is null and you only have a single row, not 12 as you would get from the timechart.

For things to work properly, all of those searches must generate the same number of rows with _time values being the correlating row identifier.

In the above scenario, if your FIRST search returns 0 results then the _time column will always be null.

See this example search modelled on yours that exhibits the possible problem

You should be able to run this

index=_audit earliest=@d+7h latest=@d+19h user=xXXX
    | eval web_url=mvindex(split("/VP_/,/VPI/,/VPC/", ","), random() % 3)
    | fields web_url user _time
    | rename user as sam
    | eval sam=lower(sam) 
    | bin _time span=1h 
    | eval url =upper(web_url) 
    | eval SIBP=case(
        match(url,"/VP_/"),"Portail SIBP historique", match(url,"/VPI/"),"IHM porail Espace CO", match(url,"/VPC/"),"Portail Guichet Espace Co"
        ) 
    | search SIBP=* 
    | stats count as PbPerf by sam _time 
    | search PbPerf > 1 
    | timechart span=1h count as "NO USERS" 
    | appendpipe 
        [ stats count as _events 
        | where _events = 0 
        | eval "NO USERS" = 0 ]
| appendcols [ search index=_audit earliest=@d+7h latest=@d+19h user=*
    | eval web_url=mvindex(split("/VP_/,/VPI/,/VPC/", ","), random() % 3)
    | fields web_url user _time
    | rename user as sam
    | eval sam=lower(sam) 
    | bin _time span=1h 
    | eval url =upper(web_url) 
    | eval SIBP=case(
        match(url,"/VP_/"),"Portail SIBP historique", match(url,"/VPI/"),"IHM porail Espace CO", match(url,"/VPC/"),"Portail Guichet Espace Co"
        ) 
    | search SIBP=* 
    | stats count as PbPerf by sam _time 
    | search PbPerf > 1 
    | timechart span=1h count as "ALL USERS" 
    | appendpipe 
        [ stats count as _events 
        | where _events = 0 
        | eval "ALL USERS" = 0 ]
        ]
0 Karma

jip31
Motivator

Sorry but its not clearing for me

Do i have to double all my searches with 2 différents conditions?

    | timechart span=1h count as "NO USERS" 
    | timechart span=1h count as "ALL USERS" 

Could you please give me an example from my original search? 

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...