topic Re: Clarification regarding pivot command in Splunk Search

Clarification regarding pivot command

Justin1224 — Tue, 29 Sep 2020 11:08:53 GMT

Hey all,

I've just encountered the pivot command for the first time and after reading through the Splunk page on it, I am still confused as to what it does. If it helps, here is my search query:

| pivot Identity_Management All_Assets count(All_Assets) AS "count" SPLITROW category AS "category" | sort - count

Any help would be appreciated!

Re: Clarification regarding pivot command

inventsekar — Tue, 29 Sep 2020 11:09:00 GMT

| pivot Identity_Management All_Assets count(All_Assets) AS "count" SPLITROW category AS "category" | sort - count

as per my understandings,
pivot Identity_Management All_Assets ---- it uses the data model.
and, it counts all assets as count, split rows category wise and sorting by count.

let us know if you have any other questions.
update - to add the photo, i edited this answer and attached it.
Fundamentally this pivot command is a wrapper around stats and xyseries. It does not add new behavior, but it may be easier to use if you are already familiar with how Pivot works.
so, assume pivot as a simple command like stats. you can see these two example pivot charts, i added the photo below -
https://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/Pivot

best regards,
Sekar

Re: Clarification regarding pivot command

Justin1224 — Tue, 29 Sep 2020 11:09:38 GMT

Thank you very much for your answer!

I'm still a little confused as to what pivot does in this query. I have read the documentation on it but am still unsure.

Also, to recap what you said to see if I understand: the query is using the datamodel named "Identity_Management" and counts the instances of the field "All_Assets" and names this count as "count". (I'm unsure of what SPLITROW does.) Then sorts descending by field count.

Is that correct? Also, could you explain what the SPLITROW part does in more detail, I'm still unsure of what it does.

Thank you!

Re: Clarification regarding pivot command

inventsekar — Mon, 26 Sep 2016 16:10:09 GMT

Hi Justin, i edited the answer and updated little more.. added a photo, for easy understanding..
for splitrows, can you check this once please..
https://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/Pivot

Re: Clarification regarding pivot command

Justin1224 — Mon, 26 Sep 2016 16:57:54 GMT

So to clarify, pivot doesn't change any of the data shown, it's just a different method to search with?

Thanks again!

Re: Clarification regarding pivot command

inventsekar — Mon, 26 Sep 2016 17:11:52 GMT

yes, exactly. Pivot does not change anything. like pie-chart, single value dashboard, pivot just creates a dashboard. thats it.

Re: Clarification regarding pivot command

Justin1224 — Mon, 26 Sep 2016 20:35:51 GMT

Ok awesome thank you so much for your help.

Re: Clarification regarding pivot command

Justin1224 — Tue, 29 Sep 2020 11:09:54 GMT

Sorry, one last question. In your original answer you said, "it uses the data model.
and, it counts all assets as count." What datamodel is it using? What I mean is, what part of the search query specifies the datamodel? Is it Identity_Management or All_Assets? Or both? Thanks again

Re: Clarification regarding pivot command

Justin1224 — Tue, 29 Sep 2020 11:09:57 GMT

Lastly, is count(All_Assets) just getting a count of the instances of the field "All_Assets" within the data? Or is All_Assets a string?

Re: Clarification regarding pivot command

Justin1224 — Mon, 26 Sep 2016 22:21:10 GMT

Sorry one final question. Why is the as capitalized (AS)? I thought that when "as" is in a search it renames a field as something else. But what does it does when it is capitalized?

Thank you again and sorry for all the questions

Re: Clarification regarding pivot command

inventsekar — Tue, 27 Sep 2016 04:41:09 GMT

Hi, AS is capitalized be cause of splunk 's search syntax.

For example, on search bar, if I search for "John or Tom" (without double quotes), splunk will search as it is..or, splunk will search "John or Tom".
If I search for "John OR Tom", this will be a OR search.

Re: Clarification regarding pivot command

Justin1224 — Tue, 27 Sep 2016 13:50:18 GMT

So it's the same? For example, I've seen search queries that have had in them: "dc(foo) as blah". And that query got a distinct count of the instances of the field foo and renamed it as blah. So you're saying that if the search was instead: "dc(foo) AS blah" it would do the exact same thing?